Re: SEQ search faster than INDEX ?

On May 25, 10:47am, Ti Lian Hwang wrote: > Subject: SEQ search faster than INDEX ? > There seems to be a problem with index searches bases on dates. They seem to > required more resources than sequential searches. > > The following shows the result of "set explain on" on 3 queries, > with different indices. > > 1) costs 1249, using a combination index > 2) costs 4053, using a date index > 3) costs 1766, using a squential scan > > Any ideas ? In my experience the cost figure has limited importance. I find it is only generally usefull when comparing SQL statements made against the same database structure. When you do this the various cost figures give you a relative but not accurate picture of the difference in effort required to met each of the SQL statements. This allows you to rephrase select statements until you get the most efficient. By far the more usefull information is the filters and indexes and structure used by the SQL statement printed in the Set Explain. This tells you whether the engine is truely choosing the most efficient path assuming of course that you as the database user have an understanding of what the most efficient method is (Sometimes the optimiser is right and you are wrong though). In this case when you are changing the database design and the SQL statements you cannot really rely on the cost figures even for relative value as you are no longer comparing like with like. I'm assuming that you re-ran update statistics between each change because this can also have a major impact on the optimiser choices. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!