5
Indexing Statements for Object Relational Model

This chapter describes the statements used when working with the spatial object data type. The statements are listed in Table 5-1.

Table 5-1 Spatial Index Creation and Usage Statements

Statement	Description
ALTER INDEX	Alter a spatial index on a column of type MDSYS.SDO_GEOMETRY.
ALTER INDEX REBUILD	Rebuild a spatial index on a column of type MDSYS.SDO_GEOMETRY.
ALTER INDEX RENAME TO	Change the name of a spatial index on a column of type MDSYS.SDO_GEOMETRY.
CREATE INDEX	Create a spatial index on a column of type MDSYS.SDO_GEOMETRY.
DROP INDEX	Delete a spatial index on a column of type MDSYS.SDO_GEOMETRY

---

ALTER INDEX

Purpose

This statement 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

INDEX_PARAMS	Allows you to change the type, (fixed or hybrid), and characteristics of the spatial index.
Keyword	Description
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 DROP INDEX. Data type is VARCHAR2.
sdo_level	Specifies the desired fixed-size tiling level. Data type is NUMBER.
sdo_numtiles	Specifies the number of variable-sized tiles to be used in tessellating an object. Data type is NUMBER.
sdo_maxlevel	Specifies the maximum tiling level. This parameter determines the tiling resolution. It must be greater than the sdo_level minimum tiling level. Modifying the default value is not recommended. Data type is NUMBER. Default is 32.
sdo_commit_interval	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.
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_params that are allowed in the STORAGE clause of a CREATE TABLE statement are supported. The following is a list of the supported subset.
Keyword	Description
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 btree index.
btree_next	Is the same as NEXT in the STORAGE clause of a CREATE INDEX statement in the case of a standard btree index.
btree_pctincrease	Is the same as PCTINCREASE in the STORAGE clause of a CREATE INDEX statement in the case of a standard btree index.

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.

Examples

ALTER INDEX qtree PARAMETERS ('add_index=HYBRID_INDEX 

                               sdo_numtiles=8

                               initial=100M

                               next=1M 

                               pctincrease=0 

                               btree_initial=5M

                               btree_next=1M 

                                  btree_pctincrease=0');

Related Topics

• ALTER INDEX REBUILD

• ALTER INDEX RENAME TO

---

ALTER INDEX REBUILD

Purpose

This function rebuilds a spatial index.

Syntax

ALTER INDEX [schema.]index REBUILD
[PARAMETERS (`rebuild_params [physical_storage_params]' ) ]

Keywords and Parameters

REBUILD_PARAMS	Specifies in a command string the index parameters to use in rebuilding the spatial index.
Keyword	Description
rebuild_index	Specifies the name of the spatial index table to be rebuilt. Data type is VARCHAR2.
sdo_level	Specifies the desired fixed-size tiling level. Data type is NUMBER.
sdo_numtiles	Specifies the number of variable-sized tiles to be used in tessellating an object. Data type is NUMBER.
sdo_maxlevel	Specifies the maximum tiling level. This parameter determines the tiling resolution. It must be greater than the sdo_level minimum tiling level. Modifying the default value is not recommended. Data type is NUMBER. Default is 32.
sdo_commit_interval	Specifies the number of underlying table rows that are processed between commit intervals for the index data. The default behavior is that a commit of the index data is done only after all rows in the underlying table have been processed. See the Usage Notes for further details. Data type is NUMBER.
layer_gtype	Specifies special processing for point data. If the layer you are indexing is all points, set the parameter to 'POINT' for optimal performance. Data type is VARCHAR2.
PHYSICAL_STORAGE_PARAMS	Determines the storage parameters used for rebuiding 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.
Keyword	Description
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.
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.
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.

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

• sdo_commit_interval: An ALTER INDEX REBUILD `rebuild_params' statement will rebuild 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 perform commits of index data after every N rows of the underlying table have been processed. This is done by specifying that sdo_commit_interval = N. The potential complication is that if there is an error during index rebuild and periodic commits 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 command does not remember any previous index parameters. All parameters should be specified for the index you want to rebuild.

Examples

The following example builds oldindex with an SDO_LEVEL = 12 and SDO_NUMTILES = NULL.

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

Related Topics

• CREATE INDEX

• DROP INDEX

---

ALTER INDEX RENAME TO

Purpose

This statement alters the name of a spatial index.

Syntax

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

Keywords and Parameters

new_index_name

Specifies the new name of the index.

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 the index `oldindex' to `newindex'.

ALTER INDEX oldindex RENAME TO newindex ;

Related Topics

• CREATE INDEX

• DROP INDEX

---

CREATE INDEX

Purpose

This statement creates a spatial index on a column of type MDSYS.SDO_GEOMETRY.

Syntax

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

INDEXTYPE IS MDSYS.SPATIAL_INDEX

[PARAMETERS `index_params [physical_storage_params]');

Keywords and Parameters

INDEX_PARAMS	Determine the type, fixed or hybrid, and characteristics of the spatial index.
Keyword	Description
sdo_level	Specifies the desired fixed-size tiling level. Data type is NUMBER.
sdo_numtiles	Specifies the number of variable-sized tiles to be used in tessellating an object Data type is NUMBER.
sdo_maxlevel	Specifies the maximum tiling level. This parameter determines the tiling resolution. It must be set greater than the sdo_level minimum tiling level. Modifying the default value is not recommended. Data type is NUMBER. Default is 32.
sdo_commit_interval	Specifies the number of underlying table rows that are processed between commit intervals for the index data. The default behavior is that a commit of the index data is done only after all rows in the underlying table have been processed. See the Usage Notes for further details. Data type is NUMBER.
layer_gtype	Specifies special processing for point data. If the layer you are indexing is all points, set this parameter to 'POINT' for optimal performance. Data type is VARCHAR2.
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.
Keyword	Description
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.
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.
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.

Prerequisites

• All the current SQL CREATE INDEX prerequisites apply.

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

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

Usage Notes

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

• The index_params string must contain valid values for either SDO_LEVEL or both SDO_LEVEL and SDO_NUMTILES.

• DEFAULT VALUES:
  • sdo_numtiles must be supplied with a value greater than or equal to one 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.

  • sdo_maxlevel equals 32. Modification is not recommended.

• SDO_LEVEL must be greater than zero and less than SDO_MAXLEVEL

• The value passed to SDO_NUMTILES is considered a recommendation. In some cases, this value may be overwritten by the indexing algorithm.

• sdo_commit_interval: 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 the rebuilding of the index, and the spatial index data was periodically committed, then the spatial index will be in an inconsistent state. The only recovery option is to use DROP INDEX 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 are shown in Table 5-2.

  Table 5-2 SDO_LEVEL and SDO_NUMTILE Combinations

  SDO_LEVEL	SDO_NUMTILES	Type of Spatial Index
  Not specified.	Not specified.	Error.
  >= 1	Not specified.	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 per geometry.
  Not specified.	>= 1	Not supported.

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

• 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.

Related Topics

• ALTER INDEX

• DROP INDEX

---

DROP INDEX

Purpose

This statement deletes a spatial index.

Syntax

DROP INDEX [schema.]index [FORCE]

Keywords and Parameters

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

1. DROP INDEX oldindex

2. DROP INDEX oldindex FORCE

Related Topics

• CREATE INDEX

---