Oracle: Fast NOT IN for multiple columns

Question

I need to synchronize two tables. Suppose the tables contain the following columns:

Table1: A, B, C, D Table2: A, B, C, E 

I need to find such rows in table 1 that in table 2 there are no records with corresponding values (A, B, C) , and then E is calculated as F (D) and update table 2.

If I need to match, for example. only A, I would write the following query:

 SELECT * FROM Table1 WHERE A NOT IN (SELECT A FROM Table2) 

The multi-column counterpart seems too slow:

 SELECT * FROM Table1 WHERE A NOT IN (SELECT A FROM Table2) AND B NOT IN (SELECT B FROM Table2) AND C NOT IN (SELECT C FROM Table2) 

What is the best way to write such a query?

Answer 1

If (a, b, c) are not NULL in both tables, then both NOT IN and NOT EXISTS most likely (in the versions I tested) generate the same execution plan.

If (a, b, c) is declared as nullable, but you know that the columns are not really zero, you can trick the optimizer into performing hash antivirus anyway by adding AND AND a is not null AND b is not null , and c is not null "in your request. (You may also need to add / * + HASH_AJ * / hint in the subquery.)

In addition, the following queries are NOT identical:

  from table1 where (a,b,c) not in (select a,b,c from table2) from table1 where a not in(select a from table2) and b not in(select b from table2) and c not in(select c from table2) 

Answer 2

  SELECT * FROM Table1 WHERE (A, B, C) NOT IN (SELECT A,B,C FROM Table2) 

Answer 3

 SELECT * FROM Table1 WHERE not exist ( SELECT 1 FROM Table2 where Table2.a=Table1.a and Table2.b=Table1.b and Table2.c=Table1.c ) 

EDIT: Please note: in some cases, NOT IN does not exist, but is not completely identical (NULL values) see http: //asktom.oracle.com/pls/asktom/f? P = 100: 11: 0 ::: : p11_question_id: 442029737684

Answer 4

You can try

 SELECT * FROM Table1 WHERE not exists ( SELECT 1 FROM Table2 where Table2.a=Table1.a and Table2.b=Table1.b and Table2.c=Table1.c ); 

as sent by guigui42. It makes the hash join anti and avoids the filter.

OR try

 select t1.* from table1 t1, table2 t2 where t1.a = t2.a(+) and t1.b = t2.b(+) and t1.c = t2.c(+) and (t2.a is null or t2.b is null or t2.c is null); 

This makes an external connection filter +. Both should be much faster than NOT IN.

Answer 5

A small addition: I found that Oracle (11gR1 in my case) refuses hash anti-union when the NOT IN clause contains more than one column, e.g.

 SELECT * FROM Table1 WHERE (A,B,C) NOT IN ( SELECT /*+ HASH_AJ */ A,B,C FROM Table2 WHERE A IS NOT NULL AND B IS NOT NULL AND C IS NOT NULL ) 

and this is even when adding one of the hints (same with UNNEST ) and non-NULL conditions. It only works with one column.