ALTER INDEX

Purpose

Use the ALTER INDEX statement to change or rebuild an existing index.

See Also: CREATE INDEX for information on creating an 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.

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::=

index_partition_description::=

modify_subpartition_clause::=

Keywords and Parameters

schema

Specify the schema containing the index. If you omit schema, Oracle assumes the index is in your own schema.

index

Specify 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.

  See Also: Oracle8i Data Cartridge Developer's Guide for information on the LOADING and FAILED states of domain indexes

deallocate_unused_clause

The deallocate_unused_clause lets you explicitly deallocate 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 integer

  The KEEP clause lets you specify 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

The allocate_extent_clause lets you explicitly allocate 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 integer	Specify 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 'filename'	Specify one of the datafiles in the index's tablespace to contain the new extent. If you omit DATAFILE, Oracle chooses the datafile.
INSTANCE integer	Use the INSTANCE clause to make 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

Use the PARALLEL clause to change the default degree of parallelism for queries and DML on the index.

Restriction: You cannot specify this clause for an index on a temporary table.

Note: The syntax of the parallel_clause supersedes syntax appearing in earlier releases of Oracle. Superseded syntax is still supported for backward compatibility, but may result in slightly different behavior.

NOPARALLEL	Specify NOPARALLEL for serial execution. This is the default.
PARALLEL	Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
PARALLEL integer	Specification of integer indicates 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 servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.

physical_attributes_clause

Use the physical_attributes_clause to 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 Also: the physical attributes 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	Use the storage_clause to change 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 Also:
  LOGGING| NOLOGGING	Use LOGGING or NOLOGGING to specify whether 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

Use the rebuild_clause to re-create 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	Use the PARTITION clause to rebuild 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	Use the SUBPARTITION clause to rebuild 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	Indicate 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 tablespace	Specify 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	Specify COMPRESS to enable 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 bitmap index.
  NOCOMPRESS	Specify NOCOMPRESS to disable key compression. This is the default.
  ONLINE	Specify ONLINE to allow DML operations on the table or partition during rebuilding of the index. Restrictions: You cannot specify ONLINE when rebuilding the secondary index of an index-organized table. 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	Specify COMPUTE STATISTICS if you want 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 Also: Oracle8i Supplied PL/SQL Packages Reference
  LOGGING | NOLOGGING	Specify whether the ALTER INDEX ... REBUILD operation will be logged.

PARAMETERS

The PARAMETERS clause 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.

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.

  See Also: Oracle8i Data Cartridge Developer's Guide for more information on indextype routines CREATE INDEX for more information on domain indexes

ENABLE

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 these conditions are true:

• The function is currently valid

• The signature of the current function matches the signature of the function when the index was created

• The function is currently marked as DETERMINISTIC

Restriction: You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE.

DISABLE

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

Specify UNUSABLE to mark 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

Use the RENAME clause to rename index to new_index_name. The new_index_name is a single identifier and does not include the schema name.

COALESCE

Specify COALESCE to instruct 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 partitioning 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

Specify 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	Specify the default tablespace for new partitions of an index or subpartitions of an index partition.
LOGGING | NOLOGGING	Specify the default logging attribute of a partitioned index or an index partition.
FOR PARTITION partition	Use the FOR PARTITION clause to specify the default attributes for the subpartitions of a partition of a local index on a composite-partitioned table.

modify_partition_clause

Use the modify_partition_clause to modify 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

Use the rename_partition or rename_subpartition to rename index partition or subpartition to new_name.

drop_partition_clause

Use the drop_partition_clause to remove 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

Use the split_partition_clause to split 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)	Specify 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 index_partition_description	Specify (optionally) the name and physical attributes of each of the two partitions resulting from the split.

modify_subpartition_clause

Use the modify_subpartition_clause to 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 Example

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 Example

The following statement drops index partition ix_antarctica:

ALTER INDEX sales_area_ix
  DROP PARTITION ix_antarctica;

Modifying Default Attributes Example

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 Example

The following statement marks the odx_acctno index as UNUSABLE:

ALTER INDEX idx_acctno UNUSABLE;

Marking a Partition Unusable Example

The following statement marks partition idx_feb96 of index idx_acctno as UNUSABLE:

ALTER INDEX idx_acctno MODIFY PARTITION idx_feb96 UNUSABLE;

Changing MAXEXTENTS Example

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 Example

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 Example

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 Example

The following statement renames an index:

ALTER INDEX emp_ix1 RENAME TO employee_ix1;

Renaming an Index Partition Example

The following statement renames an index partition:

ALTER INDEX employee_ix1 RENAME PARTITION emp_ix1_p3 
  TO employee_ix1_p3;

Splitting a Partition Example

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 Example

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 Example

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 execution processes to scan the old and to build the new index:

ALTER INDEX emp_idx
   REBUILD
   PARALLEL;