Re: Update Statistics Low, Medium & High

Clem Akins wrote: > > >In our system, we discover sometimes some tables in index searching is much > >faster while we update their statistics low or medium instead of high. > > > >We have an idea that update all tables high statistics with reference to the > >server admin. guide. > > I heard a discussion the other day which indicated that when you update > statistics "high" it causes the optimizer to evaluate every possible query > path. This evaluation might take longer than the actual query--it depends > on the indexes and the query. On the "medium" or "low" settings the optimizer > evaluates only the most likely paths, and chooses one quickly. Though I could > not find a definitive explanation of this exact point in the documentation, > a picture might look like: ... Clem, Have a look at SET OPTIMIZATION LOW|HIGH. I think this is what you are referring to. Changing the UPDATE STATISTICS doesn't affect the number of query paths that the optimizer checks it just provides different amounts of information for the optimizer to chew through in making it's choice about which query path is best. This could have a time implication. Changing the OPTIMIZATION setting affects which query paths the optimizer checks. On LOW it takes a branching path down the query path tree at each branch taking the cheapest option at that level. Fast but not necessarily the best choice. HIGH looks at all paths and decides which is cheaper. Slower optimization but better results. 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!