Re: Update Statistics

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 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. 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). 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? ---------------------- Rudy Fernandes (ICP) GIC, Kuwait ----------------------