Message Posted: Thu, 29 Jan 2004 @ 21:56:42 GMT

 < Last>>

 Subj: Re: Indexes vs statistics From: Christie, Jon

Aggregation cardinality is often hard to estimate. It is equal to the number of distinct values for the grouping columns. So if the set of grouping columns coincides with an index and you've collected statistics on the index, the optimizer can make a dead-on estimate. Otherwise, it's a crap shoot. We used to estimate that the aggregate cardinality was the square root of the number of rows going in. That's the geometric mean of the extreme possibilities; either 1 or N where N is the number of rows going in. But that estimate was often too low, and generally speaking, estimates that are too low get the optimizer in trouble more often than estimates that are too high. So the estimate was changed. I'm not 100% sure what the estimate is today, though I remember at one time it was 90% of the rows going in. That may still be the estimate, but I wouldn't count on it in every case.

Recommendation: collect stats on grouping columns. In other words, for GROUP BY X,Y,Z collect statistics on (X,Y,Z) even if they're not an index. Of course, if you've got something like GROUP BY T1.X, T2.Y, T3.Z there's not anything you can do.

Aggregation in a view causes "spooling." I'm sure you've heard about this. In the resolver (the parser phase prior to the optimizer) views (and derived tables) with a GROUP BY clause are resolved and then sent ahead to the optimizer which builds a plan to "materialize" the view (or derived table) and sends this plan (in the form of a tree) back to the resolver. In the original query, the view (or derived table) is replaced by a spool. When we get to the main optimizer phase, the optimizer has almost no information on what's inside the spool. It's a black box. This effects the confidence of estimates. I'm not sure if they become no confidence or low confidence, but they're certainly no higher.

In V2R5, derived tables are no longer automatically spooled. Instead, they are "handled like views." But since views with aggregation are spooled and since one of the primary uses of derived tables is to move aggregation up in a query or to do multiple levels of aggregation, you're still going to see spooling. But not as much as you used to.

My, my. I have rambled on. I hope you can make sense of what I said.

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2004 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback