Documentation

SQL Reference Guide

Previous Topic

Next Topic

CREATE INDEX

Description

Creates an index on the specified table using the specified columns of the table. An index improves the performance of FairCom DB SQL operations whose predicates are based on the indexed column. However, an index slows performance of INSERT, DELETE and UPDATE operations.

Syntax

CREATE [ UNIQUE ] INDEX [ IF NOT EXISTS ] index_name

ON table_name

( {column_name [ASC | DESC]} [, ... ])

[ STORAGE_ATTRIBUTES 'attributes' ];

Arguments

UNIQUE

A UNIQUE index will not allow the table to contain any rows with duplicate column values for the set of columns specified for that index.

IF NOT EXISTS

This argument avoids failure by creating the index only if an index of the same name does not already exist.

index_name

The name of the index has to be unique for the specified table.

table_name

The name of the table on which the index is being built.

column_name [ , ... ]

The columns on which searches and retrievals will be ordered. These columns are called the index key. When more than one column is specified in the CREATE INDEX statement a concatenated index is created.

Note: Fields of type LVARBINARY and LVARCHAR cannot be included in a SQL index due to their size.

ASC | DESC

The index can be ordered as either ascending (ASC) or descending (DESC) on each column of the concatenated index. The default is ASC.

STORAGE_ATTRIBUTES 'attributes'

A quoted string that specifies index attributes that are specific to FairCom DB SQL indexes. FairCom DB SQL interprets the following attribute strings:

  • ''PARTITION=<rule>' - Creates the index as the partition index to the table enabling multiple table partitions. This option can be specified after a table has been created, and the table will be rebuilt according to the partition rule in effect.

    <rule> is a FairCom DB conditional expression evaluating to a numeric value.
  • ''PARTITION' - Creates the index as the partition index to the table enabling multiple table partitions. This option can be specified after a table has been created, and the table will be rebuilt according to the partition rule in effect. (This type of partitioned file currently require a server with your partition rules included at compile time. See the FairCom DB Programmer's Reference Guide for details.)

To combine STORAGE_ATTRIBUTE options, separate them with a semicolon (;).

Example

CREATE UNIQUE INDEX postdate_idx ON customer (invoice_date) STORAGE_ATTRIBUTES 'partition' ;

Authorization

The user executing this statement must have any of the following privileges:

  • DBA privilege.
  • Ownership of the index.
  • INDEX privilege on the table.
  • EXCLUSIVE file access is required for any this operation.

    SQL Compliance

    ODBC Core SQL grammar. Extensions: STORAGE_ATTRIBUTES,

    Environment

    Embedded SQL, interactive SQL, ODBC applications

    Related Statements

    CREATE TABLE, DROP INDEX, ALTER TABLE

TOCIndex