Oracle8i Spatial User's Guide and Reference
Release 8.1.5

A67295-01

Library

Product

Contents

Index

Prev Next

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

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

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

Usage Notes

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


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

Usage Notes

None.

Examples

The following example renames the index `oldindex' to `newindex'.

ALTER INDEX oldindex RENAME TO newindex ;

Related Topics


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

Usage Notes

Related Topics


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




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index