Creating an Index on an IQ System Store Table

Create an index on an IQ system store (catalog store) table.

Prerequisites

Database Version	Table Index Privileges
SAP Sybase IQ 15.3 and 15.4	Requires CREATE permission on the specified dbspace. Also requires one of: DBA authority REFERENCE permission on the underlying table of the index. You own the underlying table of the index.
SAP Sybase IQ 16.0	Requires CREATE permission on the specified dbspace. Also requires one of: CREATE ANY INDEX system privilege. CREATE ANY OBJECT system privilege. REFERENCE permission on the underlying table of the index. You own the underlying table of the index.

The SAP Sybase IQ resource is authenticated and running.
In a multiplex configuration, the coordinator node is running.
The selected table is a base or global temporary table.

Task

Indexes improve search performance on the indexed column or columns. Indexes take up space in the database, however, and slow down the performance of insert, delete, and update operations.

When creating indexes, the order in which you specify the columns becomes the order in which the columns appear in the index. Duplicate references to column names in the index definition are not allowed.

Navigate to the Tables Properties page of an IQ system store (catalog store) table.
In the left pane, select Indexes.

Note: Indexes does not appear for Proxy tables as they are not supported.
Click New.
On the Index Name page, specify a name for the index.
Click Next.
On the Columns page, highlight one or more columns and click Add Asc or Add Desc

Note: Use Shift-click or Control-click to select multiple columns.
1. In the Columns in index list, highlight a single column and click the up and down buttons to reposition the column.
2. Highlight one or more columns and click Remove to remove columns from the index.
Click Next.
On the Uniqueness page, indicate if the index is unique.
Click Next.
On the Index Clustering page, indicate if the index is clustered and the dbspace to store the index.
Click Next.
(Optional) On the Comment page, enter a text comment for the index.
Click Finish.
The index appears in the properties view, but is not saved to the database.
Do one of the following:
- Click Apply to upload any property changes to the database and remain in the properties view.
- Click OK to upload any property changes to the database and exit the properties view.
- Click Cancel to cancel any unsaved property changes and exit the properties view.