ALTER INDEX

Syntax

deallocate_unused_clause::=

allocate_extent_clause::=

parallel_clause::=

physical_attributes_clause::=

storage_clause: See "storage_clause".

rebuild_clause::=

compression_clause::=

partitioning_clauses::=

modify_default_attributes_clause::=

modify_partition_clause::=

rename_partition/ subpartition_clause::=

drop_partition_clause::=

split_partition_clause::=

partition_description::=

modify_subpartition_clause::=

Purpose

To change or rebuild an existing index.

For information on creating an index, see "CREATE INDEX".

Prerequisites

The index must be in your own schema or you must have ALTER ANY INDEX system privilege.

Schema object privileges are granted on the parent index, not on individual index partitions or subpartitions.

You must have tablespace quota to modify, rebuild, or split an index partition or to modify or rebuild an index subpartition.

Keywords and Parameters

schema	is the schema containing the index. If you omit schema, Oracle assumes the index is in your own schema.
index	is the name of the index to be altered.
	Restrictions: If index is a domain index, you can specify only the PARAMETERS clause, the RENAME clause, or the rebuild_clause (with or without the PARAMETERS clause). No other clauses are valid. You cannot alter or rename a domain index that is marked LOADING or FAILED. If an index is marked FAILED, the only clause you can specify is REBUILD. For information on the LOADING and FAILED states of domain indexes, see Oracle8i Data Cartridge Developer's Guide.
deallocate_unused_clause	explicitly deallocates unused space at the end of the index and makes the freed space available for other segments in the tablespace. Only unused space above the high water mark can be freed. If index is range-partitioned or hash-partitioned, Oracle deallocates unused space from each index partition. If index is a local index on a composite-partitioned table, Oracle deallocates unused space from each index subpartition. Restrictions: You cannot specify this clause for an index on a temporary table. You cannot specify this clause and also specify the rebuild_clause. See Also: "ALTER TABLE" for more information on this clause.
	KEEP	specifies the number of bytes above the high water mark that the index will have after deallocation. If the number of remaining extents are less than MINEXTENTS, then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is set to the value of the current initial extent. If you omit KEEP, all unused space is freed.
	See Also: "ALTER TABLE" for a complete description of this clause.
allocate_extent_clause	explicitly allocates a new extent for the index. For a local index on a hash-partitioned table, Oracle allocates a new extent for each partition of the index. Restriction: You cannot specify this clause for an index on a temporary table or for a range-partitioned or composite-partitioned index.
	SIZE	specifies the size of the extent in bytes. Use K or M to specify the extent size in kilobytes or megabytes. If you omit SIZE, Oracle determines the size based on the values of the index's storage parameters.
	DATAFILE	specifies one of the datafiles in the index's tablespace to contain the new extent. If you omit DATAFILE, Oracle chooses the datafile.
	INSTANCE	makes the new extent available to the specified instance. An instance is identified by the value of its initialization parameter INSTANCE_NUMBER. If you omit this parameter, the extent is available to all instances. Use this parameter only if you are using Oracle with the Parallel Server option in parallel mode.
	Explicitly allocating an extent with this clause does not change the values of the NEXT and PCTINCREASE storage parameters, so does not affect the size of the next extent to be allocated.
parallel_clause	changes the default degree of parallelism for queries and DML on the index. For additional information, see the Notes to the parallel_clause of "CREATE TABLE".
	NOPARALLEL	specifies serial execution. This is the default.
	PARALLEL	causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances multiplied by the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
	PARALLEL integer	specifies the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution processes. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.
	Restriction: You cannot specify this clause for an index on a temporary table.
physical_attributes_clause	lets you change the values of parameters for a nonpartitioned index, all partitions and subpartitions of a partitioned index, a specified partition, or all subpartitions of a specified partition. See these parameters in "CREATE TABLE". Restrictions: You cannot specify this clause for an index on a temporary table. You cannot specify the PCTUSED parameter when altering an index. You cannot change the value of the PCTFREE parameter for the index as a whole (ALTER INDEX) or for a partition (ALTER INDEX ... MODIFY PARTITION). You can specify PCTFREE in all other forms of the ALTER INDEX statement.
	storage_clause	changes the storage parameters for a nonpartitioned index, index partition, or all partitions of a partitioned index, or default values of these parameters for a partitioned index. See the "storage_clause".
LOGGING| NOLOGGING	LOGGING|NOLOGGING specifies that subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against a nonpartitioned index, a range or hash index partition, or all partitions or subpartitions of a composite-partitioned index will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file.
	In NOLOGGING mode, data is modified with minimal logging (to mark new extents invalid and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not logged. Therefore, if you cannot afford to lose this index, you must take a backup after the operation in NOLOGGING mode.
	If the database is run in ARCHIVELOG mode, media recovery from a backup taken before an operation in LOGGING mode will re-create the index. However, media recovery from a backup taken before an operation in NOLOGGING mode will not re-create the index.
	An index segment can have logging attributes different from those of the base table and different from those of other index segments for the same base table. Restriction: You cannot specify this clause for an index on a temporary table.
	See Also: Oracle8i Concepts and the Oracle8i Parallel Server Concepts for more information about LOGGING and parallel DML.
RECOVERABLE| UNRECOVERABLE	These keywords are deprecated and have been replaced with LOGGING and NOLOGGING, respectively. Although RECOVERABLE and UNRECOVERABLE are supported for backward compatibility, Oracle Corporation strongly recommends that you use the LOGGING and NOLOGGING keywords.
	RECOVERABLE is not a valid keyword for creating partitioned tables or LOB storage characteristics. UNRECOVERABLE is not a valid keyword for creating partitioned or index-organized tables. Also, it can be specified only with the AS subquery clause of CREATE INDEX.
rebuild_clause	re-creates an existing index or one of its partitions or subpartitions. For a function-based index, this clause also enables the index. If the function on which the index is based does not exist, the rebuild statement will fail.
	Restrictions: You cannot rebuild an index on a temporary table. You cannot rebuild an entire partitioned index. You must rebuild each partition or subpartition, as described below. You cannot also specify the deallocate_unused_clause in this statement. You cannot change the value of the PCTFREE parameter for the index as a whole (ALTER INDEX) or for a partition (ALTER INDEX ... MODIFY PARTITION). You can specify PCTFREE in all other forms of the ALTER INDEX statement.
	PARTITION partition	rebuilds one partition of an index. You can also use this clause to move an index partition to another tablespace or to change a create-time physical attribute. Restriction: You cannot specify this clause for a local index on a composite-partitioned table. Instead, use the REBUILD SUBPARTITION clause. See Also: Oracle8i Administrator's Guide for more information about partition maintenance operations.
	SUBPARTITION subpartition	rebuilds one subpartition of an index. You can also use this clause to move an index subpartition to another tablespace. If you do not specify TABLESPACE, the subpartition is rebuilt in the same tablespace. Restrictions: The only parameters you can specify for a subpartition are TABLESPACE and the parallel_clause.
	REVERSE | NOREVERSE	specifies whether the bytes of the index block are stored in reverse order. REVERSE stores the bytes of the index block in reverse order and excludes the rowid when the index is rebuilt. NOREVERSE stores the bytes of the index block without reversing the order when the index is rebuilt. Rebuilding a REVERSE index without the NOREVERSE keyword produces a rebuilt, reverse-keyed index.
		Restrictions: You cannot reverse a bitmap index or an index-organized table. You cannot specify REVERSE or NOREVERSE for a partition or subpartition.
	TABLESPACE	specifies the tablespace where the rebuilt index, index partition, or index subpartition will be stored. The default is the default tablespace where the index or partition resided before you rebuilt it.
	COMPRESS	enables key compression, which eliminates repeated occurrence of key column values. Use integer to specify the prefix length (number of prefix columns to compress). For unique indexes, the range of valid prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1. For nonunique indexes, the range of valid prefix length values is from 1 to the number of key columns. The default prefix length is number of key columns. Oracle compresses only nonpartitioned indexes that are nonunique or unique indexes of at least two columns. Restriction: You cannot specify COMPRESS for a bitmapped index.
	NOCOMPRESS	disables key compression. This is the default.
	ONLINE	specifies that DML operations on the table or partition are allowed during rebuilding of the index. Restriction: Parallel DML is not supported during online index building. If you specify ONLINE and then issue parallel DML statements, Oracle returns an error.
	COMPUTE STATISTICS	enables you to collect statistics at relatively little cost during the rebuilding of an index. These statistics are stored in the data dictionary for ongoing use by the optimizer in choosing a plan of execution for SQL statements. The types of statistics collected depend on the type of index you are rebuilding.
		Note: If you create an index using another index (instead of a table), the original index might not provide adequate statistical information. Therefore, Oracle generally uses the base table to compute the statistics, which will improve the statistics but may negatively affect performance.
	.	Additional methods of collecting statistics are available in PL/SQL packages and procedures. See Oracle8i Supplied PL/SQL Packages Reference.
	LOGGING | NOLOGGING	specifies whether the ALTER INDEX ... REBUILD operation will be logged.
PARAMETERS	applies only to domain indexes. This clause specifies the parameter string for altering the index (or, in the rebuild_clause, rebuilding the index). The maximum length of the parameter string is 1000 characters. This string is passed uninterpreted to the appropriate indextype routine. See Also: Oracle8i Data Cartridge Developer's Guide for more information on these routines. "CREATE INDEX" for more information on domain indexes.
	Restrictions: You cannot specify this clause for any indexes other than domain indexes. The parameter string is passed to the appropriate routine only if index is not marked UNUSABLE.
ENABLE	applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if the function is currently valid, the signature of the current function matches the signature of the function when the index was created, and the function is currently marked as DETERMINISTIC. Restriction: You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE.
DISABLE	applies only to a function-based index. This clause enables you to disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.
UNUSABLE	marks the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it. Restriction: You cannot specify this clause for an index on a temporary table.
RENAME TO	renames index to new_index_name. The new_index_name is a single identifier and does not include the schema name.
COALESCE	instructs Oracle to merge the contents of index blocks where possible to free blocks for reuse. Restriction: You cannot specify this clause for an index on a temporary table. See Also: Oracle8i Administrator's Guide for more information on space management and coalescing indexes.
partitioning_clauses: The remainder of the clauses of the ALTER INDEX statement are valid only for partitioned indexes.
	Restrictions: You cannot specify any of these clauses for an index on a temporary table. You can combine several operations on the base index into one ALTER INDEX statement (except RENAME and REBUILD), but you cannot combine partition operations with other partition operations or with operations on the base index.
modify_default_attributes_clause	specifies new values for the default attributes of a partitioned index. Restriction: The only attribute you can specify for an index on a hash-partitioned or composite-partitioned table is TABLESPACE.
	TABLESPACE	specifies the default tablespace for new partitions of an index or subpartitions of an index partition.
	LOGGING | NOLOGGING	specifies the default logging attribute of a partitioned index or an index partition.
	FOR PARTITION partition	specifies the default attributes for the subpartitions of a partition of a local index on a composite-partitioned table.
modify_partition_clause	modifies the real physical attributes, logging attribute, or storage characteristics of index partition partition or its subpartitions. Restriction: You cannot specify the physical_attributes_clause for an index on a hash-partitioned table.
	Note: If the index is a local index on a composite-partitioned table, the changes you specify here will override any attributes specified earlier for the subpartitions of index, as well as establish default values of attributes for future subpartitions of that partition. To change the default attributes of the partition without overriding the attributes of subpartitions, use ALTER TABLE ... MODIFY DEFAULT ATTRIBUTES OF PARTITION.
rename_partition/ subpartition_clause	renames index partition or subpartition to new_name.
drop_partition_clause	removes a partition and the data in it from a partitioned global index. When you drop a partition of a global index, Oracle marks the index's next partition UNUSABLE. You cannot drop the highest partition of a global index.
split_partition_clause	splits a partition of a global partitioned index into two partitions, adding a new partition to the index. Splitting a partition marked UNUSABLE results in two partitions, both marked UNUSABLE. You must rebuild the partitions before you can use them.
	Splitting a usable partition results in two partitions populated with index data. Both new partitions are usable.
	AT (value_list)	specifies the new noninclusive upper bound for split_partition_1. The value_list must evaluate to less than the presplit partition bound for partition_name_old and greater than the partition bound for the next lowest partition (if there is one).
	INTO	describes the two partitions resulting from the split.
partition_description	specifies (optionally) the name and physical attributes of each of two partitions resulting from a split.
modify_subpartition_clause	lets you mark UNUSABLE or allocate or deallocate storage for a subpartition of a local index on a composite-partitioned table. All other attributes of such a subpartition are inherited from partition-level default attributes.

Examples

Modifying Real Attributes

This statement alters SCOTT's CUSTOMER index so that future data blocks within this index use 5 initial transaction entries and an incremental extent of 100 kilobytes:

ALTER INDEX scott.customer  
    INITRANS 5  
    STORAGE (NEXT 100K); 

If the SCOTT.CUSTOMER index is partitioned, this statement also alters the default attributes of future partitions of the index. New partitions added in the future will use 5 initial transaction entries and an incremental extent of 100K.

Dropping an Index Partition

The following statement drops index partition IX_ANTARTICA:

ALTER INDEX sales_area_ix
  DROP PARTITION ix_antarctica;

Modifying Default Attributes

This statement alters the default attributes of local partitioned index SALES_IX3. New partitions added in the future will use 5 initial transaction entries and an incremental extent of 100K:

ALTER INDEX sales_ix3 
  MODIFY DEFAULT ATTRIBUTES INITRANS 5 STORAGE ( NEXT 100K );

Marking an Index Unusable

The following statement marks the IDX_ACCTNO index as UNUSABLE:

ALTER INDEX idx_acctno UNUSABLE;

Marking a Partition Unusable

The following statement marks partition IDX_FEB96 of index IDX_ACCTNO as UNUSABLE:

ALTER INDEX idx_acctno MODIFY PARTITION idx_feb96 UNUSABLE;

Changing MAXEXTENTS

The following statement changes the maximum number of extents for partition BRIX_NY and changes the logging attribute:

ALTER INDEX branch_ix MODIFY PARTITION brix_ny 
  STORAGE( MAXEXTENTS 30 ) LOGGING;

Disabling Parallel Queries

The following statement sets the parallel attributes for index ARTIST_IX so that scans on the index will not be parallelized:

ALTER INDEX artist_ix NOPARALLEL;

Rebuilding a Partition

The following statement rebuilds partition P063 in index ARTIST_IX. The rebuilding of the index partition will not be logged:

ALTER INDEX artist_ix 
  REBUILD PARTITION p063 NOLOGGING;

Renaming an Index

The following statement renames an index:

ALTER INDEX emp_ix1 RENAME TO employee_ix1;

Renaming an Index Partition

The following statement renames an index partition:

ALTER INDEX employee_ix1 RENAME PARTITION emp_ix1_p3 
  TO employee_ix1_p3;

Splitting a Partition

The following statement splits partition PARTNUM_IX_P6 in partitioned index PARTNUM_IX into PARTNUM_IX_P5 and PARTNUM_IX_P6:

ALTER INDEX partnum_ix
  SPLIT PARTITION partnum_ix_p6 AT ( 5001 )
  INTO ( PARTITION partnum_ix_p5 TABLESPACE ts017 LOGGING, 
         PARTITION partnum_ix_p6 TABLESPACE ts004 );

The second partition retains the name of the old partition.

Storing Index Blocks in Reverse Order

The following statement rebuilds index EMP_IX so that the bytes of the index block are stored in REVERSE order:

ALTER INDEX emp_ix REBUILD REVERSE;

Collecting Index Statistics

The following statement collects statistics on the nonpartitioned EMP_INDX index:

ALTER INDEX emp_indx REBUILD COMPUTE STATISTICS;

The type of statistics collected depends on the type of index you are rebuilding.

See Also: Oracle8i Concepts.

PARALLEL Example

The following statement causes the index to be rebuilt from the existing index by using parallel parallel execution processes to scan the old and to build the new index:

ALTER INDEX emp_idx
   REBUILD
   PARALLEL;