The indexes the system suggests, and which to create

Every time the optimizer wanted an index that was not there, it wrote it down. The advice sits there, and nobody reads it.

Then somebody reads it, finds forty rows, and creates them all.

Do not. It is the quickest way to make a database worse. The advisor reasons one query at a time: it does not know which indexes you already have, it does not know how many writes that table takes, and it does not know that advice number 12 is nearly identical to number 3. It is not designing your schema. It is taking notes.

What that advice really is

When the optimizer runs a query and the index it wants is not there, it does two things: it copes — often by building a temporary index on the fly, an MTI — and it writes into QSYS2.SYSIXADV that it would have liked those keys.

That row says "in this execution I would have wanted this". It does not say:

• whether you already have it, in a form that would do just as well;
• whether the query that generated it matters, or somebody ran it by hand one evening;
• what it costs to maintain that index on writes;
• that another eight rows for the same table are asking for variants of the same thing.

All four answers are yours to supply.

First step: read the advice already condensed

Before even looking at the raw advice, it pays to look at it merged. The system has a condenser that fuses overlapping advice for the same table into the few shapes covering all of it:

SELECT TABLE_NAME, KEY_COLUMNS_ADVISED, INDEX_TYPE,
       TIMES_ADVISED, MTI_USED, LAST_MTI_USED,
       MOST_EXPENSIVE_QUERY, AVERAGE_QUERY_ESTIMATE,
       TABLE_SIZE, ESTIMATED_CREATION_TIME
  FROM QSYS2.CONDENSEDINDEXADVICE
 WHERE TABLE_SCHEMA = 'PRODLIB'
 ORDER BY MTI_USED DESC, TIMES_ADVISED DESC

Forty pieces of advice becoming six is the normal scenario. Those six are a reasonable starting point; the forty never were.

Allow for it being slow. That view does not read a table, it runs the condenser: it re-reads all the raw advice and merges it every time you query it. Even a plain COUNT(*) pays the same price, and on a system where nobody has ever looked at SYSIXADV it can take a while.

The remedy is to pay for it once and then work on the copy:

CREATE TABLE QTEMP.ADVICE AS
  (SELECT * FROM QSYS2.CONDENSEDINDEXADVICE) WITH DATA

From there on you query QTEMP.ADVICE as much as you like, sorting and filtering it in peace. It is also the sounder way to reason about it: the advice stays still while you are weighing it, instead of shifting under your hand with every new query somebody runs on the system.

How much raw advice there is comes from this, and it is a number worth looking at:

SELECT COUNT(*) FROM QSYS2.SYSIXADV WHERE TABLE_SCHEMA = 'PRODLIB'

If it runs to tens of thousands, the first problem is not the missing indexes: it is that nobody has ever read that list, and it holds years of queries run once and never again.

And it can be far worse than tens of thousands. On a system in production for years you easily reach tens of millions of rows: at that point the condenser is not slow, it is unusable, because it has to re-read and merge all of them on every query.

When the list is enormous: narrow first, condense after

With millions of rows you do not start from the condenser. You start from the subset that actually carries a signal, and you read it from the raw advice in a single pass:

SELECT TABLE_NAME, KEY_COLUMNS_ADVISED,
       MTI_USED, TIMES_ADVISED, LAST_ADVISED
  FROM QSYS2.SYSIXADV
 WHERE TABLE_SCHEMA = 'PRODLIB'
   AND MTI_USED > 0
   AND LAST_ADVISED > CURRENT TIMESTAMP - 30 DAYS
 ORDER BY MTI_USED DESC
 FETCH FIRST 50 ROWS ONLY

Two filters, and they clear away 99% of the noise. MTI_USED > 0 keeps only the cases where the machine really built a temporary index; not opinions, facts. A recent LAST_ADVISED keeps only what is happening now, not the archaeology. The consolidation with the prefix rule, further down, you then do by hand over fifty rows instead of millions.

Why that list grew like that

Db2 for i prunes old rows by itself, but the mechanism is recent: the global variable SYSIBMADM.QIBM_SYSIXADV_BY_DAYS says how many days to keep a row after its most recent update, and it arrives with IBM i 7.6 TR1 and 7.5 TR7. Before that there was no pruning at all: rows simply piled up.

How many days are set now:

VALUES SYSIBMADM.QIBM_SYSIXADV_BY_DAYS

The default is 365. On a store that has already grown out of hand it makes sense to lower it:

CREATE OR REPLACE VARIABLE SYSIBMADM.QIBM_SYSIXADV_BY_DAYS
      INTEGER DEFAULT 180

It has to be re-created, not set. A SET inside a session applies only to that job: every other job goes on seeing the default. To change it for real you need the CREATE OR REPLACE VARIABLE above, and you check by re-running the VALUES.

If the system is on an earlier release, the variable does not exist and there is no automatic pruning. In that case the only cleanup is from Navigator, which under Index Advisor offers Clear All Advised Indexes (wipes everything and starts from a blank sheet) along with Condense and Prune, which removes advice referring to tables that no longer exist.

Wiping is a decision, not housekeeping. With Clear All you lose the history, including the tables that only get attention at month end or year end. If you do it, do it knowing that for a few weeks the advice will be thin and partial, and that it is better done after putting aside, with a CREATE TABLE ... AS, the rows with a high MTI_USED, which are the ones you cared about.

Second step: look at the raw advice, but for the right signals

SELECT TABLE_NAME, KEY_COLUMNS_ADVISED,
       TIMES_ADVISED, MTI_USED, MTI_CREATED,
       FIRST_ADVISED, LAST_ADVISED, ESTIMATED_CREATION_TIME
  FROM QSYS2.SYSIXADV
 WHERE TABLE_SCHEMA = 'PRODLIB'
 ORDER BY MTI_USED DESC, TIMES_ADVISED DESC
 FETCH FIRST 20 ROWS ONLY

Note the ordering: MTI_USED before TIMES_ADVISED.

MTI_USED counts how many times the system actually built and used a temporary index to cope. It is the strongest signal there is, because it is not an optimizer opinion: it is work the machine really did, repeatedly, because you had not given it the tool.

A high TIMES_ADVISED counts, but has to be read together with the dates. Advice with TIMES_ADVISED = 300 packed between a FIRST_ADVISED and a LAST_ADVISED of a single afternoon is nearly always somebody re-running the same query in a loop. Three hundred times spread over six months is another matter.

Isolated advice, with TIMES_ADVISED = 1 and a date three months old, does not deserve a permanent index on a production table.

The three columns that answer "is it worth it"

Frequency and MTI tell you how often. These tell you how much it weighs, and they are what separates a useful index from a pointless one:

MOST_EXPENSIVE_QUERY: how much the worst of the queries generating that advice cost. Advice born from a query taking a few milliseconds changes nobody's life, even if it shows up a hundred times. Advice born from a forty-second query deserves attention even if it shows up three times.

TABLE_SIZE and MAX_ROW: how big the table is. A missing index on a four-hundred-row reference table is a curiosity: the system scans the lot and nobody notices. The same missing index on a ten-million-row transaction table is why the ERP feels slow.

MTI_USED_FOR_STATS: this is the nuance that avoids a mistake. A temporary index may be built to run the query, or only because the optimizer wanted to estimate how many rows would come back. Those are different needs: the first asks for an index, the second is often answered by statistics, which cost far less. A high MTI_USED with MTI_USED_FOR_STATS near zero is the clean case where the index is genuinely needed.

The final question, in short, is not "how many times was it advised" but "how much time does it give me back, and over how many rows".

Third step: consolidate by hand, using the prefix rule

This is the step the advisor does not do for you, and it is worth more than all the others.

An index on (A, B, C) also serves queries looking up by (A) and by (A, B). Keys are read from the left: any prefix is covered.

So if you have three pieces of advice for the same table:

(CUSTNO)
(CUSTNO, ORDDATE)
(CUSTNO, ORDDATE, STATUS)

these are not three indexes. They are one, the third, and it covers all three cases.

Mind the reverse, which is the mirror-image mistake: (ORDDATE, CUSTNO) is not covered by (CUSTNO, ORDDATE). Column order matters, and is not interchangeable.

The question to ask of every group of advice is always the same: what is the smallest number of indexes covering all these prefixes? Nearly always the answer is one or two.

What an index costs, concretely

An index is not free when you are not using it. It is free only if nobody ever writes to that table.

On every write. Every INSERT, UPDATE and DELETE has to maintain every index on the table. On an overnight batch inserting two million rows, the difference between three indexes and twelve is not theoretical: it is the batch finishing at 3 instead of 1, and sooner or later not finishing before opening time.

And it is the worst kind of fault to diagnose, because the symptom appears far from the cause: somebody created indexes to speed up a report, and three weeks later somebody else wonders why order loading got slow.

On space, and on everything that crosses it. Indexes take disk, and lengthen save, restore and reorganise times. On a large table, one more index is one more piece to save every night.

On the optimizer itself. The more indexes there are, the more plans it has to weigh. The extreme case (dozens of near-identical indexes on the same table) can make choosing the plan cost more than the gain.

Fourth step: create, then check it was used

The index is created normally:

CREATE INDEX PRODLIB.ORDERS_CUS_DATE_STATUS
    ON PRODLIB.ORDERS (CUSTNO, ORDDATE, STATUS)

But then it has to be verified that it was actually used. Creating an index and not checking is the same as not having created it, with the disadvantage of paying for it on every write:

SELECT INDEX_NAME, LAST_QUERY_USE, QUERY_USE_COUNT
  FROM QSYS2.SYSINDEXSTAT
 WHERE TABLE_SCHEMA = 'PRODLIB'
   AND INDEX_NAME = 'ORDERS_CUS_DATE_STATUS'

If after a few days of normal work QUERY_USE_COUNT is still zero, that index is serving nobody. Drop it.

One at a time. Create an index, let a real work cycle go by — a day, a month end, an overnight batch — and look. If you create six together and something gets worse, you will not know which of the six.

How to repair it, if the damage is done

If somebody has already created everything the advisor suggested, there is a way back, and it is the same view:

SELECT TABLE_NAME, INDEX_NAME,
       LAST_QUERY_USE, QUERY_USE_COUNT,
       LAST_STATISTICS_USE, QUERY_STATISTICS_COUNT
  FROM QSYS2.SYSINDEXSTAT
 WHERE TABLE_SCHEMA = 'PRODLIB'
   AND (QUERY_USE_COUNT = 0 OR QUERY_USE_COUNT IS NULL)
 ORDER BY TABLE_NAME

Indexes with zero counts and empty last-use dates are candidates for deletion: no query has chosen them, and every write pays for them.

Look at the statistics columns too. An index may never have been chosen to run a query and still serve the optimizer for estimating how many rows would come back, that is what LAST_STATISTICS_USE and QUERY_STATISTICS_COUNT tell you. Before deleting, check those are at zero as well.

Two cautions before firing DROP INDEX:

• The counts reset at IPL. An index "never used" on a machine restarted yesterday proves nothing. You need an observation window covering a full cycle: month-end close uses indexes nobody touches the rest of the year.
• Not everything that is an index is yours. Key constraints and DDS logical files show up in these views. Deleting the index underpinning a primary key is not housekeeping.

---

Releases. QSYS2.SYSIXADV has been present for many releases. The MTI_USED, MTI_CREATED and MTI_USED_FOR_STATS columns were added later: if the query answers SQL0206 (column not found) drop them and the rest works.

QSYS2.CONDENSEDINDEXADVICE is the condenser view, and carries the same columns as the raw advice plus the judgement ones: MOST_EXPENSIVE_QUERY, AVERAGE_QUERY_ESTIMATE, TABLE_SIZE, MAX_ROW, LAST_MTI_USED. To find out what you actually have on your system:

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE
  FROM QSYS2.SYSCOLUMNS
 WHERE TABLE_SCHEMA = 'QSYS2'
   AND TABLE_NAME = 'CONDENSEDINDEXADVICE'
 ORDER BY ORDINAL_POSITION

SYSIBMADM.QIBM_SYSIXADV_BY_DAYS, which prunes old advice by itself, arrives with 7.6 TR1 and 7.5 TR7: on earlier releases it does not exist and the list grows without limit.

QSYS2.SYSINDEXSTAT covers SQL indexes only; to see logical files and key constraints as well, use QSYS2.SYSPARTITIONINDEXSTAT.

Sources.

• IBM — QSYS2.SYSINDEXSTAT View
• IBM — Condensing index advice
• IBM — QSYS2.INDEX_ADVICE procedure
• IBM Documentation — SYSPARTITIONINDEXES
• IBM — SYSIBMADM.QIBM_SYSIXADV_BY_DAYS global variable