Re: Update Statistics

In article <332EEA22.5CE8DF1E@www.weideneder.de>, Stefan Weideneder <stefan@www.weideneder.de> wrote: >> >> NOTE : OPTCOMPIND is always set to 0 >> >> 1. WHERE clause has multiple filters, but exactly ONE corresponds >> to an index. >> >> Result : Index used (irrespective of availablity of distributions) >> Performance : with/without distributions - practically identical > >1) Might be true, either "Rudy" or me, we will record the Buffer/Page reads >on the "sysdistrib" table. Checks out OK at my end (as per Stefan's suggestion [sysmaster:sysptprof]) > >> 2. WHERE CLAUSE has multiple filters, but MORE than one corresponds >> to an index >> >> Case 1. Distributions do not exist. >> Result : Index CREATED FIRST gets used. > >2) Without any UPDATE STATISTICS, this might be true. See also 1) > You're right! UPDATE STATISTICS LOW will update sysindexes with information on how unique an index is (among others). When that is available, the more unique index gets used, irrespective of which index was created first. >> Case 2. Distributions exist. >> Result : Index with the better selectivity (as indicated by >> distributions) gets used. Checks out OK, too. (examining sysmaster:sysptprof) >> > >> >Second, if a composite index is made up of two attributes, >> >the optimizer knows the selectivity of the second attribute, >> >even if there is no data distribution available. >> >> How? > >4) It's the new fuzzy logic :;)) > >I believe, and that's my own opinion, the optimizer assumes that every >index is very selective, when you directly search for all indexed columns. >You can try it by yourself: ><snip> >SET EXPLAIN ON; >SELECT * FROM t1 WHERE f1 = "Y"; -- est. #rows returned -> 5,000>***** selectivity = 1/nunique -> rows = nrows * nunique = 5,000 ****** Ok so far! (Documented in Performance Guide 4-26) >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 (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 - in which case OL seems to make slightly different assumptions based on the data type of the column) > >SELECT * FROM t1 WHERE f3 = any-value -- est. #rows returend -> 1,000>***** selectivity = 1/10 -> rows = nrows * 1/10 = 1,000 ******* This is as per the book! > >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.nleaves ). The more > selective an index is, the more leave 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?) Cheers, ---------------------- Rudy Fernandes (ICP) GIC, Kuwait ----------------------