ALTER INDEX

The ALTER INDEX statement alters the characteristics of an existing index. It is also a CA IDMS extension of the SQL standard. You can change the structure and location of an index through the modification of the following attributes:

Key count
Displacement
Uniqueness
Area association

Authorization

To issue an ALTER INDEX statement, you must have the ALTER privilege on or own the table on which the index is defined.

Syntax

►►─ 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 ───────────┘

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.

Usage

System tables

You cannot alter an index defined on a table in the SYSTEM schema.

Changing the Number of Entries in an SR8

It is sometimes desirable to change the number of entries in an SR8 system record after an index has been loaded. The ALTER INDEX statement enables the maximum number of entries to be changed without affecting the existing index structure.

Note: For more information about index structure and design considerations, see the CA IDMS Database Administration Guide.

Example

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;