FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 TABLEVIEW 

An index is a database object used for faster data retrieval from a table or for speeding up the sorting in a query.Indexes are also used to enforce the referential integrity constraints PRIMARY KEY, FOREIGN KEY and UNIQUE.

This section describes how to create indexes, activate and deactivate them, drop them and collect statistics (recalculate selectivity) for them.

CREATE INDEX

Creates an index

Available in

DSQL, ESQL

Syntax
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
  INDEX indexname ON tablename
  {(col [, col ...]) | COMPUTED BY (<expression>)}
  [WHERE <search_condition>]
Table 1. CREATE INDEX Statement Parameters
Parameter Description

indexname

Index name.The maximum length is 63 characters

tablename

The name of the table for which the index is to be built

col

Name of a column in the table.Columns of the types BLOB and ARRAY and computed fields cannot be used in an index.

expression

The expression that will compute the values for a computed index, also known as an “expression index”

search_condition

Conditional expression of a partial index, to filter the rows to include in the index.

The CREATE INDEX statement creates an index for a table that can be used to speed up searching, sorting and grouping.Indexes are created automatically in the process of defining constraints, such as primary key, foreign key or unique constraints.

An index can be built on the content of columns of any data type except for BLOB and arrays.The name (identifier) of an index must be unique among all index names.

Key Indexes

When a primary key, foreign key or unique constraint is added to a table or column, an index with the same name is created automatically, without an explicit directive from the designer.For example, the PK_COUNTRY index will be created automatically when you execute and commit the following statement:

ALTER TABLE COUNTRY ADD CONSTRAINT PK_COUNTRY
  PRIMARY KEY (ID);

Who Can Create an Index?

The CREATE INDEX statement can be executed by:

  • Administrators

  • The owner of the table

  • Users with the ALTER ANY TABLE privilege

Unique Indexes

Specifying the keyword UNIQUE in the index creation statement creates an index in which uniqueness will be enforced throughout the table.The index is referred to as a “unique index”.A unique index is not a constraint.

Unique indexes cannot contain duplicate key values (or duplicate key value combinations, in the case of compound, or multi-column, or multi-segment) indexes.Duplicated NULLs are permitted, in accordance with the SQL standard, in both single-segment and multi-segment indexes.