ALTER INDEX

Purpose

Prerequisites

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the index's creation label or you must satisfy one of these criteria:

• If the index's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

• If the index's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

• If the index's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Syntax

Keywords and Parameters

schema

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

index

is the name of the index to be altered.

PCTFREE INITRANS MAXTRANS

changes the values of these parameters for the index. See the PCTFREE, INITRANS and MAXTRANS parameters of the CREATE TABLE command .

STORAGE

changes the storage parameters for the index. See the STORAGE clause .

ALLOCATE EXTENT

explicitly allocates a new extent for the index.

SIZE specifies the size of the extent in bytes. You can use K or M to specify the extent size in kilobytes or megabytes. If you omit this parameter, Oracle7 determines the size based on the values of the index's STORAGE parameters.

DATAFILE specifies one of the data files in the index's tablespace to contain the new extent. If you omit this parameter, Oracle7 chooses the data file.

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. Only use this parameter if you are using Oracle7 with the Parallel Server option in parallel mode.

Explicitly allocating an extent with this clause does affect the size for the next extent to be allocated as specified by the NEXT and PCTINCREASE storage parameters.

DEALLOCATE UNUSED

explicitly deallocates unused space at the end of the index and make the freed space available for other segments. Only unused space above the high-water mark can be freed. If KEEP is omitted, all unused space is freed. For more information, see the deallocate_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.

REBUILD

create the index anew using the existing index.

PARALLEL use integer parallel processes to build the new index.

NOPARALLEL do not use parallel processes to build the new index. This is the default.

RECOVERABLE

specifies that the creation of the index will be logged in the redo log file. This is the default.

If the database is run in ARCHIVELOG mode, media recovery from a backup will recreate the index. You cannot specify RECOVERABLE when using NOARCHIVELOG mode.

UNRECOVERABLE

specifies that the creation of the index will not be logged in the redo log file. As a result, media recovery will not recreate the index.

When this option is used, index creation is faster than the RECOVERABLE option because no redo log entries are written.

TABLESPACE

specifies the tablespace where the rebuilt index will be stored. The default is the default tablespace of the user issuing the command.

Usage Notes

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) 

Related Topics