Creating an Index on an IQ Store Table

Create an index on an IQ store (main store) table.

Prerequisites

Database Version	Table Index Privileges
SAP Sybase IQ 15.3 and 15.4	You must have CREATE permission on the specified dbspace. You must also have 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	You must have CREATE permission on the specified dbspace. You must also have 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.

In the Perspective Resources view, select the resource, and select Resource > Administration Console.
In the left pane, expand IQ Servers > Schema Objects > Tables, and then select Tables or Global Temporary Tables.
Select an IQ main store table from the right pane and either:
- Click the arrow to the right of the name and select Properties, or
- From the Administration Console menu bar, select Resource > Properties.
The Table Properties view appears.
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 Index Type page, select the type of index. Enter a value in the notify count field if you want to be notified after a specified number of inserts to the index. Default value is 100000. To disable notification, enter zero (0) in the field. Click Next.
(For index type Word only) (Optional) Enter a word separator character and a maximum word length permitted in the index. Default value is 255 characters. Click Next.
On the Dbspace page, indicate if the index is to be unique and the dbspace in which to store 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.
(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:
- Click OK to update any changes to the database and exit the properties view.
- Click Apply to update any changes to the database, but remain in the properties view.
- Click Cancel to cancel any changes not updated to the database and exit the properties view.