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)
                       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?

Answers


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)

Need Your Help

Using Threading, Runnable and Message in Android

android

This is my scenario. A class A implements Runnable. When user click a button, there will show a progress dialog and call the method searchMap() to search an address. The dialog dismisses after 10

Big-O run time for adding N items into ArrayList

java arraylist big-o

Say I'm adding N items to an ArrayList in Java. What is the worst-case run time for this? I know it can be O(N) to add a single item because the array may have to resize. It won't resize N times as...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.