Reducing the number of matching columns

Consider the example in Figure 1, where the index that Db2 picks is less than optimal.

CREATE TABLE PART_HISTORY (
    PART_TYPE  CHAR(2),       IDENTIFIES THE PART TYPE
    PART_SUFFIX CHAR(10),     IDENTIFIES THE PART
    W_NOW      INTEGER,       TELLS WHERE THE PART IS
    W_FROM     INTEGER,       TELLS WHERE THE PART CAME FROM
    DEVIATIONS INTEGER,       TELLS IF ANYTHING SPECIAL WITH THIS PART
    COMMENTS        CHAR(254),
    DESCRIPTION     CHAR(254),
    DATE1           DATE,
    DATE2           DATE,
    DATE3           DATE);
 
  CREATE UNIQUE INDEX IX1 ON PART_HISTORY
    (PART_TYPE,PART_SUFFIX,W_FROM,W_NOW);
  CREATE UNIQUE INDEX IX2 ON PART_HISTORY
    (W_FROM,W_NOW,DATE1);

  +------------------------------------------------------------------------------+
  |  Table statistics              |        Index statistics   IX1         IX2   |
  |--------------------------------+---------------------------------------------|
  | CARDF            100,000       |      FIRSTKEYCARDF       1000          50   |
  | NPAGES            10,000       |      FULLKEYCARDF     100,000     100,000   |
  |                                |      CLUSTERRATIO         99%         99%   |
  |                                |      NLEAF               3000        2000   |
  |                                |      NLEVELS                3           3   |
  |------------------------------------------------------------------------------|
  |                 column       cardinality    HIGH2KEY     LOW2KEY             |
  |                 --------     -----------    --------     -------             |
  |                 Part_type    1000           'ZZ'        'AA'                 |
  |                 w_now          50            1000         1                  |
  |                 w_from         50            1000         1                  |
  +------------------------------------------------------------------------------+
 
  Q1:
  SELECT * FROM PART_HISTORY   --  SELECT ALL PARTS
  WHERE PART_TYPE = 'BB'    P1 --  THAT ARE 'BB' TYPES
    AND W_FROM = 3          P2 --  THAT WERE MADE IN CENTER 3
    AND W_NOW = 3           P3 --  AND ARE STILL IN CENTER 3
 
  +------------------------------------------------------------------------------+
  |   Filter factor of these predicates.                                         |
  |    P1 = 1/1000= .001                                                         |
  |    P2 = 1/50  = .02                                                          |
  |    P3 = 1/50  = .02                                                          |
  |------------------------------------------------------------------------------|
  |  ESTIMATED VALUES                 |    WHAT REALLY HAPPENS                   |
  |                    filter    data |                       filter   data      |
  |  index  matchcols  factor    rows |    index  matchcols   factor   rows      |
  |   ix2     2       .02*.02      40 |     ix2     2       .02*.50    1000      |
  |   ix1     1       .001        100 |     ix1     1       .001        100      |
  +------------------------------------------------------------------------------+

Db2 picks IX2 to access the data, but IX1 would be roughly 10 times quicker. The problem is that 50% of all parts from center number 3 are still in Center 3; they have not moved. Assume that no statistics are available on the correlated columns in catalog table SYSCOLDIST. Therefore, Db2 assumes that the parts from center number 3 are evenly distributed among the 50 centers.

You can get the access path that you want by changing the query. To discourage the use of IX2 for this particular query, you can change the third predicate to be non-indexable.

SELECT * FROM PART_HISTORY
  WHERE PART_TYPE = 'BB'
    AND W_FROM = 3
    AND (W_NOW = 3 + 0)      <-- PREDICATE IS MADE NON-INDEXABLE

Now index I2 is not picked, because it has only one match column. The preferred index, I1, is picked. The third predicate is a non-indexable predicate, so an index is not used for the compound predicate.

You can make a predicate non-indexable in many ways. The recommended way is to add 0 to a predicate that evaluates to a numeric value or to concatenate an empty string to a predicate that evaluates to a character value.

These techniques do not affect the result of the query and cause only a small amount of overhead.

The preferred technique for improving the access path when a table has correlated columns is to generate catalog statistics on the correlated columns. You can do that either by running RUNSTATS or by updating catalog table SYSCOLDIST manually.