User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To create an index on a specified table. Indexes are used to improve database performance.

Syntax

     CREATE [ UNIQUE ] INDEX index-name
          ... ON [ owner.]table-name
          ... ( column-name [ ASC | DESC ], ... )
          ... [ { IN|ON} dbspace-name ]

Usage

Anywhere.

Permissions

Must be the owner of the table or have DBA authority.

Side effects

Automatic commit.

See also

• DROP statement

Description

The CREATE INDEX statement creates a sorted index on the specified columns of the named table. Indexes are automatically used by SQL Anywhere to improve the performance of queries issued to the database as well as for sorting queries where an ORDER BY clause is specified. Once an index is created, it is never referenced again except to delete it using the DROP INDEX statement.

UNIQUE constraint The UNIQUE constraint ensures that there will not be two rows in the table with identical values in all the columns in the index.

Ascending or descending sorting Columns are sorted in ascending (increasing) order unless descending (DESC) is explicitly specified. An index will be used by SQL Anywhere for both an ascending and a descending ORDER BY, no matter whether the index was ascending or descending. However, if an ORDER BY is performed with mixed ascending and descending attributes, an index will be used only if the index was created with the same ascending and descending attributes.

Index placement By default, the index is placed in the same database file as its table. You can place the index in a separate database file by specifying a dbspace name in which to put the index. This feature is of use mainly for large databases, to circumvent the limit of 2 GB per table.

Notes

• Index ownership There is no way of specifying the index owner in the CREATE INDEX statement. Indexes are automatically owned by the owner of the table on which they are defined. The index name must be unique for each owner.
• No indexes on views Indexes cannot be created for views.
• Index name space The name of each index must be unique for a given table. For databases created previous to release 5.5.01, the condition was more restrictive: that each index name must be unique for a given user ID.
• Exclusive table use CREATE INDEX is prevented whenever the statement affects a table currently being used by another connection. CREATE INDEX can be time consuming and the 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 keys and for unique constraints. These automatically-created indexes are held in the same database file as the table.

Examples

• Create a two-column index on the employee table.

       CREATE INDEX employee_name_index
       ON employee
       ( emp_lname, emp_fname )
  

• Create an index on the sales_order_items table for the product ID column.

       CREATE INDEX item_prod
       ON sales_order_items
       ( prod_id )