The ALTER INDEX statement alters the definition of an index.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared only if DYNAMICRULES run behavior is in effect for the package
(SQLSTATE 42509).
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- ALTERIN privilege on the schema of the index
- ALTER privilege on the table on which the index is defined
- CONTROL privilege on the index
- DBADM authority
Syntax
>>-ALTER INDEX--index-name --COMPRESS--+-NO--+-----------------><
'-YES-'
Description
- INDEX index-name
- Identifies the index to be altered. The name must identify an
index that exists at the current server (SQLSTATE 42704).
- COMPRESS
- Specifies whether index compression
is to be enabled or disabled. The index must not be an MDC or ITC
block index, catalog index, XML path index, index specification, or
an index on a created temporary table or declared temporary table
(SQLSTATE 42995).
- NO
- Specifies that index compression is disabled. A compressed index
will remain compressed until the index is rebuilt via index reorganization
or recreation.
- YES
- Specifies that index compression is enabled. An uncompressed index
will remain uncompressed until the index is rebuilt via index reorganization
or recreation.
Example
Alter
index JOB_BY_DPT to be compressed index.
ALTER INDEX JOB_BY_DPT
COMPRESS YES