INDEX_ADVISOR
Option
Generates messages suggesting additional column indexes that may improve performance of one or more queries.
ON, OFF
OFF
Option can be set at the database (PUBLIC) or user level. At the database level, the value becomes the default for any new user, but has no impact on existing users. At the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.
Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.
When set ON, the index advisor prints index recommendations as part of the query plan or as a separate message in the message log file, if query plans are not enabled. These messages begin with the string “Index Advisor:” and you can use that string to search and filter them from a message file. The output is in OWNER.TABLE.COLUMN format.
Set both INDEX_ADVISOR and INDEX_ADVISOR_MAX_ROWS to accumulate index advice.
When INDEX_ADVISOR_MAX_ROWS is set ON, index advice will not be written to the message file as separate messages. Advice will, however, continue to be displayed on query plans in the message file.
|
Situation |
Recommendation |
|---|---|
|
Local predicates on a single column where an HG, LF, HNG, DATE, TIME or DATETIME index would be desirable, as appropriate. |
Recommend adding an <index-type> index to column <col> |
|
Single column join keys where an LF or HG index would be useful. |
Add an LF or HG index to join key <col> |
|
Single column candidate key indexes where a HG exists, but could be changed to a unique HG or LF |
Change join key <col> to a unique LF or HG index |
|
Join keys have mismatched data types, and regenerating one column with a matched data type would be beneficial. |
Make join keys <col1> and <col2> identical data types |
|
Subquery predicate columns where an LF or HG index would be useful. |
Add an LF or HG index to subquery column <col> |
|
Grouping columns where an LF or HG index would be useful. |
Create an LF or HG index on grouping column <col> |
|
Single-table intercolumn comparisons where the two columns are identical data types, a CMP index are recommended. |
Create a CMP index on <col1>, <col2> |
|
Columns where an LF or HG index exists, and the number of distinct values allows, suggest converting the FP to a 1 or 2-byte FP index. |
Rebuild <col> with ‘optimize storage=on’ |
|
To support the lookup of default indexes three bytes wide |
Rebuild your FP Index as a 3-byte FP with an IQ UNIQUE constraint value of 65537 |
It is up to you to decide how many queries benefit from the additional index and whether it is worth the expense to create and maintain the indexes. In some cases, you cannot determine how much, if any, performance improvement results from adding the recommended index.
For example, consider columns used as a join key. SAP IQ uses metadata provided by HG or LF indexes extensively to generate better/faster query plans to execute the query. Putting an HG or LF index on a join column without one makes the IQ optimizer far more likely to choose a faster join plan, but without adding the index and running the query again, it is very hard to determine whether query performance stays the same or improves with the new index.
Index advisor output with query plan set OFF:
I. 03/30 14:18:45. 0000000002 Advice: Add HG or LF index on DBA.ta.c1 Predicate: (ta2.c1 < BV(1))
Index advisor output with query plan set ON:
This method accumulates index advisor information for multiple queries, so that advice for several queries can be tracked over time in a central location.
I. 03/30 14:53:24. 0000000008 [20535]: 6 ...#03: Leaf I. 03/30 14:53:24. 0000000008 [20535]: Table Name: tb I. 03/30 14:53:24. 0000000008 [20535]: Condition 1 (Invariant): (tb.c3 =tb.c4) I. 03/30 14:53:24. 0000000008 [20535]: Condition 1 Index Advisor: Add a CMP index on DBA.tb (c3,c4)