This topic contains the following 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.

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;