Re: Outer joins = full scan?

rsegovia wrote:
> 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

Why do you want to use an index on the outer join query??

Think about what an OUTER JOIN is requesting the DB to do: return ALL rows of the base table (T), matched or not with the rows of the outer table (C).

  What is the fastest way to get all the rows of the table?

1. access the index and then access the table
2. access just the table

Answer B is faster for accessing all rows of the table.

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost