8
SQL Statements for Indexing Spatial Data

ALTER INDEX

Purpose

Alters specific parameters for a spatial index or rebuilds a spatial index.

Syntax

ALTER INDEX [schema.]index PARAMETERS ('index_params [physical_storage_params]' );

Keywords and Parameters

Value	Description
INDEX_PARAMS	Allows you to change the characteristics of the spatial index, and the type (fixed or hybrid) of a quadtree index. Some keywords apply only to R-tree or to quadtree indexes.
add_index	Specifies the name of the new index table to add. Data type is VARCHAR2.
delete_index	Specifies the name of the index table to delete. You can only delete index tables that were created with the ALTER INDEX add_index statement. The primary index table cannot be deleted with this parameter. To delete the primary index table, use the DROP INDEX statement. Data type is VARCHAR2.
index_status	Specifies that index modifications are to be deferred ('index_status=deferred') or that deferred index modifications are to be synchronized with the data in the spatial table ('index_status=synchronize'). See the Usage Notes for further details. Data type is VARCHAR2.
sdo_commit_interval	Quadtree indexes only: Specifies the number of underlying table rows that are processed between commit intervals for the index data. The default behavior commits the index data only after all rows in the underlying table have been processed. See the Usage Notes for the CREATE INDEX statement for further details. Data type is NUMBER.
sdo_indx_dims	R-tree indexes only: Specifies the number of dimensions to be indexed. For example, a value of 2 causes the first two dimensions to be indexed. Must be less than or equal to the number of actual dimensions (number of SDO_DIM_ELEMENT instances in the dimensional array that describes the geometry objects in the column). Note that if the value is 3 or higher, the only Spatial operator that can be used on the indexed geometries is SDO_FILTER; the other operators described in Chapter 10 cannot be used. Data type is NUMBER. Default = 2.
sdo_level	Quadtree indexes only: Specifies the desired fixed-size tiling level. Data type is NUMBER.
sdo_numtiles	Quadtree indexes only: Specifies the number of variable-sized tiles to be used in tessellating an object. Data type is NUMBER.
sdo_rtr_pctfree	R-tree indexes only: 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.
btree_initial	Is the same as INITIAL in the STORAGE clause of a CREATE INDEX statement in the case of a standard B-tree index. (Quadtree indexes only.)
btree_next	Is the same as NEXT in the STORAGE clause of a CREATE INDEX statement in the case of a standard B-tree index. (Quadtree indexes only.)
btree_pctincrease	Is the same as PCTINCREASE in the STORAGE clause of a CREATE INDEX statement in the case of a standard B-tree index. (Quadtree indexes only.)

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

This statement is used to change the parameters of an existing index. This is the only way you can add or build multiple indexes on the same column.

The index_status keyword lets you defer modifications to the spatial index when geometries are inserted, updated, or deleted in a spatial table. Deferring the index modifications allows the geometry insert, update, and delete operations to be completed sooner, and it can reduce concurrency issues with R-tree indexes if multiple sessions are inserting rows into the spatial table. While index modifications are being deferred, spatial functions and procedures will work correctly with the current table data; however, spatial operator-based queries might perform more slowly, will not include the results of new insert operations, and might not include the results of new update operations. Therefore, you are advised not to use spatial operators while index modifications are being deferred.

For partitioned indexes, the index status can only be changed for a single partition at a time. That is, you cannot set all index partitions to deferred status with a single ALTER INDEX statement.

If you set the index status to deferred, you must later set the status to synchronize to make the index reflect the data in the table and to set the index to a valid state.

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

Examples

The following example adds a new index table named FIXED_INDEX$ to the index named QTREE.

ALTER INDEX qtree PARAMETERS ('add_index=fixed_index$
                               sdo_level=8
                               initial=100M
                               next=1M
                               pctincrease=0
                               btree_initial=5M
                               btree_next=1M
                               btree_pctincrease=0');

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

ALTER INDEX bgi MODIFY PARTITION ip2 
   PAREMETERS ('tablespace=TBS_3 sdo_level=4');

The following example defers index modifications and later (after the updates to the spatial table) synchronizes the index to reflect the table.

ALTER INDEX xyz_idx PARAMETERS ('index_status=deferred');
   .
   . <Insert rows in spatial table.>
   .
ALTER INDEX xyz_idx PARAMETERS ('index_status=synchronize');

The following example defers index modifications for an index partition and later (after the updates to the spatial table) synchronizes the index partition to reflect the table.

ALTER INDEX part_sidx MODIFY PARTITION p3 
   PARAMETERS ('index_status=deferred');
   .
   . <Insert rows in spatial table.>
   .
ALTER INDEX part_sidx MODIFY PARTITION p3 
   PARAMETERS ('index_status=synchronize');

Related Topics

• ALTER INDEX REBUILD

• ALTER INDEX RENAME TO

• CREATE INDEX

• ALTER TABLE (clauses for partition maintenance) in the Oracle9i SQL Reference

ALTER INDEX REBUILD

Syntax

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

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. Some keywords apply only to R-tree or to quadtree indexes.
layer_gtype	Checks to ensure that all geometries are of a specified geometry type. The value must be from the Geometry Type column of Table 2-1 in Section 2.2.1 (except that UNKNOWN_GEOMETRY is not allowed). In addition, specifying POINT allows for optimized processing of point data. Data type is VARCHAR2.
rebuild_index	Specifies the name of the spatial index table to be rebuilt. Data type is VARCHAR2.
sdo_commit_interval	Quadtree indexes only: Specifies the number of underlying table rows that are processed between commit intervals for the index data. The default behavior commits the index data only after all rows in the underlying table have been processed. See the Usage Notes for further details. Data type is NUMBER.
sdo_indx_dims	R-tree indexes only: Specifies the number of dimensions to be indexed. For example, a value of 2 causes the first two dimensions to be indexed. Must be less than or equal to the number of actual dimensions (number of SDO_DIM_ELEMENT instances in the dimensional array that describes the geometry objects in the column). Note that if the value is 3 or higher, the only Spatial operator that can be used on the indexed geometries is SDO_FILTER; the other operators described in Chapter 10 cannot be used. Data type is NUMBER. Default = 2.
sdo_level	Quadtree indexes only: Specifies the desired fixed-size tiling level. Data type is NUMBER.
sdo_numtiles	Quadtree indexes only: Specifies the number of variable-sized tiles to be used in tessellating an object. Data type is NUMBER.
sdo_rtr_pctfree	R-tree indexes only: 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.
btree_initial	Is the same as INITIAL in the STORAGE clause of a CREATE INDEX statement in the case of a standard B-tree index. (Quadtree indexes only.)
btree_next	Is the same as NEXT in the STORAGE clause of a CREATE INDEX statement in the case of a standard B-tree index. (Quadtree indexes only.)
btree_pctincrease	Is the same as PCTINCREASE in the STORAGE clause of a CREATE INDEX statement in the case of a standard B-tree index. (Quadtree indexes only.)
{ NOPARALLEL | PARALLEL [ integer ] }	Controls whether the rebuilding of the index is performed using serial execution (NOPARALLEL) or parallel (PARALLEL) execution. 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 server 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. The default, or normal, operation is that all rows in the underlying table are processed before the insertion of index data is committed. This requires adequate rollback segment space.

You may choose to commit index data after every n rows of the underlying table have been processed. This is done by specifying SDO_COMMIT_INTERVAL = n. The potential complication is that, if there is an error during index rebuild and if periodic commit operations have taken place, then the spatial index will be in an inconsistent state. The only recovery option is to use DROP INDEX (possibly with the FORCE option) and CREATE INDEX statements after ensuring that the various tablespaces are the required size and any other error conditions have been removed.

This statement does not use any previous parameters from the index creation. All parameters should be specified for the index you want to rebuild.

For more information about using the layer_gtype keyword to constrain data in a layer to a geometry type, see Section 4.1.5.

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

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 with an SDO_LEVEL value of 12.

ALTER INDEX oldindex REBUILD PARAMETERS('sdo_level=12');

The following example uses the ALTER INDEX statement to add a new index table named USBG_IDX_QTREE to the index named USBG_IDX, and later it uses the ALTER INDEX REBUILD statement to rebuild the index table using a different sdo_level value and specifying parallel execution. This approach lets you add a small index table, and later rebuild it in parallel execution mode with the desired parameter values (necessary because you cannot specify parallel execution for ALTER INDEX).

ALTER INDEX usbg_idx PARAMETERS (add_index=usbg_idx_qtree sdo_level=6');

-- (Add substantial geometry data to the spatial table)

ALTER INDEX usbg_idx REBUILD 
   PARAMETERS ('rebuild_index=usbg_idx_qtree sdo_level=14') PARALLEL;

Related Topics

• CREATE INDEX

• DROP INDEX

• ALTER TABLE (clauses for partition maintenance) in the Oracle9i SQL Reference

ALTER INDEX RENAME TO

Syntax

ALTER INDEX [schema.]index RENAME TO <new_index_name>;

ALTER INDEX [schema.]index PARTITION partition RENAME TO <new_partition_name>;

Purpose

Alters the name of a spatial index or a partition of a spatial index.

Keywords and Parameters

Value	Description
new_index_name	Specifies the new name of the index.
new_partition_name	Specifies the new name of the partition.

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

None.

Examples

The following example renames OLDINDEX to NEWINDEX.

ALTER INDEX oldindex RENAME TO newindex;

Related Topics

• CREATE INDEX

• DROP INDEX

CREATE INDEX

Syntax

CREATE INDEX [schema.]<index_name> ON [schema.]<tableName> (column)

     INDEXTYPE IS MDSYS.SPATIAL_INDEX

     [PARAMETERS ('index_params [physical_storage_params]' )]

     [{ NOPARALLEL | PARALLEL [ integer ] }];

Purpose

Creates a spatial index on a column of type MDSYS.SDO_GEOMETRY.

Keywords and Parameters

Value	Description
INDEX_PARAMS	Determines the type (R-tree or quadtree; and for quadtree, fixed or hybrid) and the characteristics of the spatial index. Some keywords apply only to R-tree or to quadtree indexes.
geodetic	'geodetic=FALSE' allows a non-geodetic index to be built on geodetic data, but with restrictions. (FALSE is the only acceptable value for this keyword.) See the Usage Notes for more information. Data type is VARCHAR2.
layer_gtype	Checks to ensure that all geometries are of a specified geometry type. The value must be from the Geometry Type column of Table 2-1 in Section 2.2.1 (except that UNKNOWN_GEOMETRY is not allowed). In addition, specifying POINT allows for optimized processing of point data. Data type is VARCHAR2.
sdo_commit_interval	Quadtree indexes only: Specifies the number of underlying table rows that are processed between commit intervals for the index data. The default behavior commits the index data only after all rows in the underlying table have been processed. See the Usage Notes for further details. Data type is NUMBER.
sdo_indx_dims	R-tree indexes only: Specifies the number of dimensions to be indexed. For example, a value of 2 causes the first two dimensions to be indexed. Must be less than or equal to the number of actual dimensions (number of SDO_DIM_ELEMENT instances in the dimensional array that describes the geometry objects in the column). Note that if the value is 3 or higher, the only Spatial operator that can be used on the indexed geometries is SDO_FILTER; the other operators described in Chapter 10 cannot be used. Data type is NUMBER. Default = 2.
sdo_level	Quadtree indexes only: Specifies the desired fixed-size tiling level. Data type is NUMBER.
sdo_numtiles	Quadtree indexes only: Specifies the number of variable-sized tiles to be used in tessellating an object. Data type is NUMBER.
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. (R-tree indexes only.) The value can range from 0 to 50. Data type is NUMBER. Default = 10.
PHYSICAL_STORAGE_PARAMS	Determines the storage parameters used for creating the spatial index data table. A spatial index data table is a regular Oracle table with a prescribed format. Not all physical_storage_params 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.
btree_initial	Is the same as INITIAL in the STORAGE clause of a CREATE INDEX statement in the case of a standard B-tree index. (Quadtree indexes only.)
btree_next	Is the same as NEXT in the STORAGE clause of a CREATE INDEX statement in the case of a standard B-tree index. (Quadtree indexes only.)
btree_pctincrease	Is the same as PCTINCREASE in the STORAGE clause of a CREATE INDEX statement in the case of a standard B-tree index. (Quadtree indexes only.)
{ NOPARALLEL | PARALLEL [ integer ] }	Controls whether the creation of the index is performed using serial execution (NOPARALLEL) or parallel (PARALLEL) execution. For parallel execution you can specify an integer value of degree of parallelism. See the Usage Notes for more information about parallel index creation. Default = NOPARALLEL. (If PARALLEL is specified without an integer value, the Oracle database server calculates the optimum degree of parallelism.)

Prerequisites

• All the current SQL CREATE INDEX prerequisites apply.

• You must have EXECUTE privilege on the index type and its implementation type.

• The USER_SDO_GEOM_METADATA view must contain an entry with the dimensions and coordinate boundary information for the table column to be spatially indexed.

Usage Notes

For information about R-tree and quadtree indexes, see Section 1.7.

By default, an R-tree index is created if the index_params string does not contain the sdo_level keyword or if the sdo_level value is zero (0). If the index_params string contains the sdo_level keyword with a nonzero value, a quadtree index is created. Some keywords apply only to R-tree or quadtree indexes, as noted in the Keywords and Parameters section.

Before you create an R-tree index, be sure that the rollback segment size and the SORT_AREA_SIZE parameter value are adequate, as described in Section 4.1.1.

For a quadtree index, the index_params string must contain either sdo_level or both sdo_level and sdo_numtiles, and any values specified for these parameters must be valid.

If an R-tree index is used on linear referencing system (LRS) data and if the LRS data has four dimensions (three plus the M dimension), the sdo_indx_dims parameter must be used and must specify 3 (the number of dimensions minus one), to avoid the default sdo_indx_dims value of 2, which would index only the X and Y dimensions. For example, if the dimensions are X, Y, Z, and M, specify sdo_indx_dims=3 to index the X, Y, and Z dimensions, but not the measure (M) dimension. (The LRS data model, including the measure dimension, is explained in Section 6.2.)

A partitioned spatial index can be created on a partitioned table. See Section 4.1.7 for more information about partitioned spatial indexes, including benefits and restrictions.

A spatial index cannot be created on an index-organized table.

You can specify the PARALLEL keyword to cause the index creation to be parallelized. For example:

CREATE INDEX cola_spatial_idx ON cola_markets(shape)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX PARALLEL;

For information about using the PARALLEL keyword, see the description of the parallel_clause in the section on the CREATE INDEX statement in the Oracle9i SQL Reference. In addition, the following notes apply to the use of the PARALLEL keyword for creating or rebuilding (using the ALTER INDEX REBUILD statement) spatial indexes:

• The PARALLEL clause is not supported for adding an index table with the ALTER INDEX statement; however, it is supported for rebuilding such an index table with the ALTER INDEX REBUILD statement. One useful scenario is to add a small second index table, and later rebuild the index table specifying the desired parameters and using parallel execution. See the parallel execution example for the ALTER INDEX REBUILD statement.

• The performance cost and benefits from parallel execution for creating or rebuilding an index depend on a system's resources and load. If the system's CPUs or disk controllers are already heavily loaded, you should not specify the PARALLEL keyword.

• Specifying PARALLEL for creating or rebuilding an index on tables with simple geometries, such as point data, usually results in less performance improvement than on tables with complex geometries. However, for a function-based quadtree index on point data, parallel execution does improve index creation performance significantly.

• For a quadtree index on mixed point and nonpoint data, specifying PARALLEL can degrade the performance of creating or rebuilding an index

Other options available for regular indexes (such as ASC and DESC) are not applicable for spatial indexes.

Default values for quadtree indexing:

• sdo_numtiles must be supplied with a value greater than or equal to 1 to perform hybrid indexing. If this parameter is not supplied, indexing with fixed-size tiles is performed.

• sdo_commit_interval does not allow spatial data to be committed at intervals. Insertion of spatial index data is committed only at the end of the index creation process. That is, it is committed after all rows in the underlying table have been processed.

The sdo_level value must be greater than zero.

If an sdo_numtiles value is specified, it might be overridden by the indexing algorithm.

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. The default, or normal, operation is that all rows in the underlying table are processed before the insertion of index data is committed. This requires adequate rollback segment space.

You may choose to commit index data after every n rows of the underlying table have been processed. This is done by specifying SDO_COMMIT_INTERVAL = n. The potential complication is that, if there is an error during index rebuild and if periodic commit operations have taken place, then the spatial index will be in an inconsistent state. The only recovery option is to use DROP INDEX (possibly with the FORCE option) and CREATE INDEX statements after ensuring that the various tablespaces are the required size and any other error conditions have been removed.

Interpretation of sdo_level and sdo_numtiles value combinations (quadtree indexing) is shown in Table 8-2.

Table 8-2 SDO_LEVEL and SDO_NUMTILES Combinations

SDO_LEVEL	SDO_NUMTILES	Action
Not specified or 0	Not specified or 0	R-tree index.
>= 1	Not specified or 0	Fixed indexing (indexing with fixed-size tiles).
>= 1	>= 1	Hybrid indexing with fixed-size and variable-sized tiles. The SDO_LEVEL column defines the fixed tile size. The SDO_NUMTILES column defines the number of variable tiles to generate for each geometry.
Not specified or 0	>= 1	Not supported (error).

If a tablespace name is provided in the parameters clause, the user (underlying table owner) must have appropriate privileges for that tablespace.

For more information about using the layer_gtype keyword to constrain data in a layer to a geometry type, see Section 4.1.5.

The 'geodetic=FALSE' parameter allows you to bypass the restriction that a standard quadtree index cannot be used with geodetic data. However, using this parameter is not recommended, because much of the Oracle Spatial geodetic support will be disabled, and some Spatial operations that use the quadtree index with geodetic data will not work correctly or will return less accurate results. This parameter should only be used if you cannot yet reindex the data with an R-tree index and if the results using the non-geodetic quadtree index are acceptable. (For more information about geodetic and non-geodetic indexes, see Section 4.1.4.)

Moreover, if you specify 'geodetic=FALSE', ensure that the tolerance value stored in the USER_SDO_GEOM_METADATA view is what would be used for Cartesian data. That is, do not use meters for the units of the tolerance value, but instead use the number of decimal places in the data followed by a 5 (for example, 0.00005). This tolerance value will be used for spatial operators. When you use spatial functions that require a tolerance value with this data, use the function format that allows you to specify a tolerance value, and specify the tolerance value in meters.

If you are creating a function-based spatial index, the number of parameters must not exceed 32. For information about using function-based spatial indexes, see Section 7.2.

To determine if a CREATE INDEX statement for a spatial index has failed, check to see if the DOMIDX_OPSTATUS column in the USER_INDEXES view is set to FAILED. Note that this is different from the case of regular indexes, where you check to see if the STATUS column in the USER_INDEXES view is set to FAILED.

If the CREATE INDEX statement fails because of an invalid geometry, the ROWID of the failed geometry is returned in an error message along with the reason for the failure.

If the CREATE INDEX statement fails for any reason, then the DROP INDEX statement must be used to clean up the partially built index and associated metadata. If DROP INDEX does not work, add the FORCE parameter and try again.

Examples

The following example creates a spatial R-tree index named COLA_SPATIAL_IDX. (An R-tree index is created by default if no quadtree-specific parameters are specified.)

CREATE INDEX cola_spatial_idx ON cola_markets(shape)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;

The following example creates a spatial quadtree index named QTREE.

CREATE INDEX qtree ON POLY_4PT(geometry)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX
  PARAMETERS('sdo_level=6
    sdo_commit_interval=500 tablespace=TBS_3 initial=10K
    next=10K pctincrease=10 minextents=10 maxextents=20');

The following example creates a spatial quadtree index named BG06075 with two partitions, named IP1 and IP2.

CREATE INDEX BGI ON BG06075(geometry) 
   INDEXTYPE IS MDSYS.SPATIAL_INDEX
    PARAMETERS ('tablespace=TBS_3 sdo_level=6') LOCAL
    (PARTITION IP1 PARAMETERS ('tablespace=TBS_3 sdo_level=6'),  
     PARTITION IP2 PARAMETERS ('tablespace=TBS_2 sdo_level=6'));

Related Topics

• ALTER INDEX

• DROP INDEX

DROP INDEX

Syntax

DROP INDEX [schema.]index [FORCE];

Purpose

Deletes a spatial index.

Keywords and Parameters

Value	Description
FORCE	Causes the spatial index to be deleted from the system tables even if the index is marked in-progress or some other error condition occurs.

Prerequisites

You must have EXECUTE privileges on the index type and its implementation type.

Usage Notes

Use DROP INDEX indexname FORCE to clean up after a failure in the CREATE INDEX statement.

Examples

The following example deletes a spatial quadtree index named OLDINDEX and forces the deletion to be performed even if the index is marked in-process or an error occurs.

DROP INDEX oldindex FORCE;

Related Topics

• CREATE INDEX