Tuning a full outer join

From: Richard Kuhler <noone_at_nowhere.com>
Date: Wed, 04 Jun 2003 22:42:13 GMT
Message-ID: <9BuDa.60044$x67.2672674@twister.socal.rr.com>

How can you tune the 'anti-join' part of a full outer join? I have a query that the optimizer wants to use a 'NESTED LOOPS (ANTI)'. However, the sets are very large and a 'HASH JOIN (ANTI)' would be much faster. I can easily use a 'USE_HASH' hint to change the 'NESTED LOOPS (OUTER)' to 'HASH JOIN (OUTER)' but I can't seem to control the 'anti-join' part.

Any ideas?

As a test, I've tried to get this example to use a 'MERGE JOIN (ANTI)' ...

create table x as select table_name from user_tables; create table y as select table_name from user_tables;

select --+ use_merge(y) merge_aj
  *
from x
full outer join y on x.table_name = y.table_name

Execution Plan

---

SELECT STATEMENT Optimizer=CHOOSE
  VIEW
    UNION-ALL

      MERGE JOIN (OUTER)
        SORT (JOIN)
          TABLE ACCESS (FULL) OF 'X'
        SORT (JOIN)
          TABLE ACCESS (FULL) OF 'Y'
    HASH JOIN (ANTI)
      TABLE ACCESS (FULL) OF 'Y'
      TABLE ACCESS (FULL) OF 'X'

As you can see, it's obeying my hint for the 'outer join' but I can't control the 'anti-join'.

Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production

Thanks,
Richard Received on Wed Jun 04 2003 - 17:42:13 CDT