CREATE INDEX statement

Use this statement to create an index on a specified table or materialized view. Indexes can improve database performance.

Syntax 1 - Creating an index on a table

CREATE [ VIRTUAL ] [ UNIQUE ] [ CLUSTERED ] INDEX index-name
  ON [ owner.]table-name
    ( column-name [ ASC | DESC ], ...
      | function-name ( argument, ... ] ) AS column-name )
  [ { IN | ON } dbspace-name ]
  [ FOR OLAP WORKLOAD ]

Syntax 2 - Creating an index on a materialized view

CREATE [ VIRTUAL ] [ UNIQUE ] [ CLUSTERED ] INDEX index-name
  ON [ owner.]materialized-view-name
    ( column-name [ ASC | DESC ], ...)
  [ { IN | ON } dbspace-name ]
  [ FOR OLAP WORKLOAD ]

Parameters

VIRTUAL clause The VIRTUAL keyword is primarily for use by the Index Consultant. A virtual index mimics the properties of a real physical index during the evaluation of execution plans by the Index Consultant and when the PLAN function is used. You can use virtual indexes together with the PLAN function to explore the performance impact of an index, without the often time-consuming and resource-consuming effects of creating a real index.
Virtual indexes are not visible to other connections, and are dropped when the connection is closed. Virtual indexes are not used when evaluating plans for the actual execution of queries, and so do not interfere with performance.

Virtual indexes have a limit of four columns.

See Obtain Index Consultant recommendations for a query, and Index Consultant.
CLUSTERED clause The CLUSTERED attribute causes rows to be stored in an approximate key order corresponding to the index. While the database server makes an attempt to preserve key order, total clustering is not guaranteed.
If a clustered index exists, the LOAD TABLE statement inserts rows in the order of the index key, and the INSERT statement attempts to put new rows on the same page as the one containing adjacent rows, as defined by the key order.

See Using clustered indexes.
UNIQUE clause The UNIQUE attribute ensures that there will not be two rows in the table or materialized view with identical values in all the columns in the index. Each index key must be unique or contain a NULL in at least one column.
There is a difference between a unique constraint and a unique index. Columns of a unique index are allowed to be NULL, while columns in a unique constraint are not. A foreign key can reference either a primary key or a unique constraint, but not a unique index, because it can include multiple instances of NULL.

It is recommended that you do not use approximate data types such as FLOAT and DOUBLE for primary keys or for columns in unique constraints. Approximate numeric data types are subject to rounding errors after arithmetic operations.
ASC | DESC clause Columns are sorted in ascending (increasing) order unless descending (DESC) is explicitly specified. An index is used for both an ascending and a descending ORDER BY, whether the index was ascending or descending. However, if an ORDER BY is performed with mixed ascending and descending attributes, an index is used only if the index was created with the same ascending and descending attributes.
function-name The function-name clause creates an index on a function. This clause cannot be used on declared temporary tables or materialized views.
This form of the CREATE INDEX statement is a convenience method that carries out the following operations:
1. Adds a computed column named column-name to the table. The column is defined with a COMPUTE clause that is the specified function, along with any specified arguments. See the COMPUTE clause of the CREATE TABLE statement for restrictions on the type of function that can be specified. The data type of the column is based on the result type of the function.
2. Populates the computed column for the existing rows in the table.
3. Creates an index on the column.
  
  Dropping the index does not cause the associated computed column to be dropped.
  
  For more information about computed columns, see Working with computed columns.
IN | ON clause By default, the index is placed in the same database file as its table or materialized view. You can place the index in a separate database file by specifying a dbspace name in which to put the index. This feature is useful mainly for large databases to circumvent file size limitations, or for performance improvements that might be achieved by using multiple disk devices.
If the new index can share the physical index with an existing logical index, the IN clause is ignored.

For more information about limitations, see SQL Anywhere size and number limitations.
FOR OLAP WORKLOAD clause When you specify FOR OLAP WORKLOAD, the database server performs certain optimizations and gathers statistics on the key to help improve performance for OLAP workloads. Performance improvements are most noticeable when the optimization_workload is set to OLAP. See optimization_workload option [database].
For more information about OLAP, see OLAP support.

Remarks

Syntax 1 is for use with tables; Syntax 2 is for use with materialized views.

SQL Anywhere uses physical and logical indexes. A physical index is the actual indexing structure as it is stored on disk. A logical index is a reference to a physical index. If you create an index that is identical in its physical attributes to an existing index, the database server creates a logical index that shares the existing physical index. In general, indexes created by you are considered logical indexes. The database server creates physical indexes as required to implement logical indexes, and can share the same physical index among several logical indexes. See Index sharing using logical indexes.

The CREATE INDEX statement creates a sorted index on the specified columns of the named table or materialized view. Indexes are automatically used to improve the performance of queries issued to the database, and to sort queries with an ORDER BY clause. Once an index is created, it is never referenced in a SQL statement again except to validate it (VALIDATE INDEX), alter it (ALTER INDEX), delete it (DROP INDEX), or in a hint to the optimizer.

Index ownership There is no way of specifying the index owner in the CREATE INDEX statement. Indexes are always owned by the owner of the table or materialized view.
Indexes on views You can create indexes on materialized views, but not on regular views.
Index name space The name of each index must be unique for a given table or materialized view.
Exclusive use CREATE INDEX is prevented whenever the statement affects a table or materialized view currently being used by another connection. CREATE INDEX can be time consuming and the database server will not process requests referencing the same table while the statement is being processed.
Automatically created indexes SQL Anywhere automatically creates indexes for primary key, foreign key, and unique constraints. These automatically created indexes are held in the same database file as the table.

This statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots. See Snapshot isolation.

Permissions

Must be the owner of the table or materialized view, or have either DBA authority or REFERENCES permission.

Side effects

Automatic commit. Creating an index on a built-in function also causes a checkpoint.

Column statistics are updated (or created if they do not exist).

Standards and compatibility

SQL/2003 Vendor extension.

Example

Create a two-column index on the Employees table.

CREATE INDEX employee_name_index
ON Employees
( Surname, GivenName );

Create an index on the SalesOrderItems table for the ProductID column.

CREATE INDEX item_prod
ON SalesOrderItems
( ProductID );

Use the SORTKEY function to create an index on the Description column of the Products table, sorted according to a Russian collation. As a side effect, the statement adds a computed column desc_ru to the table.

CREATE INDEX ix_desc_ru
ON Products (
 SORTKEY( Description, 'rusdict' )
 AS desc_ru );

Discuss this page in DocCommentXchange.
Send feedback about this page using email.