Release 8.1.6
A77132-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Spatial User's Guide and Reference Release 8.1.6 A77132-01 |
|
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
Alters specific parameters for a spatial index or rebuilds a spatial index.
ALTER INDEX [schema.]index PARAMETERS (`index_params [physical_storage_params]' )
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 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');
Rebuilds a spatial index.
ALTER INDEX [schema.]index REBUILD
[PARAMETERS (`rebuild_params [physical_storage_params]' ) ]
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 command does not remember any previous index parameters. All parameters should be specified for the index you want to rebuild.
The following example rebuilds oldindex with an SDO_LEVEL value of 12.
ALTER INDEX oldindex REBUILD PARAMETERS('sdo_level=12');
Alters the name of a spatial index.
ALTER INDEX [schema.]index RENAME TO <new_index_name>
|
new_index_name |
Specifies the new name of the index. |
The new_index_name string must not be longer than 18 characters.
The following example renames oldindex to newindex.
ALTER INDEX oldindex RENAME TO newindex;
Creates a spatial index on a column of type MDSYS.SDO_GEOMETRY.
CREATE INDEX [schema.]<index_name> ON [schema.]<tableName> (column)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
[PARAMETERS `index_params [physical_storage_params]']);
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.
Other options available for regular indexes (such as ASC and DESC) are not applicable for spatial indexes.
The index_name string must not be longer than 18 characters.
Default Values:
The sdo_level value must be greater than zero and less than the sdo_maxlevel value.
The sdo_numtiles value is considered a recommendation. In some cases, this value may be overwritten 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 is shown in Table 5-2.
If a tablespace name is provided in the parameters clause, the user (underlying table owner) must have appropriate privileges for that tablespace.
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.
The following example creates an index named qtree.
CREATE INDEX qtree ON POLY_4PT(geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX PAREMETERS('sdo_numtiles=4 sdo_level=6 sdo_maxlevel=36 sdo_commit_interval=500 tablespace=system initial=10K next=10K pctincrease=10 minextents=10 maxextents=20');
Deletes a spatial index.
DROP INDEX [schema.]index [FORCE]
|
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. |
You must have EXECUTE privileges on the index type and its implementation type.
Use DROP INDEX indexname FORCE to clean up after a failure in the CREATE INDEX statement.
|
 Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|