Oracle: Fast NOT IN for multiple columns

I need to synchronize two tables. Let's assume that the tables contain following columns:

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

I need to find such rows in Table1 that there isn't entry with corresponding (A, B, C) values in Table2, then calculate E as F(D) and update Table2.

If I need to match e.g. only A, I would write the following query:

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

Multi-column analog seems to be too slow:

SELECT * FROM Table1 WHERE A NOT IN (SELECT A FROM Table2)
            开发者_StackOverflow           AND B NOT IN (SELECT B FROM Table2)
                       AND C NOT IN (SELECT C FROM Table2)

What is the best way to write such query?

---

If (a,b,c) are NOT NULL in both tables, both NOT IN and NOT EXISTS will most likely (on the verisons I have tried) generate the same execution plan.

If (a,b,c) are declared as nullable, but you know that the columns are in fact not null, you can trick the optimizer into doing the hash anti join anyway by adding "AND a is not null AND b is not null AND c is not null" to your query. (You may also have to add a /*+ HASH_AJ */ hint in the sub query.)

Also, 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)

---

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

---

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 : Note that Not exist and NOT IN are not totally identical in some cases (NULL values) see : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:442029737684

---

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 posted by guigui42. It does a 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);

That does an outer join + filter. Both othese should be much faster than doing a NOT IN.

---

A small addendum: I have found that Oracle (11gR1 in my case) refuses to hash anti join 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 even when adding one of the hints (same with UNNEST) and the non-NULL conditions. With one columns only it works.

---

for performance reasons never do

NOT IN (SELECT

use

NOT EXISTS   (SELECT 1 FROM