Re: Update Statistics Plan for IDS 7.31
"Art S. Kagel" wrote:
>
> 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 recommendation in the manual used to be HIGH on all leading columns
that make the index unique. That would be a, b, c & d! But the
recommendations seem to change with each version.
> 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);
Would it? The multiple LOWs at the column level seem a bit redundant, as
LOW doesn't do any column level stuff. You could save a bit on
performance by only doing one per table, or even one per database is
probably quicker.
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock http://www.informix.com |//////// /|
| mailto:mdstock@mydas.freeserve.co.uk |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |What year 2000 bug? year 2000 bug? |/// / ////|
| |year 2000 bug? year 2000 bug? year |// / /////|
| |2000 bug? year 2000 bug? year 1900 |/ ////////|
+----------------------+-----------------------------------+-----------+
On Thu, 21 Oct 1999 01:09:43 +0100, "Mark D. Stock"
<mdstock@mydas.freeserve.co.uk> wrote:
>
>"Art S. Kagel" wrote:
>> 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);>
>Would it? The multiple LOWs at the column level seem a bit redundant, as
>LOW doesn't do any column level stuff. You could save a bit on
>performance by only doing one per table, or even one per database is
>probably quicker.
It all makes sense when you look at the changes actually made to
systables, sysindexes, and sysdistrib.
Any update stats statement updates the nrows column in systables,
but besides that, 'update low' ONLY changes data in sysindexes, and
ONLY changes the record corresponding to the index with the particular
combination of columns that you supply to the statement.
So if you update low on some non-indexed columns, you're really
doing nothing. And doing 'update low' on the whole table would
probably take care of all the indexes, though I'm not sure if its
any better or worse than specifying the columns and doing them
one by one. But at least the two UPDATE LOW's above are
not redundant.
Cheers,
Douglas Wilson