Re: Optimizer behavior - DOH!

In article <4uetpe$5sp@sparcserver.lrz-muenchen.de>, Richard Spitz <spitz@GANS2X.?> writes >Bob Payne (bpayne@netcom.com) wrote: > >: Does the optimizer use partial indexes? In other words, if I have an >: index on the sku column, warehouse column and the date column, do I have to >: include all three of those in my where-clause? > >When you have a composite index on several columns, the optimizer will >use that index for a query over the first column in the index, not over >the others. > >E.G.: create index ix_sample1 on tab1 (col1, col2, col3); > >is functionally equivalent to > > create index ix_sample2 on tab1 (col1);> >but if you need an index for col2 and col3, too, then ix_sample1 will >do you no good. In other words ix_sample1 can be used in 'WHERE col1=' but not in 'WHERE col2=', though (of course) in 'WHERE col1= AND col2='. I have also found that in some cases the optimiser makes poor choices about how to do 'ORDER BY' in that 'WHERE col1 = 'literal' ORDER BY col2' can use a TEMP table to do the 'ORDER BY' where as 'ORDER BY col1, col2' will force the right thing onto the optimiser. This sort of thing only became obvious once 'set explain on' was available! -- Sally Woolrich