Spatial Index-Related Structures

This topic describes the structure of the tables containing the spatial index data and metadata.

Concepts and usage notes for spatial indexing are explained in Indexing of Spatial Data. The spatial index data and metadata are stored in tables that are created and maintained by the Spatial indexing routines. These tables are created in the schema of the owner of the feature (underlying) table that has a spatial index created on a column of type SDO_GEOMETRY.

Spatial index names have the same restrictions and considerations as names for spatial tables and columns and for schemas containing them, as explained in Geometry Metadata Views.

Spatial Index Views

There are two sets of spatial index metadata views for each schema (user): xxx_SDO_INDEX_INFO and xxx_SDO_INDEX_METADATA, where xxx can be USER or ALL. These views are read-only to users; they are created and maintained by the Spatial indexing routines.

xxx_SDO_INDEX_INFO Views

The following views contain basic information about spatial indexes:

The USER_SDO_INDEX_INFO and ALL_SDO_INDEX_INFO views contain the same columns, as shown Table: Columns in the xxx_SDO_INDEX_INFO Views, except that the USER_SDO_INDEX_INFO view does not contain the SDO_INDEX_OWNER column. (The columns are listed in their order in the view definition.)

Table: Columns in the xxx_SDO_INDEX_INFO Views

Column Name Data Type Purpose
SDO_INDEX_OWNER VARCHAR2 Owner of the index (ALL_SDO_INDEX_INFO view only).
INDEX_NAME VARCHAR2 Name of the index.
TABLE_OWNER VARCHAR2 Name of the owner of the table containing the column on which this index is built.
TABLE_NAME VARCHAR2 Name of the table containing the column on which this index is built.
COLUMN_NAME VARCHAR2 Name of the column on which this index is built.
SDO_INDEX_TYPE VARCHAR2 Contains RTREE (for an R-tree index).
SDO_INDEX_TABLE VARCHAR2 Name of the spatial index table (described in Spatial Index Table Definition).
SDO_INDEX_STATUS VARCHAR2 (Reserved for Oracle use.)

xxx_SDO_INDEX_METADATA Views

The following views contain detailed information about spatial index metadata:

The USER_SDO_INDEX_METADATA and ALL_SDO_INDEX_METADATA views contain the same columns, as shown Table: Columns in the xxx_SDO_INDEX_METADATA Views. (The columns are listed in their order in the view definition.)

Table: Columns in the xxx_SDO_INDEX_METADATA Views

Column Name Data Type Purpose
SDO_INDEX_OWNER VARCHAR2 Owner of the index.
SDO_INDEX_TYPE VARCHAR2 Contains RTREE (for an R-tree index).
SDO_LEVEL NUMBER (No longer relevant; applies to a desupported feature.)
SDO_NUMTILES NUMBER (No longer relevant; applies to a desupported feature.)
SDO_MAXLEVEL NUMBER (No longer relevant; applies to a desupported feature.)
SDO_COMMIT_INTERVAL NUMBER (No longer relevant; applies to a desupported feature.)
SDO_INDEX_TABLE VARCHAR2 Name of the spatial index table (described in Spatial Index Table Definition).
SDO_INDEX_NAME VARCHAR2 Name of the index.
SDO_INDEX_PRIMARY NUMBER Indicates if this is a primary or secondary index. 1 = primary, 2 = secondary.
SDO_TSNAME VARCHAR2 Schema name of the SDO_INDEX_TABLE.
SDO_COLUMN_NAME VARCHAR2 Name of the column on which this index is built.
SDO_RTREE_HEIGHT NUMBER Height of the R-tree.
SDO_RTREE_NUM_NODES NUMBER Number of nodes in the R-tree.
SDO_RTREE_DIMENSIONALITY NUMBER Number of dimensions used internally by Spatial. This may be different from the number of dimensions indexed, which is controlled by the sdo_indx_dims keyword in the CREATE INDEX or ALTER INDEX statement, and which is stored in the SDO_INDEX_DIMS column in this view. For example, for an index on geodetic data, the SDO_RTREE_DIMENSIONALITY value is 3, but the SDO_INDEX_DIMS value is 2.
SDO_RTREE_FANOUT NUMBER Maximum number of children in each R-tree node.
SDO_RTREE_ROOT VARCHAR2 Rowid corresponding to the root node of the R-tree in the index table.
SDO_RTREE_SEQ_NAME VARCHAR2 Sequence name associated with the R-tree.
SDO_FIXED_META RAW If applicable, this column contains the metadata portion of the SDO_GROUPCODE or SDO_CODE for a fixed-level index.
SDO_TABLESPACE VARCHAR2 Same as in the SQL CREATE TABLE statement. Tablespace in which to create the SDOINDEX table.
SDO_INITIAL_EXTENT VARCHAR2 Same as in the SQL CREATE TABLE statement.
SDO_NEXT_EXTENT VARCHAR2 Same as in the SQL CREATE TABLE statement.
SDO_PCTINCREASE NUMBER Same as in the SQL CREATE TABLE statement.
SDO_MIN_EXTENTS NUMBER Same as in the SQL CREATE TABLE statement.
SDO_MAX_EXTENTS NUMBER Same as in the SQL CREATE TABLE statement.
SDO_INDEX_DIMS NUMBER Number of dimensions of the geometry objects in the column on which this index is built, as determined by the value of the sdo_indx_dims keyword in the CREATE INDEX or ALTER INDEX statement.
SDO_LAYER_GTYPE VARCHAR2 Contains DEFAULT if the layer can contain both point and polygon data, or a value from the Geometry Type column of Table: Valid SDO_GTYPE Values in SDO_GTYPE.
SDO_RTREE_PCTFREE NUMBER Minimum percentage of slots in each index tree node to be left empty when an R-tree index is created.
SDO_INDEX_PARTITION VARCHAR2 For a partitioned index, name of the index partition.
SDO_PARTITIONED NUMBER Contains 0 if the index is not partitioned or 1 if the index is partitioned.
SDO_RTREE_QUALITY NUMBER Quality score for an index. See the information about R-tree quality in R-Tree Quality.
SDO_INDEX_VERSION NUMBER Internal version number of the index.
SDO_INDEX_GEODETIC VARCHAR2 Contains TRUE if the index is geodetic and FALSE if the index is not geodetic.
SDO_INDEX_STATUS VARCHAR2 (Reserved for Oracle use.)
SDO_NL_INDEX_TABLE VARCHAR2 Name of a separate index table (with a name in the form MDNT_…$) for nonleaf nodes of the index. For more information, see the description of the sdo_non_leaf_tbl parameter for the CREATE INDEX statement in SQL Statements for Indexing Spatial Data.
SDO_DML_BATCH_SIZE NUMBER Number of index updates to be processed in each batch of updates after a commit operation. For more information, see the description of the sdo_dml_batch_size parameter for the CREATE INDEX statement in SQL Statements for Indexing Spatial Data.
SDO_RTREE_EXT_XPND NUMBER (Reserved for future use.)
SDO_NUM_ROWS NUMBER Number of rows (with non-null geometries) in the base spatial table (table containing the column on which this index is built).
SDO_NUM_BLKS NUMBER Number of blocks in the spatial index table (SDO_INDEX_TABLE),
SDO_ROOT_MBR SDO_GEOMETRY Minimum bounding rectangle of the maximum extent of the spatial layer. This is greater than or equal to the MBR of the current extent, and is reset to reflect the current extent when the index is rebuilt.

Spatial Index Table Definition

For an R-tree index, a spatial index table (each SDO_INDEX_TABLE entry as described in xxx_SDO_INDEX_METADATA Views) contains the columns shown in Table: Columns in an R-Tree Spatial Index Data Table.

Table: Columns in an R-Tree Spatial Index Data Table

Column Name Data Type Purpose
NODE_ID NUMBER Unique ID number for this node of the tree.
NODE_LEVEL NUMBER Level of the node in the tree. Leaf nodes (nodes whose entries point to data items in the base table) are at level 1, their parent nodes are at level 2, and so on.
INFO BLOB Other information in a node. Includes an array of <child_mbr, child_rowid> pairs (maximum of fanout value, or number of children for such pairs in each R-tree node), where child_rowid is the rowid of a child node, or the rowid of a data item from the base table.

R-Tree Index Sequence Object

Each R-tree spatial index table has an associated sequence object (SDO_RTREE_SEQ_NAME in the USER_SDO_INDEX_METADATA view, described in xxx_SDO_INDEX_METADATA Views). The sequence is used to ensure that simultaneous updates can be performed to the index by multiple concurrent users.

The sequence name is the index table name with the letter S replacing the letter T before the underscore (for example, the sequence object MDRS_5C01$ is associated with the index table MDRT_5C01$).