Outer joins = full scan?

Hi there,

I am executing this select statement:

SELECT ...
  FROM t, c

 WHERE t.location_code = c.location_code (+)
   AND t.case_num = c.case_num (+)
   AND t.debtor_id = c.debtor_id (+)
   AND c.debtor_id IS NULL
   AND t.location_code = 'value'
   AND t.case_num =  'value';

The explain plan is:

SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=37585 Bytes=1315475)   SEQUENCE OF D_SQ
    FILTER

      NESTED LOOPS (OUTER)
        TABLE ACCESS (FULL) OF T (Cost=28 Card=37585 Bytes=676530)
        INDEX (UNIQUE SCAN) OF XPKC (UNIQUE)

But if I remove the outer Join:

SELECT ...
  FROM t, c

 WHERE t.location_code = c.location_code 
   AND t.case_num = c.case_num 
   AND t.debtor_id = c.debtor_id 
   AND c.debtor_id IS NULL
   AND t.location_code = 'value'
   AND t.case_num =  'value';

the explain plan is:

SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=35)   SEQUENCE OF DEBTOR_EVENT_SQ
    NESTED LOOPS (Cost=2 Card=1 Bytes=35)

      INDEX (RANGE SCAN) OF XIE1T (NON-UNIQUE)(Cost=1 Card=1 Bytes=17)
      TABLE ACCESS (BY INDEX ROWID) OF T (Cost=1 Card=37585
Bytes=676530)
        INDEX (UNIQUE SCAN) OF XPKT (UNIQUE)

Any reason for this? How can I hint Oracle to use the index in T? I am using Oracle 8.1.

Thanks,

Rafael Received on Fri May 30 2003 - 07:00:14 CDT