Menu
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?
382
SELECT * FROM table WHERE (col1 = 123 OR col2 = 123 OR col3 = 123 OR col4 = 123);
Partitioning your data and creating local partitioned indexes can improve your query performance. On a partitioned table, each partition has its own set of index tables. Effectively, there are multiple indexes, but the results from each are combined as necessary to produce the final result set.
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
