It is important to choose the correct index type for your column data. Sybase IQ provides some indexes automatically—a default 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 need other indexes to process certain queries as quickly as possible. Sybase IQ chooses the best index type for you when there are multiple index types for a column.
Adaptive Server IQ’s 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 IQ message log if query plans are not enabled. For details, see INDEX_ADVISOR option in “Database Options,” Sybase IQ Reference Manual.
You should create either an LF or HG index in addition to the default index on LF or HG on grouping columns referenced by the WHERE clause in a join query. Sybase IQ cannot guarantee that its query optimizer will produce the best execution plan if some columns referenced in the WHERE clause lack either an LF or HG index. Non-aggregated columns referenced in the HAVING clause should also have the LF or HG index in addition to the default index. 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
In addition to the default index, all columns in this example beside l.price and l.discount should have an LF or HG index.
