Generates messages suggesting additional column indexes that may improve performance of one or more queries.
ON, OFF
OFF
Can be set temporary (for a connection), for a user, or for the PUBLIC group. Takes effect immediately.
When set ON, the index advisor prints index recommendations as part of the Sybase IQ query plan or as a separate message in the Sybase IQ 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 Sybase IQ 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 Sybase IQ message
file as separate messages. Advice will, however, continue to be
displayed on query plans in the Sybase IQ 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. Sybase 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)
“FP_LOOKUP_SIZE option”, “INDEX_ADVISOR_MAX_ROWS option”, “MINIMIZE_STORAGE option”, and “QUERY_PLAN option”.
“sp_iqindexadvice procedure,” “sp_iqindexmetadata procedure,” “sp_iqrebuildindex procedure,” and “sp_iqrowdensity procedure,” Chapter 7, “System Procedures,” in Reference: Building Blocks, Tables, and Procedures
“The Fast Projection (FP) default index type” in Chapter 6, “Using Sybase IQ Indexes” in System Administration Guide: Volume 1
Message logging in Chapter 1, “Overview of Sybase IQ System Administration” in the System Administration Guide: Volume 1
“Using IQ UNIQUE constraint on columns,” Chapter 9, “Ensuring Data Integrity,” in System Administration Guide: Volume 1
