When you create a table and specify its columns, Sybase IQ automatically creates certain default storage structures to optimize query processing. If you know what kinds of queries you plan to run, you can add multiple indexes to any column.
It is faster to create all the indexes needed before you insert any data into your database. You can drop any of the optional indexes later if you decide you don't need it.
WARNING! Be sure to verify that the index is not important before you drop it. Different queries use different indexes, even when they appear to be similar. Dropping the wrong indexes may adversely affect performance. Capture queries, run times, and query plans to determine which indexes are required.
Each type of column index is designed to speed processing of a certain kind of data.
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. |
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.
Sybase IQ assumes you will add either a LF or a 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, 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.
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).
For this tutorial, appropriate index types and storage locations have been determined for you. Simply follow the steps to index columns in your table.
Adding a Low Fast index to a column
Connect to the iqdemo database, if not connected.
Do one of the following:
In Folder view, right-click Indexes, point to New, choose Index on IQ Table...
In Tasks view, double-click the Indexes folder, and choose Create an index from the Index Design tasks.
Select the Customers table from the list, name the new index IQ_IDX_LF_ID, then click Next.
Choose the Low Fast option as the Index type, then click Next.
The default number of records to add before notification is sufficient for this tutorial. For details about the notify count, see the System Administration Guide: Volume 1.
On the dbspace dialog, choose an appropriate dbspace to store the index. For this tutorial, choose iq_main, then click Next.
On the Columns screen, select the ID column and click Add, then Next.
Type the comment Low Fast index for
id column in the Comment box and click Finish
to create the index. The Indexes container is updated with the new
index.
Now add a Low Fast index to the State column, using steps 3 through 7.
The remaining columns in the table do not require any of the optional indexes. The default storage structures are sufficient.
You can use the CREATE INDEX command instead of Sybase Central to create column indexes if you prefer. For syntax, see Reference: Statements and Options.
