Every time the optimizer wanted an index that was not there, it wrote it down. The advice sits there, and nobody reads it.
SELECT TABLE_NAME, KEY_COLUMNS_ADVISED,
TIMES_ADVISED, ESTIMATED_CREATION_TIME,
LAST_ADVISED, MTI_USED, MTI_CREATED
FROM QSYS2.SYSIXADV
WHERE TABLE_SCHEMA = 'PRODLIB'
ORDER BY TIMES_ADVISED DESC
FETCH FIRST 20 ROWS ONLY
The index is then created normally:
CREATE INDEX PRODLIB.ORDERS_CUSTOMER_DATE
ON PRODLIB.ORDERS (CUSTNO, ORDDATE)
Warning
not all advice should be followed. A high TIMES_ADVISED counts for more than
an isolated suggestion, which often comes from a query run once by hand. Every index is paid
for on every write to the table: creating twenty because the system named them makes reads
faster and inserts slower.
Tip
MTI_USED shows how many times the system built a temporary index on the fly to
cope. A high number there is the strongest signal that the index is genuinely needed.
Releases. QSYS2.SYSIXADV has been present for many releases. The MTI_USED and
MTI_CREATED columns, which concern temporary indexes built on the fly, were added later: if
the query answers SQL0206 — column not found — drop them and the rest works.
Comments
No comments yet. Be the first to comment!
You need an account to comment. Log in · Sign up