Reference Manual
   CHAPTER 9. SQL Statements     

CREATE INDEX statement

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 ]

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 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 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 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 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 useful mainly for large databases, to circumvent the limit, on operating systems other than Windows NT, 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 version 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     Adaptive Server 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.

Standards and compatibility 

• SQL/92     Vendor extension.

• Sybase     Adaptive Server Enterprise has a more complex CREATE INDEX statement than Adaptive Server Anywhere. While the Adaptive Server Enterprise syntax is permitted in Adaptive Server Anywhere, some clauses and keywords are ignored.

The full syntax for Adaptive Server Enterprise 11.5 is as follows:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
... INDEX index-name
... ON [ [ database.]owner.]table_name
(column_name [, column_name]...)
... [ WITH {
... { FILLFACTOR | MAX_ROWS_PER_PAGE } = x,
CONSUMERS = x,
... IGNORE_DUP_KEY,
... SORTED_DATA,
[ IGNORE_DUP_ROW | ALLOW_DUP_ROW ]
} ]
... [ ON segment_name ]

Adaptive Server Enterprise indexes can be either clustered or nonclustered. A clustered index almost always retrieves data faster than a nonclustered index. Only one clustered index is permitted per table.

Adaptive Server Anywhere does not support clustered indexes. The CLUSTERED and NONCLUSTERED keywords are allowed by Adaptive Server Anywhere, but no action is taken.

Adaptive Server Anywhere also allows, by ignoring, the following keywords:

• FILLFACTOR

• IGNORE_DUP_KEY

• SORTED_DATA

• IGNORE_DUP_ROW

• ALLOW_DUP_ROW

Physical placement of an index is carried out differently in Adaptive Server Enterprise and Adaptive Server Anywhere. The ON segment-name clause is supported in Adaptive Server Anywhere, but segment-name refers to a dbspace.

Index names must be unique on a given table for both Adaptive Server Anywhere and Enterprise.

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 )