Re: Update Statistics
Andrew Ford said:
>> dostats.ec -- period!
>
> Well of course! I'm just trying to reduce the run time of update
> statistics. It seems like dostats wants you to generate low stats for the
> same column multiple times and generate medium stats for some columns,
> only
> to have them immediately overwritten by generating high statistics.
Have you considered just doing UPDATE STATISTICS LOW FOR TABLE xxx DROP
DISTRIBUTIONS? You don't always need the distributions.
It's worth trying, anyway.
> I tested both update statistics methods last night and the state of
> systables, syscolumns, sysindexes and dbschema -hd output after each run
> was
> identical (well, the partnum and version of the table was different in
> systables between tests since i dropped and recreated the database for
> each
> test).
>
> Am I wrong in thinking that to update statistics against a table you only
> need 3 update statistics statements.
>
> low - every column that appears in an index
> high - every column that heads an index and every column in a composite
> index that is the first column that differs when multiple indexes have the
> same columns at the head of the index (i.e. (a, b, c, d) and (a, b, c, e)
> run high for d and e)
> medium - every column not included in the high stats
>
> Andrew
>
>
> ----- Original Message -----
> From: "Obnoxio The Clown" <obnoxio@serendipita.com>
> To: "Andrew Ford" <andrewford@austin.rr.com>
> Cc: <informix-list@iiug.org>
> Sent: Thursday, June 09, 2005 3:57 AM
> Subject: Re: Update Statistics
>
>
>>
>> Andrew Ford said:
>> > For the following table on IDS versions 9.30xC2 and up
>> >
>> > create table t (
>> > a integer,
>> > b integer,
>> > c integer,
>> > d integer,
>> > e integer
>> > )>> >
>> > create unique index idx1 on t (a, b);
>> > create index idx2 on t (b, c, d);
>> > create index idx3 on t (b, c, e);>> >
>> > do
>>
>> dostats.ec -- period!
>>
>> > update statistics low for table t (a, b);
>> > update statistics low for table t (b, c, d);
>> > update statistics low for table t (b, c, e);>> >
>> > have the same net effect as
>> >
>> > update statistics low for table t (a, b, c, d, e);>> >
>> > ?
>> >
>> > and do
>> >
>> > update statistics medium for table t distributions only;
>> > update statistics high for table t (a) distributions only;
>> > update statistics high for table t (b) distributions only;
>> > update statistics high for table t (d) distributions only;
>> > update statistics high for table t (e) distributions only;>> >
>> > have the same net effect as
>> >
>> > update statistics medium for table t (c) distributions only;
>> > update statistics high for table t (a, b, d, e) distributions only;>> >
>> > ?
>> >
>> > Sorry if this has been answered before.
>> >
>> > Tried the following but no definitive answer was found.
>> >
>> >
> http://www3.software.ibm.com/ibmdl/pub/software/dw/dm/informix/0211desai/0211desai.pdf
>> > (understand the optimizer)
>> >
> http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html
>> > (tuning update statistics)
>> > http://publibfp.boulder.ibm.com/epubs/pdf/8344.pdf (9.3 performance
> tuning
>> > guide)
>> >
> http://groups-beta.google.com/group/comp.databases.informix/browse_frm/thread/a852f14f35a0038d/6a2d537ae8d21b02?q=update+statistics+repeated+column&rnum=1&hl=en#6a2d537ae8d21b02
>> > (cdi post, but based on 7.21 recommendations)
>> >
>> > Also, anyone aware of SET EXPLAIN ON in 9.30.UC2 for update statistics
>> > only
>> > writing out 1 char to the sqexplain.out file?
>> >
>> > Thanks,
>> >
>> > Andrew
>> >
>> >
>> > sending to informix-list
>> >
>>
>>
>> --
>>
>> Bye now,
>> Obnoxio
>>
>> "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule"
>> - Coluche
>>
>> A smile is a gift that is free to the giver and precious to the
>> recipient.
>> But giving someone the finger is free too, and I find it more personal
>> and
>> sincere.
>
--
Bye now,
Obnoxio
"C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule"
- Coluche
A smile is a gift that is free to the giver and precious to the recipient.
But giving someone the finger is free too, and I find it more personal and
sincere.
sending to informix-list