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	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.

In the Perspective Resources view, select the resource and select Resource > Administration Console.
In the left pane, select IQ Servers > Schema Objects > Tables.
Select Tables or Global Temporary Tables.
Select a 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 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.