You should know in advance how data in the columns will generally be queried. For example:
Will the column be part of a join predicate?
If the column has a high number of unique values, will the column be used in a GROUP BY clause, be the argument of a COUNT DISTINCT, and/or be in the SELECT DISTINCT projection?
Will the column frequently be compared with another column of the same data type, precision, and scale?
Often, the type of data in a column gives a good indication how the column will be used. For example, a date column will probably be used for range searches in WHERE clauses, and a column that contains prices or sales amounts will probably be used in the projection as an argument for aggregate functions (SUM, AVG, and so on).
Sybase IQ can still resolve queries involving a column
indexed with the wrong index type, although it may not do so as
efficiently.
This table shows recommended index types based on the query. The index that is usually fastest for each query is listed first, the slowest last. These recommendations should not be your only criteria for choosing an index type. You should also consider the number of unique values and disk space. See the other tables in this section.
Type of Query Usage |
Recommended Index Type |
|---|---|
In a SELECT projection list |
Default |
In calculation expressions such as SUM(A+B) |
Default |
As AVG/SUM argument |
HNG, LF, HG, Default |
As MIN/MAX argument |
LF, HG, HNG |
As COUNT argument |
Default |
As COUNT DISTINCT, SELECT DISTINCT or GROUP BY argument |
LF, HG, Default |
As analytical function argument |
LF, Default |
If field does not allow duplicates |
HG |
Columns used in ad hoc join condition |
Default, HG, LF, |
Columns used in a join index |
HG, LF |
As LIKE argument in a WHERE clause |
Default |
As IN argument |
HG, LF |
In equality or inequality (=, !=) |
HG, LF; also CMP |
In range predicate in WHERE clause (>, <, >=, <=, BETWEEN |
LF, HG, or HNG; also CMP, DATE, TIME, DTTM |
In DATEPART equality, range, and IN list predicates |
DATE, TIME, DTTM |
In a CONTAINS predicate |
WD |
While HNG is recommended, in certain cases LF or HG is faster, and is often used in place of HNG. HNG tends to give consistent performance, while the performance of LF or HG with ranges depends on the size of the range selected.
For optimal query performance, columns used in join predicates, subquery predicates, GROUP BY and DISTINCT clauses should have either a HG or LF index, since IQ has no statistics other than the index for the optimizer to use. Use HG for high cardinality and LF for low cardinality columns, except for tables with fewer than 100,000 rows which should have HG.
These estimates are generally valid; however, other factors can take precedence:
For range predicates, the number of unique values is a more important factor.
With the set functions COUNT, COUNT DISTINCT, SUM, MIN, MAX, and AVG, in order to use any index other than the default, the entire query must be resolvable using a single table or join index.
BIT data can only be used in the default index; VARBINARY data greater than 255 bytes can only be used in the default and CMP index types; CHAR and VARCHAR data greater than 255 bytes can only be used in the default, CMP, and WD index types; LONG VARCHAR data can only be used in the default and WD index types; only DATE data can be used in the DATE index type; only TIME data can be used in the TIME index type; only DATETIME and TIMESTAMP data can be used in the DTTM index type.
