ALTER INDEX

Purpose

Alters specific parameters for a spatial index.

Syntax

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

Keywords and Parameters

Value	Description
INDEX_PARAMS	Changes the characteristics of the spatial index.
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_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. The default value is best for most applications; however, a value of 0 is recommended if no updates will be performed to the geometry column. Data type is NUMBER. Default = 10.
PHYSICAL_STORAGE_PARAMS	Determines the storage parameters used for altering the spatial index data table. A spatial index data table is a standard 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. This parameter is the 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 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

Use this statement to change the parameters of an existing index.

See the Usage Notes for the CREATE INDEX statement for usage information about many of the other available parameters.

Examples

The following example modifies the tablespace for partition IP2 of the spatial index named BGI.

ALTER INDEX bgi MODIFY PARTITION ip2 
   PARAMETERS ('tablespace=TBS_3');