Re: Outer Join Tuning ?!?

Thomas,

If you use the hint /*+ use_concat(a) index(a) */ you will get the plan that you want. You might also find that analyzing your tables, especially with the FOR ALL INDEXED COLUMNS option (7.3 only) might help the optimizer make a better decision.

Regards,  

Guy Harrison

gharriso@werple.net.au || http://werple.net.au/~gharriso || 613 419377964

Thomas Jagoditsch <t_j_a_at_geocities.com> wrote in article <33B0FD25.1E0BEB53_at_geocities.com>...
> hi !
>
> following SQL-Select seems to be diffcult to tune, maybe someone has a
> hint for me ( table/index defs .. appended ):
> if you have a outer join combined with or (or in = optimizer changes to
> or), you get always a full-table scan. this is a critical performance
> loss on such queries.
> how can i avoid the full-table scan ?
>
> Examples:
>
> 1.) outer table join with or:
>
> explain plan for
> select a.id
> from a, b
> where a.id = b.id(+) and
> a.name in ('A','B');
>
> EXPLAIN_PLAN
> ------------------------
> NESTED LOOPS OUTER
> TABLE ACCESS FULL A
> INDEX UNIQUE SCAN PK_B
>
> 2.) Example 1.) with removed the outer join:
>
> explain plan for
> select a.id
> from a, b
> where a.id = b.id and
> a.name in ('A','B');
>
> EXPLAIN_PLAN
> -----------------------------
> CONCATENATION
> NESTED LOOPS
> TABLE ACCESS BY ROWID A
> INDEX RANGE SCAN X_A_NAME
> INDEX UNIQUE SCAN PK_B
> NESTED LOOPS
> TABLE ACCESS BY ROWID A
> INDEX RANGE SCAN X_A_NAME
> INDEX UNIQUE SCAN PK_B
>
> 3.) removed or from Example 1.)
>
> explain plan for
> select a.id
> from a, b
> where a.id = b.id(+) and
> a.name = 'A';
>
> EXPLAIN_PLAN
> ----------------------------
> NESTED LOOPS OUTER
> TABLE ACCESS BY ROWID A
> INDEX RANGE SCAN X_A_NAME
> INDEX UNIQUE SCAN PK_B
>
>
> ----------------------------------------------------
> Appended Stuff:
>
> --snip test_cre.sql snip--
>
> create table a
> ( id number not null,
> name varchar(2) null,
> constraint pk_a primary key ( id ) );
>
> create table b
> ( id number not null,
> name varchar(2) null,
> constraint pk_b primary key ( id ) );
>
> create index x_a_name on a ( name );
>
> create index x_b_name on b ( name );
>
> --snip explain_plan.sql snip--
>
> select lpad (' ', level ) ||
> operation || ' ' ||
> options || ' ' ||
> object_name explain_plan
> from plan_table
> connect by prior id = parent_id
> start with id = 1;
>
> --snip end--
>
>
Received on Wed Jun 25 1997 - 00:00:00 CDT