One more thing, I was under the impression that you HAD to construct your where clause in the same order as a multi-column index. You're saying below that I don't have to... Learn something new!
Karen In a message dated 10/31/2012 10:44:07 AM Central Daylight Time, [email protected] writes: > << > Dennis: I'm not sure how a multi-column index would work. My > understanding is that you must reference both columns in a multi-column index > in order > for the index to be used (the fte article doesn't talk about multi-column > very much). So let's say I have a PK column. Would I have to say "give me > all rows where DateTime is null and PKColumn is not null?" > >> > > > No, to get value from a multi-column index you must use indexable terms > for columns starting with the left-most column in the index but you do not > have to include all columns. > > > For example, given CREATE INDEX PPL_BDAY_LASTNAME ON People(BirthDate, > LastName) then the following searches will use this index: > > > WHERE BirthDate BETWEEN 1/1/12 and 12/31/12 -- left-most column of index > > > WHERE BirthDate = 6/30/1960 AND LastName = 'Smith' -- left-most columns of > index > > > WHERE LastName = 'Smith' AND BirthDate = 6/30/1960 -- R:Base is smart > enough to re-order the search. > > > > But this is would not use the index: > > > WHERE LastName = 'Smith' -- Can't "peek into" the index beyond the first > column > > > > -- > Larry > > > >
