update statistics comparison
Hello,
Honestly, although doing update statistics for years :-) , still not in
100% clear on some results comparisons ( the optimizer likes one better
than other ....)
OK. What are the major possible differences of the following two sets of
update statistics jobs for a table? How will that impact optimizer'sbehavior?
job1,
UPDATE STATISTICS LOW FOR TABLE noaa:xref_aip_inv_modis;
UPDATE STATISTICS MEDIUM FOR TABLE noaa:xref_aip_inv_modis (related_table)RESOLUTION 2.000 0.950 DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH FOR TABLE noaa:xref_aip_inv_modis ( inventory_id,
related_id, relationship_id ) RESOLUTION 0.500 DISTRIBUTIONS ONLY;
job2,
UPDATE STATISTICS LOW FOR TABLE noaa:xref_aip_inv_modis;
UPDATE STATISTICS HIGH FOR TABLE noaa:xref_aip_inv_modis ( inventory_id,
related_id, relationship_id ) ;
Thanks,
Frank
--20cf3074b1425cd54d04d1146693
The HIGH statement in Job#2 will be recalculating the LOW stats for the
three listed columns that were already computed by the LOW on the entire
table. Job#1 will not do that because of the DISTRIBUTIONS ONLY clause in
the HIGH. The RESOLUTION clause in the HIGH for Job#1 is redundant since
the default resolution for HIGH is already 0.500. Job#1 is adding a MEDIUM
for the one column "related_table". What effect on the optimizer? IFF the
related_table column is used as a filter or join column in a query, the
optimizer might make a better decision about which index to use and/or the
order in which the tables are joined. Otherwise, these two sets of
commands are identical as far as the optimizer is concerned.
Have you tried using 'dostats'?
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Mon, Dec 17, 2012 at 6:04 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Hello,
>
> Honestly, although doing update statistics for years :-) , still not in
> 100% clear on some results comparisons ( the optimizer likes one better
> than other ....)
>
> OK. What are the major possible differences of the following two sets of
> update statistics jobs for a table? How will that impact optimizer's> behavior?
>
> job1,
>
> UPDATE STATISTICS LOW FOR TABLE noaa:xref_aip_inv_modis;
> UPDATE STATISTICS MEDIUM FOR TABLE noaa:xref_aip_inv_modis (related_table)> RESOLUTION 2.000 0.950 DISTRIBUTIONS ONLY;
> UPDATE STATISTICS HIGH FOR TABLE noaa:xref_aip_inv_modis ( inventory_id,
> related_id, relationship_id ) RESOLUTION 0.500 DISTRIBUTIONS ONLY;>
> job2,
>
> UPDATE STATISTICS LOW FOR TABLE noaa:xref_aip_inv_modis;
> UPDATE STATISTICS HIGH FOR TABLE noaa:xref_aip_inv_modis ( inventory_id,
> related_id, relationship_id ) ;>
> Thanks,
> Frank
>
> --20cf3074b1425cd54d04d1146693
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340ef132ee3304d1160a1e
Well the answer to this question depends on the version of the server you
are running. If
you are using version 11.70 with auto stats. Then the only difference is
the update statistics
medium as the server will automatically recognize that the data has not
changed between the
first time update stats low was executed and the second time (hidden in the
update stats high
command) in which the update stats low was attempted to be executed.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 12/17/2012 05:01:42 PM:
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 12/17/2012 05:17 PM
> Subject: Re: update statistics comparison [29105]
> Sent by: ids-bounces@iiug.org
>
> The HIGH statement in Job#2 will be recalculating the LOW stats for the
> three listed columns that were already computed by the LOW on the entire
> table. Job#1 will not do that because of the DISTRIBUTIONS ONLY clause in
> the HIGH. The RESOLUTION clause in the HIGH for Job#1 is redundant since
> the default resolution for HIGH is already 0.500. Job#1 is adding a
MEDIUM
> for the one column "related_table". What effect on the optimizer? IFF the
> related_table column is used as a filter or join column in a query, the
> optimizer might make a better decision about which index to use and/or
the
> order in which the tables are joined. Otherwise, these two sets of
> commands are identical as far as the optimizer is concerned.
>
> Have you tried using 'dostats'?
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated
nor
> those of the entities themselves.
>
> On Mon, Dec 17, 2012 at 6:04 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Hello,
> >
> > Honestly, although doing update statistics for years :-) , still not in
> > 100% clear on some results comparisons ( the optimizer likes one better
> > than other ....)
> >
> > OK. What are the major possible differences of the following two sets
of
> > update statistics jobs for a table? How will that impact optimizer's> > behavior?
> >
> > job1,
> >
> > UPDATE STATISTICS LOW FOR TABLE noaa:xref_aip_inv_modis;
> > UPDATE STATISTICS MEDIUM FOR TABLE noaa:xref_aip_inv_modis
(related_table)> > RESOLUTION 2.000 0.950 DISTRIBUTIONS ONLY;
> > UPDATE STATISTICS HIGH FOR TABLE noaa:xref_aip_inv_modis
( inventory_id,
> > related_id, relationship_id ) RESOLUTION 0.500 DISTRIBUTIONS ONLY;> >
> > job2,
> >
> > UPDATE STATISTICS LOW FOR TABLE noaa:xref_aip_inv_modis;
> > UPDATE STATISTICS HIGH FOR TABLE noaa:xref_aip_inv_modis
( inventory_id,
> > related_id, relationship_id ) ;> >
> > Thanks,
> > Frank
> >
> > --20cf3074b1425cd54d04d1146693
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae9340ef132ee3304d1160a1e
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>