It confirms our intuition on the
number of table scans and sorts. It also reveals that the time scanning a
single table (about 100 msec) is small compared to the time spent when
sorting each table (about 450 msec). Therefore we could try to aim lower
than 2.4 sec of total execution time.
This is not the only possible execution plan for the symmetric difference
query. Oracle has quite sophisticated query rewrite capabilities, and
transforming set operation into join is one of them.
In our example, both minus
operators could be rewritten as anti-joins. As of version 10.2, however,
this transformation is not enabled by default, and used as part of view
merging/ query unnesting framework only. It can be enabled with
alter session set "_convert_set_to_join"=
true;
The other alternative is to rewrite the SQL query manually [replacing the
minus operator with a NOT IN subquery] evidences about 30% improvement in
execution time . . .
select *
from A
where (col1,col2,?) not in
(select col1,col2,? from B)
union all
select * from B
where (col1,col2,?) not in
(select col1,col2,? from A);
The new execution statistics are very different and faster, using hash
joins:
Execution plan for comparing the contents of two tables with NOT IN subquery
