Each type of column index is designed to speed processing of a certain kind of data.
Sybase IQ always uses the fastest index available for the current query or join predicate. If you did not create the index types the query optimizer would ideally like to use for a column, Sybase IQ can still resolve queries involving the column, but response time may be slower than it would be with the correct index type(s).
When you designate a column or set of columns as either a PRIMARY KEY or UNIQUE, Sybase IQ creates a High_Group index for it automatically. Choose one PRIMARY KEY from all UNIQUE constraints for the table. Both PRIMARY KEY and UNIQUE constraints do not allow nulls; however, a unique index would allow them. UNIQUE constraints also provide hints on column constraints to the query optimizer. The System Administration Guide: Volume 1 describes when to use each type of index and the space and time trade-offs of each.
These index types are unique to Sybase IQ data and cannot be applied to SQL Anywhere tables. For more information, see the System Administration Guide: Volume 1.
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 for more than 1,000 distinct values or for a table with less than 25,000 rows.) |
High_Non_Group (HNG) |
A non value-based bitmap index ideal for most high-cardinality DSS operations involving ranges or aggregates. |
Low_Fast (LF) |
A value-based bitmap for processing queries on low-cardinality data. (Recommended 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. |
Sybase IQ assumes you will add either a LF or a HG index to every column in a WHERE clause and in a GROUP BY clause.