Re: Update Statistics

Rudy Fernandes wrote: > > In article <332EEA22.5CE8DF1E@www.weideneder.de>, > Stefan Weideneder <stefan@www.weideneder.de> wrote: > SNIP > >SELECT * FROM t1 WHERE f2 = 2500; -- est. #rows returned -> <= 10> > > >( Is there someone out there, who's interested in the above behaviour ? > > I didn't find this in any handbook ) > > > > OL 7.2 returns 'Estimated # of Rows Returned: 1 ; SEQUENTIAL SCAN' > > The query path is fine, but the estimate foxes me. > > As per the book, the estimate should be 500 the book makes a difference between "indexed-cols" and "any-cols". So what's the meaning of "any-cols" ? > (i.e selectivity 1/10 for cols > which are not the first in an index). However, the book (Performance Guide > Ch4) also says the selectivity logic mentioned is 'not exhaustive'. > It seems to be treated ass an indexed column for which update statistics > low has not been run - Simply change the number of different values for the second column, and run UPDATE STATISTICS LOW again. Then you will see that the optimizer calculatesthe number of rows to be returned very good. Obviously there must be a formula... You don't have to change the data type to get this new result, but you must run UPDATE STATISTICS LOW again ! > in which case OL seems to make slightly different > assumptions based on the data type of the column) > SNIP > > > >If this will be returned by your optimizer, you should wonder, how > >the optimizer got this information for *f2*, don't you ? We didn't > >run UPDATE STATISTICS HIGH for the *f2* column. > >Again, change the values for the column *f2* and enter instead of 5,000 > >different values only 1,000 different values. The estimated number of > >rows - and the real one, too - will be about 10. The only thing to > >do before is an UPDATE STATISTICS LOW for that table. > >Since there is no information stored for the column *f2* I believe, > >that the number of estimated rows returned by the optimizer will be > >determined by > > > >* the number of unique values of the first indexed column > > ( I'm bad, I've changed the system catalog tables to find it out, > > but the estimated number of rows didn't change very meaningfull. > > NEVER CHANGE YOUR SYSTEM TABLES INSIDE A PRODUCTIVE ENVIRONMENT ) > > > > Sorry. Can't agree with this - there cannot be any meaningful > selectivity relationship between columns in an index. > > >* and the number of index levels ( sysindexes.leaves ). The more > > selective an index is, the more leaf pages will exist in your > > index tree. If you know the selectivity of the first indexed column > > than you can estimate the selectivity of the rest. > > Leaf pages are not an accurate indicator of selectivity because they > depend on two factors > > 1. index uniqueness > 2. index width > > Since the width of the index is not available in sysindexes, I can't see > the optimizer using leaf pages as an indication of selectivity. > (So what on earth are leaf pages doing there?) > Okay, let me try to explain, how it might be possible to calculate the number of rows for the second column. First, let's talk about the relation between leaf pages and the selectivity of an index. Inside a leaf node each unique value is stored in the following way: composite-key-value | rowid | delete-flag | rowid | delete-flag | ... Additionally there will be a slot entry at the end of the node/page. For each unique composite-key-value there is a list of rowids. Add. each unique composite-key-value requires at least one slot-entry ( 4 Byte ). Because we have always the same number of rows, we just have to look at the overhead for the composite-key-values and the slot-entries. Now, if you only have a single value for your key-value and there will be only one entry ( key and slot ) inside your page. But if you would have several different key-values, there must be several key-values and slot-entries stored and possibly you need more leaf pages to store the data. If you would know, that your index is made up of two columns, and if you would know, that the first column has only two different values, you could estimate the number of different values for the second column, if you would know the number of leaf pages. All these informations are stored in "sysindexes" ( nunique + leaves ) . All you need are the following parameters: number of rows in your table ( overhead inside your leaves ) number of leaf pages ( sysindexes.leaves / sysfragments.leaves ) keyvalue size ( via sysindexes.part1-16 and syscolumns.colno ) number of different values in the first column ( sysindexes.nunique ) The only problem I see is the fillfactor of your index. This could raise a problem; but I ran a few tests for 38 hours, created different indexes and stored different values for the first and second column of the composite index. In about 90% of all runs the optimizer estimated the correct number of rows to be returned. Bye Stefan