Release 8.1.7
Part Number A85337-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Spatial User's Guide and Reference Release 8.1.7 Part Number A85337-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]' )
|
INDEX_PARAMS |
Allows you to change the characteristics of the spatial index, and the type (fixed or hybrid) of a quadtree index. |
|
Keyword |
Description |
|
add_index |
Specifies the name of the new index table to add. |
|
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. |
|
sdo_commit_interval |
Specifies the number of underlying table rows that are processed between commit intervals for the index data. (Quadtree indexes only.) 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. |
|
sdo_fanout |
Specifies the fanout value, which reflects the node capacity of the index tree. (R-tree indexes only.) If queries that use the index are likely to return thousands of rows or more, you may want to specify a value greater than the default, such as 50 or 60. |
|
sdo_indx_dims |
Specifies the number of dimensions to be indexed. (R-tree indexes only.) For example, a value of 2 causes the first 2 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). |
|
sdo_level |
Specifies the desired fixed-size tiling level. (Quadtree indexes only.) |
|
sdo_numtiles |
Specifies the number of variable-sized tiles to be used in tessellating an object. (Quadtree indexes only.) |
|
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. |
|
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. |
|
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 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.) |
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.
See the Usage Notes for the CREATE INDEX statement for usage information about many of the available parameters.
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');
ALTER INDEX [schema.]index REBUILD
[PARAMETERS (`rebuild_params [physical_storage_params]' ) ]
Rebuilds a spatial index.
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.
See also the Usage Notes for the CREATE INDEX statement for usage information about many of the available parameters.
The following example rebuilds OLDINDEX with an SDO_LEVEL value of 12.
ALTER INDEX oldindex REBUILD PARAMETERS('sdo_level=12');
ALTER INDEX [schema.]index RENAME TO <new_index_name>
Alters the name of a spatial index.
|
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;
CREATE INDEX [schema.]<index_name> ON [schema.]<tableName> (column)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
[PARAMETERS `index_params [physical_storage_params]']);
Creates a spatial index on a column of type MDSYS.SDO_GEOMETRY.
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 non-zero 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 1.7.1.1. See also the considerations and recommendations for certain R-tree index parameters in Section 3.2.4.
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.
With an R-tree index on linear referencing system (LRS) data, the sdo_indx_dims parameter must be used and must specify the number of dimensions minus one, so as not to index the measure dimension. For example, if the dimensions are X, Y, and M, specify sdo_indx_dims=2 to index only the X and Y dimensions, and not the measure (M) dimension. (The LRS data model, including the measure dimension, is explained in Section E.2.)
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 for quadtree indexing:
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 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 a spatial quadtree index named QTREE.
CREATE INDEX qtree ON POLY_4PT(geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('sdo_numtiles=4 sdo_level=6 sdo_commit_interval=500 tablespace=system initial=10K next=10K pctincrease=10 minextents=10 maxextents=20');
DROP INDEX [schema.]index [FORCE]
Deletes a spatial index.
|
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.
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
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|