Using CREATE INDEX - Firebird

The CREATE INDEX statement creates an index on one or more columns of a table. A single -column index searches only one column in response to a query, whereas a multi-column index searches one or more columns.

This is the syntax:

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index-name ON table-name (col [, col ...]);

Mandatory Elements

The mandatory elements of the CREATE INDEX syntax are as follows:

  • CREATE INDEX index-name names the index. The identifier must be distinct from all other object identifiers in the database, apart from constraint and column identifiers. It is a good idea to use a systematic naming scheme, however, as an aid to self-documentation.
  • table-name is the name of the table to which the index applies.
  • col [, col...] is column name or a comma-separated list naming the columns that are to be the index keys. Column order is significant in indexes. For more information, see the upcoming section titled “Multi-column Indexes.”

Example

The following declaration creates a non-unique, ascending index on a personal name column in the PERSON table. It may aid search conditions like

WHERE LAST_NAME = 'Johnston' or WHERE LAST_NAME STARTING WITH 'Johns':

CREATE INDEX LAST_NAME_X ON PERSON(LAST_NAME);

Optional Elements

UNIQUE

The UNIQUE keyword can be used on indexes for which you want to disallow duplicate entries. The column or group is checked for duplicate values when the index is created and for existing values each time a row is inserted or updated.

Unique indexes make sense only when you need to enforce uniqueness as an intrinsic characteristic of the data item or group. For example, you would not define a unique index on a column storing a person’s name, because personal names are not intrinsically unique. Conversely, a unique index is a good idea on a column containing a Social Security number, since a unique key violation on it would alert the user to an error that needed attention.

Example

In this example, a unique index is created on three columns of an inventory table to ensure that the system stores at most one row for each size and color of an item:

CREATE UNIQUE INDEX STK_SIZE_COLOR_UQX ON STOCK_ITEM (PRODUCT_ID, SIZE, COLOR);

Note that a unique index is not a key. If you require a unique key for referential purposes, apply a UNIQUE constraint to the column(s) instead.

Finding Duplicates

Of course, it will not be possible to create a unique index on a column that already contains duplicate values. Before defining a unique index, use a SELECT statement to find duplicate items in the table. For example, before putting a unique index on PRODUCT_NAME in this PRODUCT table, this check would reveal any duplicates in the column:

SELECT PRODUCT_ID, UPPER(PRODUCT_NAME) FROM PRODUCT GROUP BY PRODUCT_ID, UPPER(PRODUCT_NAME) HAVING COUNT(*) > 1;

How you deal with duplicates depends on what they signify, according to your business rules, and the number of duplicates needing to be eliminated. Usually, a stored procedure will be the most efficient way to handle it.

ASC[ENDING] or DESC[ENDING]

The keywords ASC[ENDING] and DESC[ENDING] determine the vertical sort order of the index. ASC specifies an index that sorts the values from lowest to highest. It is the default and can be omitted. DESC sorts the values from highest to lowest and must be specified if a descending index is wanted. A descending index may prove useful for queries that are likely to search for high values (oldest age, most recent, biggest, etc.) and for any ordered searches or outputs that will specify a descending sort order.

Example

The following definition creates a descending index on a table in the employee database:

CREATE DESCENDING INDEX DESC_X ON SALARY_HISTORY (CHANGE_DATE);

The optimizer will use this index in a query such as the following, which returns the employee numbers and salaries of the ten employees who most recently had a raise:

SELECT FIRST 10 EMP_NO, NEW_SALARY FROM SALARY_HISTORY ORDER BY CHANGE_DATE DESCENDING;

If you intend to use both ascending and descending sort orders on a particular column, define both an ascending and a descending index for the same column. For example, it will be fine to create the following index in addition to the one in the previous example:

CREATE ASCENDING INDEX ASCEND_X ON SALARY_HISTORY (CHANGE_DATE);

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics