Oracle8i SQL Reference
Release 3 (8.1.7)

Part Number A85397-01

Library

Product

Contents

Index

Go to previous page Go to beginning of chapter Go to next page

SQL Statements:
ALTER CLUSTER to ALTER SYSTEM, 6 of 19


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:

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:

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

See Also: "Notes on the parallel_clause" for CREATE TABLE  

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:

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:

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:

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:

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:

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;

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index