ALTER INDEX REBUILD

Syntax

ALTER INDEX [schema.]index REBUILD  
     [PARAMETERS ('rebuild_params [physical_storage_params]' ) ] 
     [{ NOPARALLEL | PARALLEL [ integer ] }] ;

or

ALTER INDEX [schema.]index REBUILD ONLINE 
     [PARAMETERS ('rebuild_params [physical_storage_params]' ) ] 
     [{ NOPARALLEL | PARALLEL [ integer ] }] ;

or

ALTER INDEX [schema.]index REBUILD PARTITION partition  
     [PARAMETERS ('rebuild_params [physical_storage_params]' ) ];

Purpose

Rebuilds a spatial index or a specified partition of a partitioned index.

Keywords and Parameters

Value	Description
REBUILD_PARAMS	Specifies in a command string the index parameters to use in rebuilding the spatial index.
index_status=cleanup	For an online rebuild operation (ALTER INDEX REBUILD ONLINE), performs cleanup operations on tables associated with the older version of the index.
layer_gtype	Checks to ensure that all geometries are of a specified geometry type. The value must be from the Geometry Type column in SDO_GTYPE (except that UNKNOWN_GEOMETRY is not allowed). In addition, specifying POINT allows for optimized processing of point data. Data type is VARCHAR2.
sdo_dml_batch_size	Specifies the number of index updates to be processed in each batch of updates after a commit operation. The default value is 4000; for example, if you insert 5000 rows into the spatial table and then perform a commit operation, the updates to the spatial index table are performed in two batches of insert operations (4000 and 1000). See the Usage Notes for the CREATE INDEX statement for more information. Data type is NUMBER.
sdo_indx_dims	Specifies the number of dimensions to be indexed. For example, a value of 2 causes only the first two dimensions to be indexed. Must be less than or equal to the number of actual dimensions. For usage information related to three-dimensional geometries, see Three-Dimensional Spatial Objects. Data type is NUMBER. Default = 2.
sdo_max_memory	Specifies the amount of maximum memory that can be allocated to perform a spatial index build or rebuild operation. Can be from 64000 (about 64 KB) to 200000000 (about 200 MB). If the specified number of bytes cannot be allocated, 64000 (about 64 KB) is allocated. Specifying a value greater than the default can significantly improve index creation performance; however, do not specify more than 20 percent of available memory. Data type is NUMBER. Default = 10000000 (about 10 MB).
sdo_rtr_pctfree	Specifies the minimum percentage of slots in each index tree node to be left empty when the index is created. Slots that are left empty can be filled later when new data is inserted into the table. The value can range from 0 to 50. Data type is NUMBER. Default = 10.
PHYSICAL_STORAGE_PARAMS	Determines the storage parameters used for rebuilding the spatial index data table. A spatial index data table is a regular Oracle table with a prescribed format. Not all physical storage parameters that are allowed in the STORAGE clause of a CREATE TABLE statement are supported. The following is a list of the supported subset.
tablespace	Specifies the tablespace in which the index data table is created. Same as TABLESPACE in the STORAGE clause of a CREATE TABLE statement.
initial	Is the same as INITIAL in the STORAGE clause of a CREATE TABLE statement.
next	Is the same as NEXT in the STORAGE clause of a CREATE TABLE statement.
minextents	Is the same as MINEXTENTS in the STORAGE clause of a CREATE TABLE statement.
maxextents	Is the same as MAXEXTENTS in the STORAGE clause of a CREATE TABLE statement.
pctincrease	Is the same as PCTINCREASE in the STORAGE clause of a CREATE TABLE statement.
{ NOPARALLEL \| PARALLEL [ integer ] }	Controls whether serial (NOPARALLEL) execution or parallel (PARALLEL) execution is used for the rebuilding of the index and for subsequent queries and DML operations that use the index. For parallel execution you can specify an integer value of degree of parallelism. See the Usage Notes for the CREATE INDEX statement for guidelines and restrictions that apply to the use of the PARALLEL keyword. Default = NOPARALLEL. (If PARALLEL is specified without an integer value, the Oracle database calculates the optimum degree of parallelism.)

Prerequisites

You must have EXECUTE privileges on the index type and its implementation type.
The spatial index to be altered is not marked in-progress.

Usage Notes

An ALTER INDEX REBUILD 'rebuild_params' statement rebuilds the index using supplied parameters. Spatial index creation involves creating and inserting index data, for each row in the underlying table column being spatially indexed, into a table with a prescribed format. All rows in the underlying table are processed before the insertion of index data is committed, and this requires adequate rollback segment space.

The ONLINE keyword rebuilds the index without blocking the index; that is, queries can use the spatial index while it is being rebuilt. However, after all queries issued during the rebuild operation have completed, you must clean up the old index information (in the MDRT tables) by entering a SQL statement in the following form:

ALTER INDEX [schema.]index REBUILD ONLINE PARAMETERS ('index_status=cleanup');

The following limitations apply to the use of the ONLINE keyword:

Only query operations are permitted while the index is being rebuilt. Insert, update, and delete operations that would affect the index are blocked while the index is being rebuilt; and an online rebuild is blocked while any insert, update, or delete operations that would affect the index are being performed.
You cannot use the ONLINE keyword for a rebuild operation if the index was created using the 'sdo_non_leaf_tbl=TRUE' parameter.
You cannot use the ONLINE keyword for a partitioned spatial index.

Effective with Release 12.1, the ALTER INDEX REBUILD statement reuses any previous parameters from the index creation. If new or changed parameters are passed, new parameters are merged with the previous ones, and changed parameters override the previous ones.

For more information about using the layer_gtype keyword to constrain data in a layer to a geometry type, see Constraining Data to a Geometry Type.

With a partitioned spatial index, you must use a separate ALTER INDEX REBUILD statement for each partition to be rebuilt.

If you want to use a local partitioned spatial index, follow the procedure in Creating a Local Partitioned Spatial Index.

See also the Usage Notes for the CREATE INDEX statement for usage information about many of the available parameters and about the use of the PARALLEL keyword.

Examples

The following example rebuilds OLDINDEX and specifies the tablespace in which to create the index data table.

ALTER INDEX oldindex REBUILD PARAMETERS('tablespace=TBS_3');