Indexes are the primary tuning mechanisms inside
SAP Sybase IQ. Knowing when and where to use indexes can make your
queries run faster.
Always use
indexes on:
- Join columns (HG index regardless of cardinality)
- Searchable columns (HG or LF index based on cardinality)
DATE, TIME, and DATETIME/TIMESTAMP columns
(DATE, TIME,
DTTM)
The DATE, TIME, or
DATETIME/TIMESTAMP column should also have an LF or HG index depending
on data cardinality.
- If you are uncertain whether the column will be used heavily,
place an LF or HG index on the column. Workload Management can
subsequently be enabled to monitor the use of indexes.
- Use PRIMARY KEY, UNIQUE CONSTRAINT, or
UNIQUE HG indexes where appropriate, as they provide
SAP Sybase IQ with additional information about the unique
data in the indexed column(s).
- A column with an HNG or CMP index should have a corresponding
LF or HG index
- Indexes are not needed on columns whose data is ONLY returned to the
client (projected)