Re: Separate Index or Composite Indice ?

From: Jan Krueger <jk_at_stud.uni-hannover.de>
Date: Sun, 02 Sep 2007 20:56:10 +0200
Message-ID: <46db06c3$0$29370$4c56b896@news-read1.lambdanet.net>

Jonathan Lewis wrote:

> "Richard Foote" <richard.foote_at_nospam.bigpond.com> wrote in message 
> news:xEUBi.29001$4A1.27811_at_news-server.bigpond.net.au...
> 

>> Note by placing the yes_no column first in a composite column, you can
>> potentially use a skip-scan access if you happen to not reference yes_no
>> in a predicate. Unlikely the other way around. So I would consider a
>> composite index on yes_no, some_id a more likely combination than the
>> other way around if you go down the composite index path although it does
>> depend on the distribution of data on the yes_no column and how likely
>> the column is referenced.
>>
>> Some things to consider anyways.
>>
>> Cheers
>>
>> Richard
>>

> 
> Just a little warning - last time I checked the skip-scan was
> not considered as an option for the second table in a nested
> loop join with index.
> 

Was not considered means you have traced the optimizer (10053 event) or you haven't seen it in the execution plan?

Best regards.

Jan Received on Sun Sep 02 2007 - 13:56:10 CDT