DB2 Create Index

 

An index is an ordered set of pointers to rows of a table. DB2 can use indexes to improve performance and ensure uniqueness.

In most cases, access to data is faster with an index than with a scan of the table.

An index is stored separately from the data in the table. Each index is physically stored in its own index space. When you define an index by using CREATE INDEX statement, DB2 builds this structure and maintains it automatically. However you can perform. necessary maintenance such as reorganizing it or recovering the index.

Be aware that indexes have both benefits and disadvantages. A greater number of indexes can simultaneously improve the access performance of a particular transaction and require additional processing for inserting, updating, and deleting index keys.

CREATE INDEX STATEMENTS:

create  [UNIQUE [ WHERE NOT NULL] ] index index_name on table_name [USING acc_name] (column1,column2, ... ... , column.N)

UNIQUE: Prevents the table from containing two or more rows with the same value of the index key. When UNIQUE is used, all null values for a column are considered equal.For example, if the key is a single column that can contain null values, that column can contain only one null value. The constraint is enforced when rows of the table are updated or new rows are inserted.

The constraint is also checked during the execution of the CREATE INDEX statement. If the table already contains rows with duplicate key values, the index is not created.

UNIQUE WHERE NOT NULL: Prevents the table from containing two or more rows with the same value of the index key where all null values for a colum are not considered equal. Multiple null values are allowed. Otherwise, this is identical to UNIQUE.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24123206/viewspace-665746/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24123206/viewspace-665746/