Oracle Spatial User's Guide and Reference Release 8.1.7 Part Number A8533701 

The objectrelational implementation of Oracle Spatial consists of a set of object data types, an index method type, and operators on these types. A geometry is stored as an object, in a single row, in a column of type SDO_GEOMETRY. Spatial index creation and maintenance is done using basic DDL (CREATE, ALTER, DROP) and DML (INSERT, UPDATE, DELETE) statements.
This section presents a simple example of creating a spatial table, inserting data, creating the spatial index, and performing spatial queries. It refers to concepts that were explained in Chapter 1 and that will be explained in other sections of this chapter.
The scenario is a soft drink manufacturer that has identified geographical areas of marketing interest for several products (colas). The colas could be those produced by the company or by its competitors, or some combination. Each area of interest could represent any userdefined criterion: for example, an area where that cola has the majority market share, or where the cola is under competitive pressure, or where the cola is believed to have significant growth potential. Each area could be a neighborhood in a city, or a part of a state, province, or country.
Figure 21 shows the areas of interest for four colas.
Example 21 performs the following operations:
Many concepts and techniques in Example 21 are explained in detail in other sections of this chapter.
 Create a table for cola (soft drink) markets in a  given geography (such as city or state).  Each row will be an area of interest for a specific  cola (for example, where the cola is most preferred  by residents, where the manufacturer believes the  cola has growth potential, and so on). CREATE TABLE cola_markets ( mkt_id NUMBER PRIMARY KEY, name VARCHAR2(32), shape MDSYS.SDO_GEOMETRY);  The next INSERT statement creates an area of interest for  Cola A. This area happens to be a rectangle.  The area could represent any userdefined criterion: for  example, where Cola A is the preferred drink, where  Cola A is under competitive pressure, where Cola A  has strong growth potential, and so on. INSERT INTO cola_markets VALUES( 1, 'cola_a', MDSYS.SDO_GEOMETRY( 2003,  2dimensional polygon NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),  one rectangle (1003 = exterior) MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)  only 2 points needed to  define rectangle (lower left and upper right) ) );  The next two INSERT statements create areas of interest for  Cola B and Cola C. These areas are simple polygons (but not  rectangles). INSERT INTO cola_markets VALUES( 2, 'cola_b', MDSYS.SDO_GEOMETRY( 2003,  2dimensional polygon NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),  one polygon (exterior polygon ring) MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1) ) ); INSERT INTO cola_markets VALUES( 3, 'cola_c', MDSYS.SDO_GEOMETRY( 2003,  2dimensional polygon NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),  one polygon (exterior polygon ring) MDSYS.SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3) ) );  Now insert an area of interest for Cola D. This is a  circle with a radius of 2. It is completely outside the  first three areas of interest. INSERT INTO cola_markets VALUES( 4, 'cola_d', MDSYS.SDO_GEOMETRY( 2003,  2dimensional polygon NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4),  one circle MDSYS.SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11) ) );   UPDATE METADATA VIEW    Update the USER_SDO_GEOM_METADATA view. This is required  before the Spatial index can be created. Do this only once for each  layer (that is, tablecolumn combination; here: COLA_MARKETS and SHAPE). INSERT INTO USER_SDO_GEOM_METADATA VALUES ( 'cola_markets', 'shape', MDSYS.SDO_DIM_ARRAY(  20X20 grid, virtually zero tolerance MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005), MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005) ), NULL  SRID );   CREATE THE SPATIAL INDEX   CREATE INDEX cola_spatial_idx ON cola_markets(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('SDO_LEVEL = 8');   PERFORM SOME SPATIAL QUERIES    Return the topological intersection of two geometries. SELECT SDO_GEOM.SDO_INTERSECTION(c_a.shape, m.diminfo, c_c.shape, m.diminfo) FROM cola_markets c_a, cola_markets c_c, user_sdo_geom_metadata m WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' AND c_a.name = 'cola_a' AND c_c.name = 'cola_c';  Do two geometries have any spatial relationship? SELECT SDO_GEOM.RELATE(c_b.shape, m.diminfo, 'anyinteract', c_d.shape, m.diminfo) FROM cola_markets c_b, cola_markets c_d, user_sdo_geom_metadata m WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' AND c_b.name = 'cola_b' AND c_d.name = 'cola_d';  Return the areas of all cola markets. SELECT c.name, SDO_GEOM.SDO_AREA(c.shape, m.diminfo) FROM cola_markets c, user_sdo_geom_metadata m WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE';  Return the area of just cola_a. SELECT c.name, SDO_GEOM.SDO_AREA(c.shape, m.diminfo) FROM cola_markets c, user_sdo_geom_metadata m WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' AND c.name = 'cola_a';  Return the distance between two geometries. SELECT SDO_GEOM.SDO_DISTANCE(c_b.shape, m.diminfo, c_d.shape, m.diminfo) FROM cola_markets c_b, cola_markets c_d, user_sdo_geom_metadata m WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' AND c_b.name = 'cola_b' AND c_d.name = 'cola_d';  Is a geometry valid? SELECT c.name, SDO_GEOM.VALIDATE_GEOMETRY(c.shape, m.diminfo) FROM cola_markets c, user_sdo_geom_metadata m WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE' AND c.name = 'cola_c';  Is a layer valid? (First, create the results table.) CREATE TABLE validation_results (mkt_id number, result varchar2(10)); EXECUTE SDO_GEOM.VALIDATE_LAYER('COLA_MARKETS', 'SHAPE', 'MKT_ID', 'VALIDATION_RESULTS'); SELECT * from validation_results;
In the Spatial objectrelational model, the geometric description of a spatial object is stored in a single row, in a single column of object type SDO_GEOMETRY in a userdefined table. Any table that has a column of type SDO_GEOMETRY must have another column, or set of columns, that defines a unique primary key for that table. Tables of this sort are sometimes referred to as geometry tables.
Oracle Spatial defines the object type SDO_GEOMETRY as:
CREATE TYPE sdo_geometry AS OBJECT ( SDO_GTYPE NUMBER, SDO_SRID NUMBER, SDO_POINT SDO_POINT_TYPE, SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY, SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY);
The sections that follow describe the semantics of each SDO_GEOMETRY attribute, and then describe some usage considerations (Section 2.2.6).
SDO_GTYPE indicates the type of the geometry. Valid geometry types correspond to those specified in the Geometry Object Model for the OGIS Simple Features for SQL specification (with the exception of Surfaces.) The numeric values differ from those given in the OGIS specification, but there is a direct correspondence between the names and semantics where applicable. Table 21 shows the valid SDO_GTYPE values.
Value  Geometry Type  Description 

d000 
UNKNOWN_GEOMETRY 
Spatial ignores this geometry. 
d001 
POINT 
Geometry contains one point. 
d002 
LINESTRING 
Geometry contains one line string. 
d003 
POLYGON 
Geometry contains one polygon with or without holes.^{Foot 1} 
d004 
COLLECTION 
Geometry is a heterogeneous collection of elements.^{Foot 2} 
d005 
MULTIPOINT 
Geometry has multiple points. 
d006 
MULTILINESTRING 
Geometry has multiple line strings. 
d007 
MULTIPOLYGON 
Geometry has multiple, disjoint polygons (more than one exterior boundary). 
^{Foot 1}
For a polygon with holes, enter the exterior boundary first, followed by any interior boundaries. ^{Foot 2} All polygons in the collection must be disjoint. 
The d in the Value column of Table 21 is the number of dimensions: 2, 3, or 4. For example, a value of 2003 indicates a 2dimensional polygon.
Note: The prerelease 8.1.6 format of a 1digit value is still supported. If a 1digit value is used, however, Oracle Spatial determines the number of dimensions and stores the appropriate 4digit value in the DIMINFO column of the metadata views described in Section 2.4. 
The number of dimensions reflects the number of ordinates used to represent each vertex (for example, X,Y for 2dimensional objects). Points and lines are considered 2dimensional objects. (However, see Section E.2 for dimension information about LRS points.)
In any given layer (column), all geometries must have the same number of dimensions. For example, you cannot mix 2dimensional and 3dimensional data in the same layer.
Values d008d099 are reserved for future use.
SDO_SRID can be used to identify a coordinate system (spatial reference system) to be associated with the geometry. If SDO_SRID is null, no coordinate system is associated with the geometry. If SDO_SRID is not null, it must contain a value from the SRID column of the MDSYS.CS_SRS table (described in Section D.3.1), and this value must be inserted into the SRID column of the USER_SDO_GEOM_METADATA view (described in Section 2.4).
All geometries in a geometry column must have the same SDO_SRID value.
For information about coordinate systems, see Appendix D.
SDO_POINT is defined using an object type with attributes X, Y, and Z, all of type NUMBER. If the SDO_ELEM_INFO and SDO_ORDINATES arrays are both null, and the SDO_POINT attribute is nonnull, then the X and Y values are considered to be the coordinates for a point geometry. Otherwise the SDO_POINT attribute is ignored by Spatial. You should store point geometries in the SDO_POINT attribute for optimal storage; and if you have only point geometries in a layer, it is strongly recommended that you store the point geometries in the SDO_POINT attribute.
Note: Do not use the SDO_POINT attribute in defining a linear referencing system (LRS) point. For information about LRS, see Appendix E. 
SDO_ELEM_INFO is defined using a varying length array of numbers. This attribute lets you know how to interpret the ordinates stored in the SDO_ORDINATES attribute (described in Section 2.2.5).
Each triplet set of numbers is interpreted as follows:
Note: For polygon ring elements in a single geometry, you can use either 1digit or 4digit SDO_ETYPE values for all elements; however, you cannot mix 1digit and 4digit SDO_ETYPE values. 
SDO_ETYPE values 1, 2, and 3 are considered simple elements. They are defined by a single triplet entry in the SDO_ELEM_INFO array. Moreover, the following are considered variants of type 3, with the first digit indicating exterior (1) or interior (2):
1003: exterior polygon ring (must be specified in counterclockwise order)
2003: interior polygon ring (must be specified in clockwise order)
You should specify an SDO_ETYPE value of 3 if you do not know if the simple polygon is exterior or interior; otherwise, you should specify 1003 or 2003.
SDO_ETYPE values 4 and 5 are considered compound elements. They contain at least one header triplet with a series of triplet values that belong to the compound element. Moreover, the following are considered variants of type 5, with the first digit indicating exterior (1) or interior (2):
1005: exterior polygon ring (must be specified in counterclockwise order)
2005: interior polygon ring (must be specified in clockwise order)
You should specify an SDO_ETYPE value of 5 if you do not know if the compound polygon is exterior or interior; otherwise, you should specify 1005 or 2005.
The elements of a compound element are contiguous. The last point of a subelement in a compound element is the first point of the next subelement. The point is not repeated.
If SDO_ETYPE is a compound element (4 or 5), this field specifies how many subsequent triplet values are part of the element.
If the SDO_ETYPE is not a compound element (1, 2, or 3), the interpretation attribute determines how the sequence of ordinates for this element is interpreted. For example, a line string or polygon boundary may be made up of a sequence of connected straight line segments or circular arcs.
Descriptions of valid SDO_ETYPE and SDO_INTERPRETATION value pairs are given in Table 22.
If a geometry consists of more than one element, then the last ordinate for an element is always one less than the starting offset for the next element. The last element in the geometry is described by the ordinates from its starting offset to the end of the SDO_ORDINATES varying length array.
For compound elements (SDO_ETYPE values 4 and 5), a set of n triplets (one per subelement) is used to describe the element. It is important to remember that subelements of a compound element are contiguous. The last point of a subelement is the first point of the next subelement. For subelements 1 through n1, the end point of one subelement is the same as the starting point of the next subelement. The starting point for subelements 2...n2 is the same as the end point of subelement 1...n1. The last ordinate of subelement n is either the starting offset minus 1 of the next element in the geometry, or the last ordinate in the SDO_ORDINATES varying length array.
The current size of a varying length array can be determined by using the function varray_variable.Count in PL/SQL or OCIColSize in the Oracle Call Interface (OCI).
The semantics of each SDO_ETYPE element and the relationship between the SDO_ELEM_INFO and SDO_ORDINATES varying length arrays for each of these SDO_ETYPE elements are given in Table 22.
SDO_ETYPE  SDO_INTERPRETATION  Meaning 

0 
0 
Unsupported element type. Ignored by the Spatial functions and procedures. 
1 
1 
Point type. 
1 
n > 1 
Point cluster with n points. 
2 
1 
Line string whose vertices are connected by straight line segments. 
2 
2 
Line string made up of a connected sequence of circular arcs. Each circular arc is described using three coordinates: the arc's starting point, any point on the arc, and the arc's end point. The coordinates for a point designating the end of one arc and the start of the next arc are not repeated. For example, five coordinates are used to describe a line string made up of two connected circular arcs. Points 1, 2, and 3 define the first arc, and points 3, 4, and 5 define the second arc, where point 3 is only stored once. 
3 
1 
Simple polygon whose vertices are connected by straight line segments. Note that you must specify a point for each vertex, and the last point specified must be identical to the first (to close the polygon). For example, for a 4sided polygon, specify 5 points, with point 5 the same as point 1. 
3 
2 
Polygon made up of a connected sequence of circular arcs that closes on itself. The end point of the last arc is the same as the start point of the first arc. Each circular arc is described using three coordinates: the arc's start point, any point on the arc, and the arc's end point. The coordinates for a point designating the end of one arc and the start of the next arc are not repeated. For example, five coordinates are used to describe a polygon made up of two connected circular arcs. Points 1, 2, and 3 define the first arc, and points 3, 4, and 5 define the second arc. The coordinates for points 1 and 5 must be the same, and point 3 is not repeated. 
3 
3 
Rectangle type. A bounding rectangle such that only two points, the lowerleft and the upperright, are required to describe it. 
3 
4 
Circle type. Described by three points, all on the circumference of the circle. 
4 
n > 1 
Line string with some vertices connected by straight line segments and some by circular arcs. The value, n, in the Interpretation column specifies the number of contiguous subelements that make up the line string. The next n triplets in the SDO_ELEM_INFO array describe each of these subelements. The subelements can only be of SDO_ETYPE 2. The last point of a subelement is the first point of the next subelement, and must not be repeated. See Section 2.3 and Figure 24 for an example of a geometry using this type. 
5 
n > 1 
Compound polygon with some vertices connected by straight line segments and some by circular arcs. The value, n, in the Interpretation column specifies the number of contiguous subelements that make up the polygon. The next n triplets in the SDO_ELEM_INFO array describe each of these subelements. The subelements can only be of SDO_ETYPE 2. The end point of a subelement is the start point of the next subelement, and it must not be repeated. The start and end points of the polygon must be the same. See Section 2.3.4 and Figure 25 for an example of a geometry using this type. 
SDO_ORDINATES is defined using a varying length array (1048576) of NUMBER type that stores the coordinate values that make up the boundary of a spatial object. This array must always be used in conjunction with the SDO_ELEM_INFO varying length array. The values in the array are ordered by dimension. For example, a polygon whose boundary has four 2dimensional points is stored as {X1, Y1, X2, Y2, X3, Y3, X4, Y4, X1, Y1}. If the points are 3dimensional, then they are stored as {X1, Y1, Z1, X2, Y2, Z2, X3, Y3, Z3, X4, Y4, Z4, X1, Y1, Z1}. Spatial index creation, operators, and functions ignore the Z values because this release of the product supports only 2dimensional spatial objects. The number of dimensions associated with each point is stored as metadata in the xxx_SDO_GEOM_METADATA views, described in Section 2.4.
The values in the SDO_ORDINATES array must all be valid and nonnull. There are no special values used to delimit elements in a multielement geometry. The start and end points for the sequence describing a specific element are determined by the STARTING_OFFSET values for that element and the next element in the SDO_ELEM_INFO array as explained previously. The offset values start at 1. SDO_ORDINATES(1) is the first ordinate of the first point of the first element.
You should use the SDO_GTYPE values as shown in Table 21; however, Spatial does not check or enforce all geometry consistency constraints. Spatial does check the following:
The SDO_GEOM.VALIDATE_GEOMETRY function can be used to evaluate the consistency of a single geometry object or all the instances of SDO_GEOMETRY in a specified feature table.
This section contains examples of several geometry types.
Figure 22 illustrates a rectangle.
In the SDO_GEOMETRY definition of the geometry illustrated in Figure 22:
Figure 23 illustrates a polygon consisting of two elements: an exterior polygon ring and an interior polygon ring. The inner element in this example is treated as a void (a hole).
In the SDO_GEOMETRY definition of the geometry illustrated in Figure 23:
1003 indicates that the element is an exterior polygon ring; 2003 indicates that the element is an interior polygon ring.
19 indicates that the second element (the interior polygon ring) ordinate specification starts at the 19th number in the SDO_ORDINATES array (that is, 12, meaning that the first point is 12,15).
An example of such a "polygon with a hole" might be a land mass (such as a country or an island) with a lake inside it. Of course, an actual land mass might have many such interior polygons: each one would require a triplet element in SDO_ELEM_INFO, plus the necessary ordinate specification.
Exterior and interior rings cannot be nested. For example, if a country has a lake and there is an island in the lake (and perhaps a lake on the island), a separate polygon must be defined for the island; the island cannot be defined as an interior polygon ring within the interior polygon ring of the lake.
In a multipolygon (polygon collection), rings must be grouped by polygon, and the first ring of each polygon must be the exterior ring. For example, consider a polygon collection that contains two polygons (A and B):
The elements in SDO_ELEM_INFO and SDO_ORDINATES must be in one of the following orders (depending on whether you specify Polygon A or Polygon B first):
Figure 24 illustrates a crescentshaped object represented as a compound line string made up of one straight line segment and one circular arc. Four points are required to represent this shape. Points 1 and 2 describe the straight line segment and points 2, 3, and 4 describe the circular arc. The SDO_ELEM_INFO array contains 3 triplets for this compound line string. These are {(1,4,2), (1,2,1), (3,2,2)}. The SDO_ORDINATES array contains (X1,Y1, X2, Y2, X3, Y3, X4,Y4).
The first triplet indicates that this element is a compound line string made up of two line strings, which are described with the next two triplets.
The second triplet indicates that the line string is made up of straight line segments and that the ordinates for this line string start at offset 1. The end point of this line string is determined by the starting offset of the second line string, 3 in this instance. Assuming the vertices are 2dimensional, the coordinates for the end point of the first line string are at ordinates 3 and 4.
The third triplet indicates that the second line string is made up of circular arcs with ordinates starting at offset 3. The end point of this line string is determined by the starting offset of the next element or the current length of the SDO_ORDINATES array, if this is the last element.
Figure 25 illustrates an ice cream coneshaped object represented as a compound polygon made up of one straight line segment and one circular arc. Five points are required to represent this shape. Points 1, 2, and 3 describe one acute angleshaped line string, and points 3, 4, and 5 describe the circular arc. Points 1 and 5 are the same point. The SDO_ELEM_INFO array contains three triplets for this compound line string. These triplets are {(1,1005,2), (1,2,1), (5,2,2)}.
The first triplet indicates that this element is a compound line string made up of two line strings, which are described using the next two triplets.
The second triplet indicates that the line string is made up of straight line segments and that the ordinates for this line string start at offset 1. The end point of this line string is determined by the starting offset of the second line string, 5 in this instance. Assuming the vertices are 2dimensional, the coordinates for the end point of the first line string are at ordinates 5 and 6.
The third triplet indicates that the second line string is made up of circular arcs with ordinates starting at offset 5. The end point of this line string is determined by the starting offset of the next element or the current length of the SDO_ORDINATES array, if this is the last element.
The geometry metadata describing the dimensions, lower and upper bounds, and tolerance in each dimension is stored in a global table owned by MDSYS (which users should never directly update). Each Spatial user has the following views available in the schema associated with that user:
Spatial users are responsible for populating these views. For each spatial column, you must insert an appropriate row into the USER_SDO_GEOM_METADATA view. Oracle Spatial ensures that the other two views (ALL_SDO_GEOM_METADATA and DBA_SDO_GEOM_METADATA) are also updated to reflect the rows that you insert into USER_SDO_GEOM_METADATA.
Note: These views were new for release 8.1.6. If you are migrating from an earlier release of Spatial, see Appendix B. 
Each metadata view has the following definition:
( TABLE_NAME VARCHAR2(32), COLUMN_NAME VARCHAR2(32), DIMINFO MDSYS.SDO_DIM_ARRAY, SRID NUMBER );
In addition, the ALL_SDO_GEOM_METADATA and DBA_SDO_GEOM_METADATA views have an OWNER column identifying the schema that owns the table specified in TABLE_NAME.
The TABLE_NAME column contains the name of a feature table, such as ROADS or PARKS, that has a column of type SDO_GEOMETRY.
The COLUMN_NAME column contains the name of the column of type SDO_GEOMETRY. For the tables ROADS and PARKS, this column is called THEGEOMETRY, and therefore the xxx_SDO_GEOM_METADATA views should contain rows with values (ROADS, THEGEOMETRY, SOMEDIMINFO1, NULL) and (PARKS, THEGEOMETRY, SOMEDIMINFO2, NULL).
The DIMINFO column is a varying length array of an object type, ordered by dimension, and has one entry per dimension. The SDO_DIM_ARRAY type is defined as follows:
Create Type SDO_DIM_ARRAY as VARRAY(4) of SDO_DIM_ELEMENT;
The SDO_DIM_ELEMENT type is defined as:
Create Type SDO_DIM_ELEMENT as OBJECT ( SDO_DIMNAME VARCHAR2(64), SDO_LB NUMBER, SDO_UB NUMBER, SDO_TOLERANCE NUMBER);
The SDO_DIM_ARRAY instance is of size n if there are n dimensions. That is, DIMINFO contains 2 SDO_DIM_ELEMENT instances for 2dimensional geometries, 3 instances for 3dimensional geometries, and 4 instances for 4dimensional geometries. Each SDO_DIM_ELEMENT instance in the array must have valid (not null) values for the SDO_LB, SDO_UB, and SDO_TOLERANCE attributes.
Spatial assumes that the varying length array is ordered by dimension, and therefore, in the ROADS and PARKS tables, SomeDimInfo1 is the SDO_DIM_ELEMENT for the first dimension and SomeDimInfo2 is the SDO_DIM_ELEMENT for the second dimension. It is imperative that the DIMINFO varying length array is ordered by dimension in the same way the ordinates for the points in SDO_ORDINATES varying length array are ordered. That is, if the SDO_ORDINATES varying length array contains {X1, Y1, ..., Xn, Yn}, then SomeDimInfo1 must define the X dimension and SomeDimInfo2 must define the Y dimension.
Section 3.1.2 contains examples that show the use of the SDO_GEOMETRY and SDO_DIM_ARRAY types. These examples demonstrate how various geometry objects are represented, and how a feature table and the USER_SDO_GEOM_METADATA view are populated with the data for those objects.
The SRID column should contain either of the following: the SRID value for the coordinate system (see Appendix D) for all geometries in the column, or NULL if no specific coordinate system should be associated with the geometries.
This section describes the structure of the tables containing the spatial index data and metadata. Concepts and usage notes for spatial indexing are explained in Section 1.7. 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.
There are three metadata views per schema (user). These views are readonly to users; they are created and maintained by the Spatial indexing routines.
Note: These views were new for release 8.1.6. If you are migrating from an earlier release of Spatial, see Appendix B. 
The USER_SDO_INDEX_METADATA, ALL_SDO_INDEX_METADATA, and DBA_SDO_INDEX_METADATA views contain the same columns, as shown Table 23. (The columns are listed in their order in the view definition.)
Column Name  Data Type  Purpose 

SDO_INDEX_OWNER 
VARCHAR2 
The owner of the index. 
SDO_INDEX_TYPE 
VARCHAR2 
Contains QTREE (for a quadtree index) or RTREE (for an Rtree index). 
SDO_INDEX_NAME 
VARCHAR2 
The name of the index. 
SDO_INDEX_TABLE 
VARCHAR2 
Name of the spatial index table (described in Section 2.5.2). 
SDO_INDEX_PRIMARY 
NUMBER 
Indicates if this is a primary or secondary index. 1 = primary, 2 = secondary. 
SDO_TSNAME 
VARCHAR2 
The schema name of the SDO_INDEX_TABLE. 
SDO_COLUMN_NAME 
VARCHAR2 
The column name on which this index is built. 
SDO_RTREE_HEIGHT 
NUMBER 
Height of the Rtree (Rtree index). 
SDO_RTREE_NUM_NODES 
NUMBER 
Number of nodes in the Rtree (Rtree index). 
SDO_RTREE_DIMENSIONALITY 
NUMBER 
Number of dimensions indexed (Rtree index). 
SDO_RTREE_FANOUT 
NUMBER 
Maximum number of children in each Rtree node (Rtree index). 
SDO_RTREE_ROOT 
VARCHAR2 
Rowid corresponding to the root node of the Rtree in the index table (Rtree index). 
SDO_RTREE_SEQ_NAME 
VARCHAR2 
Sequence name associated with the Rtree (Rtree index). 
SDO_LEVEL 
NUMBER 
The fixed tiling level at which to tile all objects in the geometry column (quadtree index). 
SDO_NUMTILES 
NUMBER 
Suggested number of tiles per object that should be used to approximate the shape (quadtree index). 
SDO_MAXLEVEL 
NUMBER 
The maximum level for any tile for any object (quadtree index). It will always be greater than the SDO_LEVEL value. 
SDO_COMMIT_INTERVAL 
NUMBER 
The number of geometries (rows) to process, during index creation, before committing the insertion of spatial index entries into the SDOINDEX table. See Section A.1.4 for more information about SDO_COMMIT_INTERVAL. 
SDO_FIXED_META 
RAW 
If applicable, this column contains the metadata portion of the SDO_GROUPCODE or SDO_CODE for a fixedlevel index. 
SDO_TABLESPACE 
VARCHAR2 
Same as in the SQL CREATE TABLE statement. Tablespace in which to create the SDOINDEX table. 
SDO_INITIAL_EXTENT 
NUMBER 
Same as in SQL CREATE TABLE statement. 
SDO_NEXT_EXTENT 
NUMBER 
Same as in SQL CREATE TABLE statement. 
SDO_PCTINCREASE 
NUMBER 
Same as in SQL CREATE TABLE statement. 
SDO_MIN_EXTENTS 
NUMBER 
Same as in SQL CREATE TABLE statement. 
SDO_MAX_EXTENTS 
NUMBER 
Same as in SQL CREATE TABLE statement. 
Each quadtree spatial index table (each SDO_INDEX_TABLE entry as described in Table 23 in Section 2.5.1) contains the columns shown in Table 24.
The SDO_CODE, SDO_ROWID, and SDO_STATUS columns are always present. The SDO_GROUPCODE column is present only when the selected index type is HYBRID.
Each Rtree spatial index table has an associated sequence object (SDO_RTREE_SEQ_NAME in the USER_SDO_INDEX_METADATA view, described in Table 23 in Section 2.5.1). 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 as a suffix. For example, if the index table name is E1_RT$, the sequence name is E1_RT$S.

Copyright © 19962000, Oracle Corporation. All Rights Reserved. 
