Re: Why does the optimizer do this ? uhmmmmmm

In article <B5Eo8hA.jorget@delphi.com>, Jorge Torralba <jorget@delphi.com> writes: |> If you are |> using a composite index and only use the first part of the index in your where |> clause. You will get a performance hit if you select anything other than that |> column. Interesting. What version are you using? I can't duplicate this behavior, but I have noticed that SE 5.04.UC1 seems less willing to use composite indices than SE 4.10.UC2 is. After I upgraded one system, I spent quite a few hours trying to figure out why certain ad hoc queries that used to run quickly had slowed to a crawl. In my case I was doing a join, where the primary key of a small table matched the first part of a composite index on the large table. With 4.10.UC2 this worked great. With 5.04.UC1 it often chooses to do a sequential scan of the large table. This is, of course, disastrous :-( "Update statistics" doesn't help. The applications that the end-users use almost always specify both parts of the composite index, and in that case everything works fine. So I left it alone. Jorge's message spurred me to do some more experimenting. As far as I can tell, with my system it doesn't matter which fields you select. But if I specify additional criteria in the small table, even on fields that aren't indexed, the optimizer gets the idea and uses the index after all. Do more recent version of SE 5 have a better weighting of factors? That would make me feel better about upgrading the system that's still running 4.10.UC2 ;-) -- Harry Bochner bochner@das.harvard.edu