Re: Update Statistics

Rudy Fernandes wrote: > > Hi Stefan, > > > >I'm wondering whether the recommendations below are really from > >Informix. > >UPDATE STATISTICS HIGH and MEDIUM generate data distributions for> >the optimizer. This information must be read if they are > >available. > > I don't think so. If OPTCOMPIND is 0 or 1, under most OLTP circumstances > distributions will NOT be read. > > In fact, experiments that I have done on a SINGLE table query have > shown the following. > > 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. > 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) > Case 2. Distributions exist. > Result : Index with the better selectivity (as indicated by > distributions) gets used. > > Note : The above applies if the competing filters are similar in > the sense that all have equalities or all have matches. If one > has an equality and the other(s) matches, then the equality filter is > used (without getting into a distributions competition, I assume). > 3) MATCHES ??? Isn't this documented with a standard selectivity of 1/5 ? > > My conclusions are that, in an OLTP environment with OPTCOMPIND = 0, I > do NOT have to worry about the overhead of having distributions (even > with multiple-table joins). > > >If the optimizer generates the best query plan, even > >without this distribution, this additional information will slow > >down the query. Normally data distribution will be neccessary in > >about 5% to 10% of all your queries. > > I agree, if the environment is OLTP. > > > > >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: CREATE TABLE t1 ( f1 char(1), f2 int, f3 int, ... ); Now insert 5,000 rows with the following values: f1 = "Y" f2 = 1 to 2500 f3 = anything you want Again, load 5,000 rows with the following values: f1 = "N" f2 = 2501 to 5000 f3 = anything CREATE INDEX idx01 ON t1 ( f1, f2 ); UPDATE STATISTICS (LOW) FOR TABLE t1; We will find a few informations about the first indexed column of the index "idx01" in sysindexes. The number of unique values of our indexes will be 2 ( Y/N ). Now you should run 3 queries: SET EXPLAIN ON; SELECT * FROM t1 WHERE f1 = "Y"; -- est. #rows returned -> 5,000***** selectivity = 1/nunique -> rows = nrows * nunique = 5,000 ****** 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 ) SELECT * FROM t1 WHERE f3 = any-value -- est. #rows returend -> 1,000***** selectivity = 1/10 -> rows = nrows * 1/10 = 1,000 ******* 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 ) * 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. Any comments ??? Bye, Stefan