Each type of column index is designed to speed processing of a certain kind of data.
These index types are unique to column-based IQ data and cannot be applied to tables in the catalog store.
SAP Sybase IQ assumes you add either an LF or HG index to every column mentioned in a WHERE clause and in a GROUP BY clause.
When you designate a column or set of columns as either a PRIMARY KEY or UNIQUE, the database server automatically creates a High_Group index for it. Choose one PRIMARY KEY from all UNIQUE constraints for the table. Neither PRIMARY KEY nor UNIQUE constraints allow nulls; however, a unique index does allow them. UNIQUE constraints also provide hints on column constraints to the query optimizer.
The database server always uses the fastest index available for the current query or join predicate. If you have not created the index types the query optimizer would ideally use for a column, the server can still resolve queries involving the column, but response time may be slower than it would be with the correct index type or types.
| Index Type | Purpose |
|---|---|
| Compare (CMP) | Stores the binary comparison (<, >, =, <=, >=, or NE) of any two columns with identical data types, precision, and scale. |
| DATE | An index on columns of data type DATE used to process queries involving date quantities. |
| Datetime (DTTM) | An index on columns of data type DATETIME or TIMESTAMP used to process queries involving datetime quantities. |
| High_Group (HG) | An enhanced B-tree index to process equality and group by operations on high-cardinality data. Recommended only for more than 1,000 distinct values or for a table with less than 25,000 rows. |
| High_Non_Group (HNG) | A nonvalue-based bitmap index ideal for most high-cardinality DSS operations involving ranges or aggregates. |
| Low_Fast (LF) | A value-based bitmap index for processing queries on low-cardinality data. Recommended only for up to 1,000 distinct values and more than 25,000 rows in the table. Can support up to 10,000 distinct values. |
| TIME | An index on columns of data type TIME used to process queries involving time quantities. |
| WD | Used to index keywords by treating the contents of a CHAR, VARCHAR, or LONG VARCHAR column as a delimited list. |
| Text | Used to index terms (words) and their positions. Provides ability to search for individual terms, phrases, pairs of terms within specified distances and given order, as well as combinations of these conditions. |