Re: Outer joins = full scan?

From: Ed Prochak <edprochak_at_adelphia.net>
Date: Fri, 30 May 2003 17:13:27 GMT
Message-ID: <3ED7957E.8060701@adelphia.net>

Billy Verreynne wrote:
> Ed Prochak wrote:
>
>
>

>>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?
>>
>>A) access the index and then access the table
>>B) access just the table
>>
>>Answer B is faster for accessing all rows of the table.

>
>
> True, but not in this case Ed.
>
> He uses criteria in his statement on table T (table 1 in the outer join).
> Thus is it not requested all rows from table T.
>
> I.e.
>

>>   AND t.location_code = 'value'
>>   AND t.case_num =  'value';

>
>
> This could be faster using an index than a full table scan.
>
> --
> Billy

True, I overlooked that. It does explain why the optimizer chose a full scan though. (The cardinality of the outer join query was 37thousand.)

So as another poster pointed out, are those attributes usable for the index he wants to use. If they are, a suitable hint might move the CBO to go the other way.

Thanks for the sanity check.

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

Received on Fri May 30 2003 - 12:13:27 CDT