DB2 SQL: How to Create Index (1 of 2)

An index is an object that contains an ordered set of pointers that refer to rows in a base table.

Each index is based on one or more columns in the base table to which it refers (known as keys); however, indexes are stored as separate entities. Read more on Index

Benefits of Using Index

  • Provide a fast, efficient method for locating specific rows of data in very large tables. (In some cases, all the information needed to resolve a query may be found in the index itself, in which case the actual table data does not have to be accessed.)
  • Provide a logical ordering of the rows of a table. (Data is stored in a table in no particular order; when indexes are used, the values of one or more columns can be sorted in ascending or descending order. This is very beneficial when processing queries that contain ORDER BY and GROUP BY clauses.)
  • Improve overall query performance. (If no index exists on a table, a table scan must be performed for each table referenced in a query. The larger the table, the longer a table scan takes because a table scan requires each table row to be accessed sequentially.)
  • Enforce the uniqueness of records stored in a table.
  • Can require a table to use clustering storage, which causes the rows of a table to be physically arranged according to the ordering of their index column values. (Although all indexes provide a logical ordering of data, only a clustering index provides a physical ordering of data.)
    Can provide greater concurrency in multi-user environments. (Because records can be located faster, acquired locks do not have to be held as long.)

Disadvantages of Index

  • Each index created requires additional storage or disk space. The exact amount of space needed is dependent on the size of the associated table, along with the size and number of columns contained in the index.

  • Every insert and update operation performed on a table requires additional updating of the indexes associated with that table. This is also true when data is bulk-loaded into a table using DB2’s LOAD utility.

  • Each index potentially adds an alternative access path that the DB2 Optimizer must consider when generating the optimum access plan to use to resolve a query. This in turn increases compilation time when static queries are embedded in an application program.

Syntax for creating index

ON [TableName] ([PriColumnName]  ,...)

IndexName:- Identifies the name that is to be assigned to the index to be created.
TableName:- Identifies the name assigned to the base table with which the index to be created is to be associated.
PriColumnName:- Identifies one or more primary columns that are to be part of the index’s key. (The combined values of each primary column specified will be used to enforce data uniqueness in the associated base table.)
SecColumnName:- Identifies one or more secondary columns whose values are to be stored with the values of the primary columns specified but are not to be used to enforce data uniqueness.
PercentFree:Specifies a percentage of each index page to leave as free space when building the index.

SQL Query to create index

CREATE INDEX empno_indx
 ON employee (empno DESC)

SQL Query to create UNIQUE Index

  1. A unique index is created by specifying the UNIQUE clause with the CREATE INDEX statement. When the CREATE INDEX statement is executed with the UNIQUE clause specified, rows in the table associated with the index being created must not have two or more occurrences of the same values in the set of columns that make up the index key.
  2. If the table for which the index is to be created contains data, uniqueness is checked when DB2 attempts to create the index specified—if records with duplicate values for the index key are found, the index will not be created; if no duplicates are found, the index is created, and uniqueness is enforced each time an insert or update operation is performed against the table. Any time the uniqueness of the index key is compromised, the insert or update operation will fail, and an error will be generated.
 ON employee (ssn)

SQL Query to create Multi-column UNIQUE Index

 ON customer (custid, salutation, lname)

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.