Re: Tuning a full outer join

Richard Kuhler wrote:

> 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

It would appear that you never ran DBMS_STATS and produced statistics for the CBO.

And I'd be far more concerned about the apparent lack of indexes, or at least their usage,
then how Oracle might choose an execution path.

After you've got current statistics, run EXPLAIN PLAN and verified index usage or fixed
problems such that those indexes are used ... if you have any remaining problems go back
to this posting.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)