When you load data into a table, Sybase IQ stores data by column rather than by row, for each column in the table. The column orientation gives IQ indexes important advantages over traditional row-based indexing. Column storage structures your data according to the attributes you are interested in tracking. In a data warehousing environment, usually you want to look at specific attributes of thousands or millions of rows of data, rather than complete, single rows of data that typically are the focus in transaction processing. Column storage optimizes your ability to perform selections or calculations on the attributes you care about.
The default column storage structure that Sybase IQ creates for each column is actually an index optimized for storing and projecting data. Depending on the size of your database, the disk space available to you, and the type of queries your users submit, you almost certainly want to supplement this default index with one or more of the Sybase IQ bitwise index types. You can choose from several column index types. The column indexes you define are created as part of each individual table.
Besides the column indexes, Sybase IQ also allows you to define join indexes. Join indexes are optimized for joining related tables. You may want to create a join index for any set of columns that your users commonly join to resolve queries. Column indexes underlie any join indexes involving those columns.
The first half of this chapter discusses column indexes. The second half of this chapter discusses join indexes; see “Using join indexes” for details.
A default index that optimizes projections is created by Sybase IQ for all columns.
Columns with fewer than 65536 unique values can be stored in an optimized default index that significantly reduces storage requirements. This format supports improved performance by the IQ optimizer and for the aggregate functions SUM, SUM DISTINCT, MAX, MIN, and COUNT DISTINCT. It is available for:
Any column where IQ UNIQUE() is specified
All columns created when the MINIMIZE_STORAGE database option is ON
To achieve maximum query performance, however, you should choose one or more additional index types for most columns that best represent the cardinality and usage of column data:
Compare or CMP Stores the binary comparison (<, >, or =) of any two distinct 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 or DTTM An index on columns of data type DATETIME or TIMESTAMP used to process queries involving datetime quantities.
High_Group or 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)
High_Non_Group or HNG A non value-based bitmap index ideal for most high-cardinality DSS operations involving ranges or aggregates
Low_Fast or LF A value-based bitmap for processing queries on low-cardinality data (recommended for up to 1,000 distinct values. 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.
Select column indexes according to the type of data in the column and your intended operations for the column data. In general, you can use any index or combination of indexes on any column. However, there are some exceptions.
To take advantage of the High_Non_Group index types for columns with nonintegral numeric data, use the NUMERIC or DECIMAL data types, which support up to 254 digits to the left or right of the decimal point. Be aware that some index types are incompatible, and that creating indexes you don't need wastes a lot of disk space. Read the sections that follow for details on how to select an index.
When a column is designated as a FOREIGN KEY, PRIMARY KEY, or UNIQUE, Sybase IQ creates a High_Group index for it automatically. For each foreign key, Sybase IQ creates a non-unique High_Group index.
You can also create a High_Group index on a
set of columns explicitly. For details, see CREATE
INDEX statement in Sybase IQ Reference Manual.
You may also want to define additional indexes on your columns for best performance. Sybase IQ uses the fastest index available for the current query or join predicate. If you do not create the correct types of indexes for a column, Sybase IQ can still resolve queries involving the column, but response may be slower than it would be with the correct index type(s).
If multiple indexes are defined on a particular column, Sybase IQ builds all the indexes for that column from the same input data.
If you set the INDEX_ADVISOR option on your database, Sybase IQ issues messages in the message log or query plan to suggest additional index(es) that might improve performance. Messages focus on these areas:
local predicate columns
single-column join key columns
correlated subquery columns
grouping columns
For details, see “INDEX_ADVISOR option”in Chapter 2, “Database Options,” of Sybase IQ Reference Manual.
If you decide to follow the recommendations, you must create the indexes yourself.
If you discover later that an additional index is needed, you can always add indexes. However, it is much faster to create all the appropriate indexes before you insert any data.
You can only rename or alter an index in a base table or global temporary table with the owner type USER. See the ALTER INDEX statement in the Sybase IQ Reference Manual for more information on renaming indexes and changing foreign key role names.
You can drop any optional index if you decide that you do not need it. See the DROP INDEX command in the Sybase IQ Reference Manual for more information on dropping indexes.
You may want to remove a foreign key constraint, but
retain the underlying HG index. A non-unique HG index
can provide query performance improvement, but may be expensive
to build.
Note that ALTER TABLE DROP FOREIGN KEY CONSTRAINT does not remove the automatically-created non-unique HG index. You cannot drop a primary key if associated foreign keys remain. To remove such an index, drop it explicitly after issuing the ALTER TABLE DROP FOREIGN KEY command.
