Update statistics
This is a multi-part message in MIME format.
------=_NextPart_000_0034_01C05A1D.1A715400
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
I've read about this in the manuals, I've asked advise and looked at
samples, but update statistics is still not 100% clear to me.
I also got different answers from different people with regards to how it
must be done. On my machine I run -
update statistics low drop distributions; (whole database)
update statistics high (for all leading columns indexes - using the
sysindexes.part1 column)
update statistics medium (for all other columns used in indexes -
nonleading columns in indexes)
update statistics for procedure;
Is this correct ?
I've also written a 4gl program (because I don't like the scripts too much)
that is a little different -
I still run update statistics low drop distributions; (whole database)
it will then update statistics high for all leading columns in indexes, and
on all other columns (if they are used in indexes or not) I run update
statistics medium.
Can anyone give me a short and exact answer as to how it must be done ?
Thanks
Dirk
Dirk Moolman
Database Administrator
Reach Technologies
"Bravery is the capacity to perform properly even when scared half to
death."
- General Omar Bradley
------=_NextPart_000_0034_01C05A1D.1A715400
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>I've =
read about this=20
in the manuals, I've asked advise and looked at samples, but =
update=20
statistics is still not 100% clear to me.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D925404913-29112000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>I also =
got different=20
answers from different people with regards to how it must be done. =
On my=20
machine I run -</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D925404913-29112000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>update =
statistics=20
low drop distributions; (whole database)</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>update =
statistics=20
high (for all leading columns indexes - using the sysindexes.part1=20
column)</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>update =
statistics=20
medium (for all other columns used in indexes - nonleading columns =
in=20
indexes)</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>update =
statistics=20
for procedure;</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D925404913-29112000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D925404913-29112000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>Is =
this correct=20
? </SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D925404913-29112000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>I've =
also written a=20
4gl program (because I don't like the scripts too much) that =
is a=20
little different -</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D925404913-29112000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>I =
still run update=20
statistics low drop distributions; (whole database)</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>it =
will then update=20
statistics high for all leading columns in indexes, and on all =
other=20
columns (if they are used in indexes or not) I run update =
statistics=20
medium.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D925404913-29112000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>Can =
anyone give me a=20
short and exact answer as to how it must be done ?</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3D2><FONT face=3DArial><SPAN=20
class=3D925404913-29112000>Thanks</SPAN></FONT></FONT></DIV>
<DIV><SPAN class=3D925404913-29112000></SPAN><FONT size=3D2><FONT =
face=3DArial><SPAN=20
class=3D925404913-29112000>Dirk</SPAN><BR><BR><BR></FONT></FONT></DIV>
<P><FONT face=3D"Comic Sans MS" size=3D2>Dirk Moolman</FONT> <BR><FONT=20
face=3D"Comic Sans MS" size=3D2>Database Administrator</FONT> <BR><FONT=20
face=3D"Comic Sans MS" size=3D2>Reach Technologies</FONT> </P><BR>
<P><FONT face=3D"Comic Sans MS" size=3D2>"Bravery is the capacity to =
perform=20
properly even when scared half to death."</FONT> <BR><FONT face=3D"Comic =
Sans MS"=20
size=3D2>- General Omar Bradley</FONT> </P>
<DIV> </DIV></BODY></HTML>
------=_NextPart_000_0034_01C05A1D.1A715400--
This is how you should run update statistics, according to Performance
Guide manual:
For each table that your query accesses, build data distributions
according to the following guidelines:
1. Run UPDATE STATISTICS MEDIUM for all columns in a table that do
not head an index. This step is a single UPDATE STATISTICS
statement. The default parameters are sufficient unless the table is
very large, in which case you should use a resolution of 1.0, 0.99.
With the DISTRIBUTIONS ONLY option, you can execute UPDATE
STATISTICS MEDIUM at the table level or for the entire system
because the overhead of the extra columns is not large.
2. Run UPDATE STATISTICS HIGH for all columns that head an index.
For the fastest execution time of the UPDATE STATISTICS statement,
you must execute one UPDATE STATISTICS HIGH statement for each
column.
In addition, when you have indexes that begin with the same subset
of columns, run UPDATE STATISTICS HIGH for the first column in
each index that differs.
For example, if index ix_1 is defined on columns a, b, c, and d, and
index ix_2 is defined on columns a, b, e, and f, run UPDATE
STATISTICS HIGH on column a by itself. Then run UPDATE
STATISTICS HIGH on columns c and e. In addition, you can run
UPDATE STATISTICS HIGH on column b, but this step is usually notnecessary.
3. For each multicolumn index, execute UPDATE STATISTICS LOW for
all of its columns. For the single-column indexes in the preceding
step, UPDATE STATISTICS LOW is implicitly executed when you
execute UPDATE STATISTICS HIGH.
4. For small tables, run UPDATE STATISTICS HIGH.
Because the statement constructs the statistics only once for each
index, these
steps ensure that UPDATE STATISTICS executes rapidly.
For additional information about data distributions and the UPDATE
STATISTICS statement, see the Informix Guide to SQL: Syntax.
In article <90334l$3ah$1@news.xmission.com>,
"Dirk Moolman" <dirkm@reach.co.za> wrote:
>
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0034_01C05A1D.1A715400
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: 7bit
>
> I've read about this in the manuals, I've asked advise and looked at
> samples, but update statistics is still not 100% clear to me.
>
> I also got different answers from different people with regards to
how it
> must be done. On my machine I run -
>
> update statistics low drop distributions; (whole database)
> update statistics high (for all leading columns indexes - using the
> sysindexes.part1 column)
> update statistics medium (for all other columns used in indexes -
> nonleading columns in indexes)
> update statistics for procedure;>
> Is this correct ?
>
> I've also written a 4gl program (because I don't like the scripts
too much)
> that is a little different -
>
> I still run update statistics low drop distributions; (whole database)
> it will then update statistics high for all leading columns in
indexes, and
> on all other columns (if they are used in indexes or not) I run
update
> statistics medium.
>
> Can anyone give me a short and exact answer as to how it must be
done ?
>
> Thanks
> Dirk
>
> Dirk Moolman
> Database Administrator
> Reach Technologies
>
> "Bravery is the capacity to perform properly even when scared half to
> death."
> - General Omar Bradley
>
> ------=_NextPart_000_0034_01C05A1D.1A715400
> Content-Type: text/html;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META content=3D"text/html; charset=3Diso-8859-1" =
> http-equiv=3DContent-Type>
> <META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR></HEAD>
> <BODY>
> <DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-
29112000>I've =
> read about this=20
> in the manuals, I've asked advise and looked at samples, but =
> update=20
> statistics is still not 100% clear to me.</SPAN></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN=20
> class=3D925404913-29112000></SPAN></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>I
also =
> got different=20
> answers from different people with regards to how it must be
done. =
> On my=20
> machine I run -</SPAN></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN=20
> class=3D925404913-29112000></SPAN></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-
29112000>update =
> statistics=20
> low drop distributions; (whole database)</SPAN></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-
29112000>update =
> statistics=20
> high (for all leading columns indexes - using the sysindexes.part1=20
> column)</SPAN></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-
29112000>update =
> statistics=20
> medium (for all other columns used in indexes - nonleading
columns =
> in=20
> indexes)</SPAN></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-
29112000>update =
> statistics=20
> for procedure;</SPAN></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN=20
> class=3D925404913-29112000></SPAN></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN=20
> class=3D925404913-29112000></SPAN></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>Is =
> this correct=20
> ? </SPAN></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN=20
> class=3D925404913-29112000></SPAN></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-
29112000>I've =
> also written a=20
> 4gl program (because I don't like the scripts too much)
that =
> is a=20
> little different -</SPAN></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN=20
> class=3D925404913-29112000></SPAN></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>I =
> still run update=20
> statistics low drop distributions; (whole database)
</SPAN></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>it =
> will then update=20
> statistics high for all leading columns in indexes, and on all =
> other=20
> columns (if they are used in indexes or not) I run update =
> statistics=20
> medium.</SPAN></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN=20
> class=3D925404913-29112000></SPAN></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2><SPAN class=3D925404913-29112000>Can
=
> anyone give me a=20
> short and exact answer as to how it must be done ?</SPAN></FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2><FONT face=3DArial><SPAN=20
> class=3D925404913-29112000>Thanks</SPAN></FONT></FONT></DIV>
> <DIV><SPAN class=3D925404913-29112000></SPAN><FONT size=3D2><FONT =
> face=3DArial><SPAN=20
> class=3D925404913-29112000>Dirk</SPAN><BR><BR><BR></FONT></FONT></DIV>
> <P><FONT face=3D"Comic Sans MS" size=3D2>Dirk Mool
Related threads
- Conversion to differeent characters sets
- Problem in changing locale via dbexport/dbimport
- RE: openlink error "Unable to load locale categories"