Choose the correct column index type to make your queries run faster.
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 index advisor 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 “Database Options,” in Reference: Statements and Options.
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
Adding indexes increases storage requirements and load time. Add indexes only if there is a net benefit to query performance.
Users frequently need to see the data from more than one table at once. This data can be joined at query time, or in advance by creating a join index. Sometimes you can improve query performance by creating a join index for columns that are joined in a consistent way.
Because join indexes require substantial time and space to load, create them only for joins needed on a regular basis. Sybase IQ join indexes support one-to-many and one-to-one join relationships.
When you delete data rows, Sybase IQ creates a version page for each database page that contains any of the data being deleted. The versions are retained until the delete transaction commits. For this reason, you may need to add disk space when you delete data. See “Overlapping versions and deletions” on for details.