ALTER INDEX
ALTER INDEX#
Syntax#
alter_index ::=#
directkey_mod_clause ::=, rebuild_clause ::=, alter_index_properties ::=
directkey_mod_clause ::=#
rebuild_clause ::=#
index_attribute ::=#
alter_index_properties ::=#
alter_index_segment_attribute_clause ::=, allocate_extent_clause ::=
alter_index_segment_attribute_clause ::=#
storage_clause ::=#
allocate_extent_clause ::=#
Prerequisites#
The SYS user, the owner of the schema containing the index, and users having the ALTER ANY INDEX system privilege can execute the ALTER INDEX statement.
Description#
The ALTER INDEX statement is used to change or rebuild an existing index.
user_name#
This specifies the name of the owner of the index to be altered.
If omitted, Altibase will assume that the index belongs to the schema of the user connected via the current session.
index_name#
This specifies the name of the index to be altered.
directkey_mod_clause#
This specifies whether to use a direct key index or not. For more detailed information about direct key indexes, please refer to CREATE INDEX.
MAXSIZE integer#
This sets the maximum size of a direct index.
OFF#
This changes a direct key index to a normal index.
rebuild_clause#
This rebuilds an existing index or one of its partitions.
index_attribute#
This specifies the tablespace in which the rebuilt index partition will be stored.
RENAME#
This specifies the name of index to be changed.
AGING#
This records a transaction commit SCN in an index page and deletes old versions of nodes. This statement is only available for disk-based indexes.
REORGANIZATION#
The ALTER INDEX clause reorganizes the index space through integration of leaf nodes in memory B-tree index and neighboring nodes.
The REORGANIZATION clause ensures high efficiency of index space especially when it is used in a case the index range is relatively greater than that of the data, or there is an occurrence of index fragmentation on particular indexes. The memory based B-tree indexes are available for use.
alter_index_segment_attribute_clause#
INITRANS Clause#
This changes the initial number of Touched Transaction Slots (TTS).
MAXTRANS Clause#
This changes the maximum number of Touched Transaction Slots (TTS).
storage_clause#
Sets parameters for managing extents in segments.
INITEXTENTS Clause#
This ignores the INITEXTENTS parameter in the ALTER INDEX statement.
NEXTEXTENTS Clause#
This determines the number of extents that are added to the segment every time the segment is increased in size.
MINEXTENTS Clause#
This Sets the minimum number of extents in a segment.
MAXEXTENTS Clause#
This sets the maximum number of extents in a segment.
allocate_extent_clause#
This explicitly allocates extents to the index segment. Set SIZE to the total size of extents that are to be added to the index segment. If the disk tablespace consists of several data files, extents are distributed equally between them,
Example 1: Changing the Direct Key Index#
<Query> Rebuild the index partition IDX_P5 in the tablespace TBS1
iSQL> ALTER INDEX idx1 DIRECTKEY;
<Query> Change the direct key index idx1 to a normal index.
iSQL> ALTER INDEX idx1 DIRECTKEY OFF;
<Query> Change the index idx3 to a direct key index and set the maximum
iSQL> ALTER INDEX idx3 DIRECTKEY MAXSIZE 10;
Example 2: Rebuilding the Index Partition#
<Query> Rebuild the index partition idx_p5 in tablespace tbs1.
iSQL> ALTER INDEX IDX1 REBUILD PARTITION idx_p5 TABLESPACE tbs1;
Example 3: Changing the Index Name#
<Query> Change the name of emp_idx1 to emp_idx2.
iSQL> ALTER INDEX emp_idx1 RENAME TO emp_idx2;
Example 4: Allocating Extents to Indexes#
<Query> Allocate extents totaling 10MB in size to the index LOCAL_IDX, which is located in a disk tablespace.
iSQL> ALTER INDEX felt_idx ALLOCATE EXTENT ( SIZE 10M );
Example 5: Reorganization Indexes#
<Query> Execute reorganization of index idx1.
iSQL> ALTER INDEX idx1 REORGANIZATION;