O'Reilly logo

SQL in a Nutshell, 2nd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

CREATE/ALTER INDEX Statement

Synopsis

Indexes are special objects built on top of tables that speed many data-manipulation operations, such as SELECT, UPDATE, and DELETE statements. The selectivity of a given WHERE clause and the available query plans the database query optimizer can choose from is usually based upon the quality of indexes that have been placed on the table in a given database.

The CREATE INDEX command is not a part of the ANSI SQL standard, and thus varies greatly among vendors.

Platform

Command

DB2

Supported, with variations

MySQL

Supported, with variations

Oracle

Supported, with variations

PostgreSQL

Supported, with variations

SQL Server

Supported, with variations

Common Vendor Syntax

CREATE [UNIQUE] INDEX index_name ON table_name
(column_name [, ...])

Keywords

CREATE [UNIQUE] INDEX index_name

Creates a new index named index_name in the current database and schema context. Since indexes are associated with specific tables (or sometimes views), the index_name need only be unique to the table it is dependent on. The UNIQUE keyword defines the index as a unique constraint for the table and disallows any duplicate values into the indexed column or columns of the table. (Refer to Section 2.4.)

ON table_name

Declares the pre-existing table that the index is associated with. The index is dependent upon the table. If the table is dropped, so is the index.

(column_name [, ...])

Defines one or more columns in the table that are indexed. The pointers derived from ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required