ASA SQL Reference
  SQL Statements

CREATE INDEX statement

Description 

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

Syntax 

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 ]

Parameters 

VIRTUAL keyword    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 query 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.

For more information, see Starting the Index Consultant, and Index Consultant overview.

CLUSTERED keyword    The CLUSTERED attribute causes table rows to be stored in an approximate key order corresponding to the index. While the server makes an attempt to preserve key order, total clustering is not guaranteed.

If a clustered index exists, the LOAD TABLE statement inserts rows into the table in the order of the index key, and the INSERT statement attempts to put new rows on the same table page as the one containing adjacent rows, as defined by the key order.

For more information, see Using clustered indexes.

UNIQUE keyword    The UNIQUE attribute ensures that there will not be two rows in the table 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 on a table 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 column with a unique constraint, but not a unique index, because it can include multiple instances of NULL.

ASC | DESC option    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.

function-name parameter    The function-name parameter creates an index on a built-in function. 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 built-in function, along with any specified arguments. 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 column to be dropped. This form of CREATE INDEX cannot be used on declared temporary tables.

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

For more information on limitations, see Size and number limitations.

Usage 

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 in a SQL statement again except to validate it (VALIDATE INDEX) or delete it (DROP INDEX).

You cannot create indexes on views.

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

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

Permissions 

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

The table must be a base table or a global temporary table.

Side effects 

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

See also 

DROP statement

Indexes

Types of index

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    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 Anywhere 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.

  Unique indexes in Adaptive Server Anywhere permit entries that contain NULL, and are otherwise identical. Unique indexes in Adaptive Server Enterprise do not permit entries that contain NULL and are otherwise identical.

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

Example 

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

CREATE INDEX item_prod
ON sales_order_items
( prod_id )

Use the SORTKEY function to create an index on the description column of the product 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 product (
 SORTKEY( description, 'rusdict' )
 AS desc_ru )