Creating a Materialized View Index

Create an index on a materialized view.

Prerequisites

Database Version	Materialized View Index Privileges
SAP Sybase IQ 15.3 and 15.4	You must have one of: DBA authority You own the underlying database object of the index
SAP Sybase IQ 16.0	You must have one of: CREATE ANY OBJECT system privilege CREATE permission on the dbspace, along with one of: CREATE ANY INDEX system privilege You own the materialized view index

The SAP Sybase IQ resource is authenticated and running.
In a multiplex configuration, the coordinator node is running.

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 > Views, and then select Materialized Views.
Select a materialized view 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 Materialized View Properties view appears.
In the left pane, select Indexes.
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.