Re: Update Statistics Plan for IDS 7.31
From: "Art S. Kagel" <kagel@bloomberg.net> > >Asside to Obnoxio: You guys were doing OK. What'd you need me for? ;-) If you have two indexes on a table, one on (a,b,c) and one on (a,b,d), should you update statistics high on a and b or a, c and d? I still await your opinion on this. :-) ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html> <br>Obnoxio The Clown wrote: <blockquote TYPE=CITE>From: "Art S. Kagel" <kagel@bloomberg.net> <br>> <br>>Asside to Obnoxio: You guys were doing OK. What'd you need me for? ;-) <p>If you have two indexes on a table, one on (a,b,c) and one on (a,b,d),</blockquote> To start I would ask why you wouldn't create the indexes on in the following <br>manner instead: <br>(a,b,c) <br>(d,b,a) - or switch b and d <p>this would give you the most possible options to use an index given two <br>indexes on three colums. <br> <br> <blockquote TYPE=CITE> <br>should you update statistics high on a and b or a, c and d? I still await <br>your opinion on this. :-) <br> </blockquote> Now for the statistics: That will depend on the the distirbutions of the values <br>in the columns. If there is a very low cardinality there is really no need to run <br>statistics on the column, unless you are trying to avoid the index. Each <br>database is different and should be analysed for the proper scheme for running <br>statistics. There are applications that run better with no statistics in the database <br>at all. I am very aware of the recommended statistics of Informix, however, <br>experience shows me that there is no one statistics plan that works best for <br>all databases. I have seen some schemes implemented that definitely don't work, <br>other that work one one database and not on another. Too summarize, <br>each database is different, so why would the same statistics scheme work on <br>every database the same way? I guess I really didn't answer your question, but <br>overall statistics and indexes are very dependent on the data each are going <br>against. <br> <p>Greg</html>
Obnoxio The Clown wrote:
>
> From: "Art S. Kagel" <kagel@bloomberg.net>
> >
> >Asside to Obnoxio: You guys were doing OK. What'd you need me for? ;-)
>
> If you have two indexes on a table, one on (a,b,c) and one on (a,b,d),
> should you update statistics high on a and b or a, c and d? I still await
> your opinion on this. :-)
The recommendations are to update stats HIGH on all lead index columns, ie
column a, and if multiple composite indexes begin with the same lead
columns the also update stats HIGH for the first column that differs in
each index, therefore c and d. There is a note that in some instances it
may be beneficial to also do HIGH for the intervening columns, ie b, but
dostats does not implement that one so dostats output would be:
UPDATE STATISTICS MEDIUM FOR TABLE atable DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH FOR TABLE atable(a) DISTRIBUTIONS ONLY;
UPDATE STATISTICS LOW FOR TABLE atable(a,b,c);
UPDATE STATISTICS HIGH FOR TABLE atable(d) DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH FOR TABLE atable(c) DISTRIBUTIONS ONLY;
UPDATE STATISTICS LOW FOR TABLE atable(a,b,d);
Art S. Kagel