SQL › Alter Index Extensions › ALTER INDEX Statement

ALTER INDEX Statement

The ALTER INDEX was enhanced to support additional types of changes to an index.

For more information on the full ALTER INDEX statements, see the SQL Reference Guide.

ALTER INDEX Syntax

The following diagram shows the syntax for the new parameters for the ALTER INDEX statement:

►►─ ALTER INDEX index-name ON ┬────────────────┬ table-identifier ────►
                              └─ schema-name. ─┘      

   ┌───────────────────────────────────────────┐
 ►─▼─┬─ INDEX BLOCK CONTAINS key-count KEYS ─┬─┴──────────────────────►◄
     ├─ DISPLACEMENT IS page-count PAGES ────┤
     ├─┬───────┬─ UNIQUE ────────────────────┤
     │ └─ NOT ─┘                             │
     └─ IN segment-name.area-name ───────────┘

ALTER INDEX Parameters

This section describes the parameters for the ALTER INDEX statement:

page-count PAGES

Specifies how far away from the index owner the bottom-level index records are stored.

If the value of page-count is zero (0), the bottom-level internal index records are not displaced from the index owner.

Limit: An unsigned integer from 0–32,767.

UNIQUE

Specifies that the index-key value in any given row of the table on which the index is defined must be different from the index-key value in all other rows of the table. The table cannot contain any duplicate index-key values.

If you specify UNIQUE and the table contains duplicate index-key values, the alter statement will fail.

NOT UNIQUE

Removes the restriction that all values of the index-key within the table must be unique.

When the UNIQUE restriction is used to ensure uniqueness of a referenced key in some constraint, you cannot remove it from an index unless another index or CALC key can be used in its place.

IN

Requests a change in the location of the named index.

area-name

Identifies a new area with which the index is to be associated. Area-name must identify an area defined in the dictionary.

segment-name

Identifies the segment associated with the area.

Example: Alter the EMP_LNAME Index

In this example, the EMP_LNAME index is moved from its current location to the DEMO.EMPAREA area. Each internal index record will have a maximum of 30 keys and the bottom-level index records will be displaced 40 pages from the top of the index.

alter index emp_lname (last_name) on emp.benefits
      displacement is 40 pages
           index block contains 30 keys
      in area demo.emparea;