IDS Forum

Re: Update Stats on Combo Index

Posted By: ART KAGEL, BLOOMBERG/ 731 LEXIN
Date: Tuesday, 24 January 2006, at 11:02 a.m.

In Response To: Update Stats on Combo Index (Jarratt, Linwood B.)

Lennie:

You should be doing one of the following:

If you have an older engine version (Earlier than 7.31xD2 or 9.30xC3):
1. MEDIUM on all columns in the table.
2. Separate HIGH with DISTRIBUTIONS ONLY on the first column of each index key
and the first column that's different if several indexes start with the same
column(s).
3. LOW on the entire key of every index.

- Optimization: if there's a single column index you can omit the
DISTRIBUTIONS ONLY when you do the HIGH on that column and eliminate the LOW on
that single column key.

If you have a later, optimized, server version (ie 7.31xD2+ or 9.30xC3+ or 9.4
or 10.0):
1. Single HIGH with DISTRIBUTIONS ONLY on all columns that lead any index plus
the first columns that are different when several indexes start with the same
column(s). If this statement is longer than 64K break it up into two or more
statements.
2. MEDIUM on all columns not listed in the HIGH run. If this statement is
longer than 64K break it up into two or more statements.
3. LOW on the entire key of every index.

OR - you can just get my dostats utility which implements these protocols
automatically. Dostats also provides many options and features that make
managing your server stats much easier. Dostats is included in the package
utils2_ak available from the IIUG Software Repository.

The information above is found in the Informix Performance Guide and in a paper
by John Miller III at:
www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/020
3miller.html

Art S. Kagel

----- Original Message -----
From: Li.... Jarratt <ids@iiug.org>
At: 1/24 10:21

I would like to know about how Update statistics works in regards to combo
indexes. Specifically, we update stats medium on each column that is
included in an index and the primary key index as high. We seem to
sometimes have slowness issues with combo indexes even directly after the
stats have been updated. My question is should be do an update stats on the
combo index or should the individual columns be sufficient?

Thanks

Lennie

Informix DBA
Information Technology Tax team
Lake County, IL
O: 847-377-2092
C: 847-309-7718

Math illiteracy affects 7 out of every 5 people.

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Update Stats on Combo Index
  Jarratt, Linwood B. -- Tuesday, 24 January 2006, at 10:21 a.m.
  • Re: Update Stats on Combo Index
    Obnoxio The Clown -- Tuesday, 24 January 2006, at 10:35 a.m.
    • RE: Update Stats on Combo Index
      Jarratt, Linwood B. -- Tuesday, 24 January 2006, at 10:36 a.m.
      • RE: Update Stats on Combo Index
        Obnoxio The Clown -- Tuesday, 24 January 2006, at 10:44 a.m.
        • RE: Update Stats on Combo Index
          Jarratt, Linwood B. -- Tuesday, 24 January 2006, at 11:00 a.m.
          • RE: Update Stats on Combo Index
            Obnoxio The Clown -- Tuesday, 24 January 2006, at 11:16 a.m.
  • Re: Update Stats on Combo Index
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 24 January 2006, at 11:02 a.m.
  • Re: Update Stats on Combo Index
    Anup Agrawal -- Tuesday, 24 January 2006, at 11:59 a.m.
    • Re: Update Stats on Combo Index
      ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 24 January 2006, at 12:10 p.m.
      • RE: Update Stats on Combo Index
        Jarratt, Linwood B. -- Tuesday, 24 January 2006, at 12:13 p.m.
        • RE: Update Stats on Combo Index
          ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 24 January 2006, at 12:14 p.m.
          • RE: Update Stats on Combo Index
            Noel Murphy -- Wednesday, 25 January 2006, at 7:18 a.m.
            • RE: Update Stats on Combo Index
              ART KAGEL, BLOOMBERG/ 731 LEXIN -- Wednesday, 25 January 2006, at 8:34 a.m.
    • RE: Update Stats on Combo Index
      Ford, Andrew G -- Tuesday, 24 January 2006, at 12:21 p.m.
      • RE: Update Stats on Combo Index
        Obnoxio The Clown -- Tuesday, 24 January 2006, at 12:25 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.