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 ...]);
The mandatory elements of the CREATE INDEX syntax are as follows:
The following declaration creates a non-unique, ascending index on a personal name column in the PERSON table. It may aid search conditions likeWHERE LAST_NAME = 'Johnston' or WHERE LAST_NAME STARTING WITH 'Johns':
CREATE INDEX LAST_NAME_X ON PERSON(LAST_NAME);
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.
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.
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.
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);
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.