Re: Outer joins = full scan?

Originally posted by Rsegovia
> 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

Hard to answer without knowing what indexes you have. Suppose the only index on T was (debtor_id,...). That could be used for the second query (if driving from C) but not for the first.

--
Posted via http://dbforums.com