Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
ALTER CLUSTER to ALTER SEQUENCE, 6 of 18
Use the ALTER
INDEX
statement to change or rebuild an existing index.
The index must be in your own schema or you must have ALTER
ANY
INDEX
system privilege.
To execute the MONITORING
USAGE
clause, the index must be in your own schema.
To modify a domain index, you must have EXECUTE
object privilege on the indextype of the index.
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.
alter_index::=
alter_index
deallocate_unused_clause
allocate_extent_clause
parallel_clause
physical_attributes_clause
storage_clause: See storage_clause
.
rebuild_clause
compression_clauses::=
compression_clauses
alter_index_partitioning
modify_index_default_attrs
modify_index_partition
rename_index_partition
drop_index_partition
split_index_partition
index_partition_description::=
index_partition_description
modify_index_subpartition
Specify the schema containing the index. If you omit schema
, Oracle assumes the index is in your own schema.
Specify the name of the index to be altered.
Restrictions:
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.
LOADING
or FAILED
. If an index is marked FAILED
, the only clause you can specify is REBUILD
.
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:
rebuild_clause
.
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.
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.
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.
Specify one of the datafiles in the index's tablespace to contain the new extent. If you omit DATAFILE
, Oracle chooses the datafile.
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 Real Application Clusters.
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.
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.
Specify NOPARALLEL
for serial execution. This is the default.
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.
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
.
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.
Restrictions:
PCTUSED
parameter at all when altering an index.
PCTFREE
parameter only as part of the rebuild_clause
, the modify_index_default_attrs
clause, or the split_partition_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.
Use LOGGING
or NOLOGGING
to specify whether subsequent Direct Loader (SQL*Loader) and direct-path 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:
Oracle9i Database Concepts and the Oracle9i Data Warehousing Guide for more information about |
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
.
Use the rebuild_clause
to re-create an existing index or one of its partitions or subpartitions. If index is marked UNUSABLE
, a successful rebuild will mark it USABLE
. 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:
INVALID
. Instead, you must drop and then re-create it.
deallocate_unused_clause
in this statement.
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.
PARAMETERS
clause (either for the index or for a partition of the index). No other rebuild clauses are valid.
ALTER
INDEX
... REBUILD
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.
Note: The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle9i Database Administrator's Guide for a discussion of these restrictions. |
Restriction: You cannot specify this clause for a local index on a composite-partitioned table. Instead, use the REBUILD
SUBPARTITION
clause.
See Also:
Oracle9i Database Administrator's Guide for more information about partition maintenance operations |
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.
Note: The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle9i Database Administrator's Guide for a discussion of these restrictions. |
Restrictions:
TABLESPACE
and the parallel_clause
.
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:
REVERSE
or NOREVERSE
for a partition or subpartition.
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.
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).
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.
Specify NOCOMPRESS
to disable key compression. This is the default.
Specify ONLINE
to allow DML operations on the table or partition during rebuilding of the index.
Restrictions:
ONLINE
and then issue parallel DML statements, Oracle returns an error.
ONLINE
for a bitmap index or a cluster index.
ONLINE
when rebuilding an index that enforces a referential integrity constraint.
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.
Additional methods of collecting statistics are available in PL/SQL packages and procedures
Specify whether the ALTER
INDEX
... REBUILD
operation will be logged.
The PARAMETERS
clause applies only to domain indexes. This clause specifies the parameter string for altering or rebuilding a domain index or a partition of a domain index. If index is marked UNUSABLE
, modifying the parameters alone does not make it USABLE
. You must also rebuild the UNUSABLE
index to make it usable.
The maximum length of the parameter string is 1000 characters. This string is passed uninterpreted to the appropriate indextype routine.
Note: If you have installed Oracle Text, you can rebuild your Oracle Text domain indexes using parameters specific to that product. For more information on those parameters, please refer to Oracle Text Reference. |
Restrictions:
index
is not marked IN_PROGRESS
or FAILED
, no index partitions are marked IN_PROGRESS
, and the partition being modified is not marked FAILED
.
index
is not marked IN_PROGRESS
.
index
is not marked IN_PROGRESS
or FAILED
and partition
is not marked IN
_PROGRESS
.
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:
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.
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.
Specify RENAME
TO
to rename an index or a partition of an index. The new_index_name
is a single identifier and does not include the schema name.
Restrictions:
index
and any partitions of index
must not be marked IN_PROGRESS
or FAILED
.
index
must not be marked IN_PROGRESS
or FAILED
, none of the partitions can be marked IN_PROGRESS
, and the partition you are renaming must not be marked FAILED
.
Specify COALESCE
to instruct Oracle to merge the contents of index blocks where possible to free blocks for reuse.
Restriction:
COALESCE
clause of ALTER
TABLE
.
See Also:
COALESCE
for information on coalescing space of an index-organized table
Use this clause to begin or end the collection of statistics on index usage. This clause is useful in determining whether an index is being used.
Specify MONITORING
USAGE
to begin statistics collection. Oracle first clears existing statistics on index
and then begins to collect statistics on index usage. Statistics collection continues until a subsequent ALTER
INDEX
... NOMONITORING
USAGE
statement is executed.
To terminate collection of statistics on index, specify NOMONITORING
USAGE
.
To see the statistics collected, query the ALL_
, USER_
, or DBA_INDEXES
data dictionary views. To determine when the statistics collection began and ended, query the V$OBJECT_USAGE
dynamic performance view.
See Also:
Oracle9i Database Reference for information on the data dictionary and dynamic performance views |
The UPDATE
BLOCK
REFERENCES
clause is valid only for normal and domain indexes on index-organized tables. Specify this clause to update all the stale "guess" data block addresses stored as part of the index row with the correct database address for the corresponding block identified by the primary key.
Restriction: You cannot combine this clause with any other clause of ALTER
INDEX
.
The partitioning clauses of the ALTER
INDEX
statement are valid only for partitioned indexes.
Note: The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle9i Database Administrator's Guide for a discussion of these restrictions. |
Restrictions:
ALTER
INDEX
statement (except RENAME
and REBUILD
), but you cannot combine partition operations with other partition operations or with operations on the base index.
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
.
Specify the default tablespace for new partitions of an index or subpartitions of an index partition.
Specify the default logging attribute of a partitioned index or an index 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.
Restriction: You cannot specify FOR
PARTITION
for a list partition.
Use the modify_index_partition
clause to modify the real physical attributes, logging attribute, or storage characteristics of index partition partition
or its subpartitions.
The UPDATE
BLOCK
REFERENCES
clause is valid only for normal indexes on index-organized tables. Use this clause to update all stale "guess" data block addresses stored in the secondary index partition.
Restrictions:
physical_attributes_clause
for an index on a hash-partitioned table.
UPDATE
BLOCK
REFERENCES
with any other clause in ALTER
INDEX
.
Use the rename_index_partition
clauses to rename index partition or subpartition to new_name
.
Restriction: You cannot rename the subpartition of a list partition.
Use the drop_index_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.
Use the split_index_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.
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).
Specify (optionally) the name and physical attributes of each of the two partitions resulting from the split.
Use the modify_index_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.
Restriction: You cannot modify the subpartition of a list partition.
The following statement alters the oe.cust_lname_ix
index so that future data blocks within this index use 5 initial transaction entries and an incremental extent of 100 kilobytes:
ALTER INDEX oe.cust_lname_ix INITRANS 5 STORAGE (NEXT 100K);
If the oe.cust_lname_ix
index were partitioned, this statement would also alter the default attributes of future partitions of the index. New partitions added in the future would then use 5 initial transaction entries and an incremental extent of 100K.
The following statement drops index partition ix_antarctica
:
ALTER INDEX sales_area_ix DROP PARTITION ix_antarctica;
The following 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 );
The following statement marks the idx_acctno
index as UNUSABLE
:
ALTER INDEX idx_acctno UNUSABLE;
The following statement marks partition idx_feb96
of index idx_acctno
as UNUSABLE
:
ALTER INDEX idx_acctno MODIFY PARTITION idx_feb96 UNUSABLE;
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;
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;
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;
The following statement renames an index:
ALTER INDEX emp_ix1 RENAME TO employee_ix1;
The following statement renames an index partition:
ALTER INDEX employee_ix1 RENAME PARTITION emp_ix1_p3 TO employee_ix1_p3;
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.
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;
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.
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;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|