Re: Tuning a full outer join

It would seem that the optimizer always resolves a (full outer join) to a (left outer join) first and then a (anti join). Since there is only one select in the query, the hint is always applied to the (left outer join). I've got the feeling that sooner or later ORACLE is going to give you another hint to better control full outer join.

If you really want to fully control the full outer join, and you have control over how the query is written, why not decompose the full outer join into a left outer join and a anti join. You can then union all the 2 results. This is eaxctly what the optimizer is doing behind the scene anyway. This is also how full outer join was handled before ORACLE started supporting ANSI full outer join syntax.

You will then be able to control how each join is done. Two hints for two select statements. Since the execution plans are identical, performance should be alike. The union all statement takes longer to parse. But the effect should be negligible.

• Jusung Yang

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<9BuDa.60044$x67.2672674_at_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 Thu Jun 05 2003 - 02:23:31 CDT