Example 4: Query2 Taking Too Long to Run

Problem Description

After fixing the problem above and running RUNSTATS for all tables in the database, CustomerC found another statement that was taking too long to complete.

Problem Analysis and Resolution

Step 1: Identify the Query

The customer was able to determine the problem query, using the application snapshot and dynamic SQL snapshot. The problem query was identified as:

select c1,c2 from f
  where c1=67 and
  c2='NS'

Step 2: Run Explain for the SQL Statement

The explain plan for the identified statement, as shown below, indicated that the optimizer had chosen an index-only access plan.

Estimated Cost = 5814.739
Estimated Cardinality = 1

Access Plan:
-----------
        Total Cost:             5814.739
        Query Degree:           1

       Rows
      RETURN
      (   1)
       Cost
        I/O
        |
      888.548
      IXSCAN
      (   2)
     5814.739
         2
        |
      955343
  INDEX: USER1
 FX1

Step 3: Check the Indexes

An index-only access plan normally provides good performance, and because the query is returning only one row, there appears to be something wrong with the table or indexes. The indexes for the table can be examined using the DESCRIBE TABLE command. The SHOW DETAIL option is required to get a listing of the key columns for the index.

describe indexes for table f show detail

The output of the above statement was:

Index          Index          Unique         Number of
schema         name           rule           columns        Column names
------------   ------------   ------------   ------------   ------------
USER1          FX1            D              2              +C2+C1

Therefore, there is one index named FX1 on the table, and the index keys are C2 and C1. Because there is a + sign before each column name, the index keys are in ascending order.

Step 4: Examine the Key Cardinality

To examine the cardinality of the index keys, there are two options, assuming that RUNSTATS was run recently:

Select the CARD column from the SYSCAT.COLUMNS view.
Select the FIRSTKEYCARD and FIRST2KEYCARD columns from the SYSCAT.INDEXES view.

The following statement will query the SYSCAT.INDEXES view to examine the key cardinality:

select firstkeycard, first2keycard from syscat.indexes
  where indname='FX1'

The output of this statement is:

FIRSTKEYCARD         FIRST2KEYCARD
-------------------- --------------------
                   4                34816

1 record(s) selected.

Therefore, there are only four distinct values for C2 and 34,816/4, or 8,704 distinct values for C1. Therefore, if the order of the keys were reversed, the selectivity of the query would be improved, and the index would be able to find the result much quicker with an index defined as C1,C2.

Step 5: Create the New Index

Create the new index and capture the index statistics as follows:

create index fx2 on f (c1,c2) collect statistics

Step 6: Retest

After creating the new index, the query was able to complete in less than a second. The new access plan is again using index-only access, but it is now using the new index. The new access plan looked like the following:

Estimated Cost = 42.6512
Estimated Cardinality = 1

Access Plan:
-----------
        Total Cost:             42.6512
        Query Degree:           1

       Rows
      RETURN
      (   1)
       Cost
        I/O
        |
      888.548
      IXSCAN
      (   2)
     5814.739
         2
        |
      955343
  INDEX: USER1
 FX2