It is important to choose the correct index type for your column data. Sybase IQ provides some indexes automatically—an index on all columns that optimizes projections, and an HG index for UNIQUE and PRIMARY KEYS and FOREIGN KEYS. While these indexes are useful for some purposes, you may need other indexes to process certain queries as quickly as possible.
The Sybase IQ query optimizer features an index advisor that generates messages when the optimizer would benefit from an additional index on one or more columns in your query. To activate the index advisor, set the INDEX_ADVISOR option ON. Messages print as part of a query plan or as a separate message in the message log (.iqmsg) if query plans are not enabled, and output is in OWNER.TABLE.COLUMN format. For details, see “INDEX_ADVISOR option,” in Chapter 2, “Database Options,” in Reference: Statements and Options.
You should consider creating either an LF or HG index on grouping columns referenced by the WHERE clause in a join query if the columns are not using enumerated FP storage. The Sybase IQ optimizer may need metadata from the enumerated FP or HG/LF index to produce an optimal query plan. Non-aggregated columns referenced in the HAVING clause may also benefit from a LF or HG index to help with query optimization. For example:
SELECT c.name, SUM(l.price * (1 - l.discount))
FROM customer c, orders o, lineitem l
WHERE c.custkey = o.custkey
AND o.orderkey = l.orderkey
AND o.orderdate >= "1994-01-01"
AND o.orderdate < "1995-01-01"
GROUP by c.name
HAVING c.name NOT LIKE "I%"
AND SUM(l.price * (1 - l.discount)) > 0.50
ORDER BY 2 desc
Note that adding indexes increases storage requirements and load time and should only be done if there is a net benefit to query performance.
