INDEX_ADVISOR Option

Generates messages suggesting additional column indexes that may improve performance of one or more queries.

Allowed Values

ON, OFF

Default

OFF

Scope

Can be set temporary (for a connection), for a user, or for the PUBLIC group. Takes effect immediately.

Description

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.

Note: 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.

Index Advisor
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.

Example

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:

Note: 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)