Outer Join Tuning ?!?

From: Thomas Jagoditsch <t_j_a_at_geocities.com>
Date: 1997/06/25
Message-ID: <33B0FD25.1E0BEB53@geocities.com>#1/1

This is a multi-part message in MIME format.
--------------CD24323F1C96EC293752B720

Content-Type: text/plain; charset=us-ascii
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Transfer-Encoding: 7bit

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--

--------------CD24323F1C96EC293752B720

Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Thomas Jagoditsch
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Thomas Jagoditsch
n:              Jagoditsch;Thomas
org:            BULL Austria AG
email;internet: t_j_a_at_geocities.com
note:           Home-Page:

=0A=
http://www.geocities.com/ResearchTriangle/3142 x-mozilla-cpt: ;0
x-mozilla-html: TRUE
end: vcard

--------------CD24323F1C96EC293752B720--
Received on Wed Jun 25 1997 - 00:00:00 CDT