When you create a table and specify its columns, Sybase IQ automatically creates certain default storage structures to optimize query processing. Knowing what kinds of queries you plan to run, you can add multiple indexes to any column.
Some additional tasks are required for multiplex databases.
Please see the Sybase IQ System Administration Guide instead of this chapter
if you need to index data in a multiplex.
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 or VARCHAR column as a delimited list. |
These index types are unique to Sybase IQ data and cannot be applied to Anywhere tables. For more information, see the Sybase IQ System Administration Guide.
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 Sybase IQ System Administration Guide 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 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 asiqdemo database, if not connected.
Select the Indexes container and click the New Index
toolbar button
or
choose Alt+F,N,I.
Type the name ASIQ_IDX_LF_ID. You may choose any index name unique in the database. Descriptive names are best.
Select the customer table from the list and click Next.
On the Type screen, select the Low Fast option.
The default number of records to add before notification is sufficient for this tutorial. For details about the notify count, see the Sybase IQ System Administration Guide.
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 the Sybase IQ Reference Manual.
