Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Tables, views, and indexes » Indexes

 

Creating an index

Create indexes on base tables, temporary tables, and materialized views. You can also create indexes on a built-in function using a computed column.

Prerequisites

To create an index on a table, you must be the owner of the table or have one of the following privileges:

  • CREATE ANY INDEX system privilege
  • CREATE ANY OBJECT system privilege
  • REFERENCES privilege on the table and either the COMMENT ANY OBJECT system privilege, the ALTER ANY INDEX system privilege, or the ALTER ANY OBJECT system privilege

To create an index on a materialized view, you must be the owner of the materialized view or have one of the following privileges:

  • CREATE ANY INDEX system privilege
  • CREATE ANY OBJECT system privilege

You cannot create an index on a regular view. You cannot create an index on a materialized view that is disabled.

Context and remarks

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 is not allowed. You can use the Index Consultant to guide you in a proper selection of indexes for your database.

There is an automatic commit when creating an index on a local temporary table if the auto_commit_on_create_local_temp_index option is set to On. This option is set to Off by default.

Creating an index on a function (an implicit computed column) causes a checkpoint.

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

 Task
  1. In Sybase Central, use the SQL Anywhere 16 plug-in to connect to the database.

  2. In the left pane, right-click Indexes and click New » Index.

  3. Follow the instructions in the Create Index Wizard.

Results

The new index appears on the Index tab for the table and in Indexes. The new index is available to be used by queries.

 See also