Generates messages suggesting additional column indexes that may improve performance of one or more queries.
ON, OFF
Can be set temporary (for a connection), for a user, or for the PUBLIC group. Takes effect immediately.
OFF
When set ON, the index advisor prints index recommendations as part of the IQ query plan or as a separate message in the 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.
Situation |
Recommendation |
|---|---|
Local predicates on a single column where an HG, LF, HNG, DATE, TIME or DATETIME index would be desirable, as appropriate. |
Recommends adding an <index-type> index to column <col> |
Single column join keys where a 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 a LF or HG index would be useful. |
Add an LF or HG index to subquery column <col> |
Grouping columns where a 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 will be recommended. |
Create a CMP index on <col1>, <col2> |
Columns where a LF or HG index exist, and the number of distinct values will allow, suggest converting the FP to a 1 or 2-byte FP index. |
Rebuild <col> with ‘optimize storage=on’ |
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.
“Message logging” on page 24 in Chapter 1, “Overview of Sybase IQ System Administration,”of the Sybase IQ System Administration Guide
