13
Administrative Functions and Procedures

The SDO_ADMIN procedures create and maintain spatial structures in the database, and are used to perform the following tasks:

• Tessellate entries in a geometry table and place them in a spatial index table

• Verify spatial index information

This chapter contains descriptions of the administrative functions and procedures used for working with spatially indexed geometric data. This chapter refers to the relational Spatial model only.

Table 13-1 lists the administrative functions and procedures for working with spatially indexed geometry-based data.

Table 13-1 Administrative Procedures for Spatially Indexed Data

Procedure or Function	Description
SDO_ADMIN.POPULATE_INDEX	Generates a spatial index for the geometry table using either a set number of tiles, or a fixed-size tile.
SDO_ADMIN.POPULATE_INDEX_FIXED	Generate a spatial index using fixed-size tiles. This is a deprecated procedure.
SDO_ADMIN.POPULATE_INDEX_FIXED_POINTS	Generates a spatial index using fixed-size tiles for a layer composed solely of point data.
SDO_ADMIN.SDO_CODE_SIZE	Determines the required sizes for SDO_CODE and SDO_MAXCODE.
SDO_ADMIN.SDO_VERSION	Returns the release number of the installed version of the Spatial option.
SDO_ADMIN.UPDATE_INDEX	Updates the spatial index based on changes to the geometry table.
SDO_ADMIN.UPDATE_INDEX_FIXED	Updates a spatial index with fixed-size tiles. This is a deprecated procedure.
SDO_ADMIN.VERIFY_LAYER	Checks for the existence of geometry and spatial index tables.

---

SDO_ADMIN.POPULATE_INDEX

Purpose

This procedure tessellates a list of geometric objects created by selecting all the entries in the geometry table that do not have corresponding entries in the spatial index table.

This procedure can generate either fixed-size or variable-sized tiles depending on values stored in the <layername>_SDOLAYER table.

Syntax

SDO_ADMIN.POPULATE_INDEX (layername)

Keywords and Parameters

layername

Specifies the name of the data set layer. The layer name is used to construct the names of the geometry and spatial index tables. Data type is VARCHAR2.

Usage Notes

Consider the following when using this procedure:

• The <layername>_SDOINDEX table must be created prior to calling this procedure. Use the SQL CREATE TABLE statement to create the spatial index table.

• For performance reasons, create an index on the SDO_GID column in the
  <layername>_SDOGEOM table before calling this procedure.

• This procedure generates either fixed-size or variable-sized tiles depending on values stored in the <layername>_SDOLAYER table as follows:

  SDO_LEVEL	SDO_NUMTILES	Action
  NULL	NULL	Error.
  >= 1	NULL	Perform fixed-size tiling (recommended for relational model).
  >= 1	>= 1	Perform hybrid indexing. The SDO_LEVEL column defines the partition bucket size. The SDO_NUMTILES column defines the number of tiles to generate per geometry. Note: hybrid indexing is for experimentation purposes only in the relational model..
  NULL	>= 1	Not supported.

• If the <layername>_SDOINDEX table is empty, the procedure selects all the geometries in the geometry table and generates index entries for them. If the index table is not empty, the procedure determines which entries in the geometry table do not have index entries, and generates them.

• SDO_ADMIN.POPULATE_INDEX() behaves similarly to the CREATE INDEX statement in SQL. An implicit commit is executed after the procedure is called.

• SDO_ADMIN.POPULATE_INDEX() operates as a single transaction. To reduce the amount of rollback required to execute this procedure, you can write a routine that loops and calls SDO_ADMIN.UPDATE_INDEX() repeatedly. See Section A.3.1, "cr_spatial_index.sql Script" for more information.

Example 13-1 tessellates all the geometric objects in the LAYER1_SDOGEOM table and adds the generated tiles to the LAYER1_SDOINDEX table.

Example 13-1 Populate an Index

SQL> EXECUTE SDO_ADMIN.POPULATE_INDEX('layer1');
SQL> COMMIT;

Related Topics

• SDO_ADMIN.UPDATE_INDEX( ) procedure

---

SDO_ADMIN.POPULATE_INDEX_FIXED

Purpose

This procedure is provided for compatibility with Spatial Cartridge release 8.0.3 tables, but it has been replaced by enhanced features in the SDO_ADMIN.POPULATE_INDEX() procedure, in order to support schema changes as shown in Section 10.1.

This procedure tessellates a list of geometric objects created by selecting all the entries in the geometry table that do not have corresponding entries in the spatial index table. This procedure can also tessellate all the geometric objects in a geometry table or view and add the tiles to the spatial index table.

Use this procedure to tessellate the geometries into fixed-size tiles.

Syntax

SDO_ADMIN.POPULATE_INDEX_FIXED (layername, tile_size, [synch_flag,] [sdo_tile_flag,]
[sdo_maxcode_flag])

Keywords and Parameters

layername	Specifies the name of the data set layer. The layer name is used to construct the name of the geometry and spatial index tables. Data type is VARCHAR2.
tile_size	Specifies the number of tessellations required to achieve the desired tile size (see the Usage Notes). Data type is INTEGER.
synch_flag	Specifies whether to tessellate every geometric object in the geometry table, or only those that do not have corresponding entries in the spatial index table. If TRUE, only those geometric objects in the geometry table that do not have any corresponding tiles in the spatial index table are tessellated. If FALSE, all the geometric objects in the geometry table are tessellated and new tiles are simply added to the spatial index table. Default value is TRUE. Data type is BOOLEAN.
sdo_tile_flag	For internal use only. Not supported in this release. Default value is FALSE.
sdo_maxcode_flag	Specifies whether or not the SDO_MAXCODE column is populated. If TRUE, SDO_MAXCODE is populated. If FALSE, the column is not populated. Set this flag to FALSE for the recommended fixed-size tiling. Default value is TRUE. Data type is BOOLEAN.

Usage Notes

Consider the following when using this procedure:

• The SQL CREATE TABLE statement is used to create the spatial index table, <layername>_SDOINDEX, prior to calling this procedure.

• The layer is tessellated into equal-sized tiles based on the number passed in the tile_size parameter. The value of tile_size specifies how many times to tessellate the layer. See Section 11.3.2, "Spatial Indexing with Fixed-Size Tiles".

• For performance reasons, set the synch_flag to FALSE when the spatial index table contains zero rows.

• For performance reasons, create an index on the SDO_GID column in the
  <layername>_SDOGEOM table before calling this procedure.

• SDO_ADMIN.POPULATE_INDEX_FIXED() behaves similarly to the CREATE INDEX statement in SQL. An implicit commit is executed after the procedure is called.

• SDO_ADMIN.POPULATE_INDEX_FIXED() operates as a single transaction. To reduce the amount of rollback required to execute this procedure, you can write a routine that loops and calls SDO_ADMIN.UPDATE_INDEX_FIXED() repeatedly. See Section A.3.1, "cr_spatial_index.sql Script" for more information.

Example 13-2 tessellates all the geometric objects in the LAYER1_SDOGEOM table using fixed-size tiles, and adds the generated tiles to the LAYER1_SDOINDEX table.

Example 13-2 Populate an Index with Fixed-Size Tiles

SQL> EXECUTE SDO_ADMIN.POPULATE_INDEX_FIXED('layer1',4,FALSE,FALSE,FALSE);

Related Topics

• SDO_ADMIN.UPDATE_INDEX_FIXED( ) procedure

• SDO_TUNE.ESTIMATE_TILING_LEVEL( ) function

---

SDO_ADMIN.POPULATE_INDEX_FIXED_POINTS

Purpose

This procedure builds an index with fixed-size tiles for a geometry layer consisting solely of point data. Because a point is indexed using a single tile, special optimizations are possible.

Syntax

SDO_ADMIN.POPULATE_INDEX_FIXED_POINTS (layername, sdo_tile_flag, commit_count)

Keywords and Parameters

layername	Specifies the name of the data set layer. Data type is VARCHAR2.
sdo_tile_flag	Specifies whether or not to generate the SDO_TILE column. Default value is FALSE. Data type is BOOLEAN.
commit_count	Specifies how many points to index before updating and committing the data. Default value is 50. Data type is NUMBER.

Usage Notes

Consider the following when using this procedure:

• The <layername>_SDOLAYER, <layername>_SDOGEOM, and <layername>_SDODIM tables must be populated prior to calling this procedure.

• The <layername>_SDOINDEX table must be created prior to calling this procedure. Use the SQL CREATE TABLE statement to create the spatial index table.

• For performance reasons, create an index on the SDO_GID column in the
  <layername>_SDOGEOM table before calling this procedure.

• If the <layername>_SDOINDEX table is empty, the procedure selects all the geometries in the geometry table and generates index entries for them. If the index table is not empty, the procedure determines which entries in the geometry table do not have index entries, and generates them.

Example 13-3 tessellates all the points in the LAYER1_SDOGEOM table and adds the generated tiles to the LAYER1_SDOINDEX table. This example commits after every 100 points.

Example 13-3 Populate an Index with Fixed-Size Tiles Based on Point Data

SQL> EXECUTE SDO_ADMIN.POPULATE_INDEX_FIXED_POINTS('layer1', FALSE, 100 );

Related Topics

• SDO_ADMIN.UPDATE_INDEX( ) procedure

---

SDO_ADMIN.SDO_CODE_SIZE

Purpose

This function determines the size that the SDO_MAXCODE column should be in the <layername>_SDOINDEX table.

Syntax

SDO_ADMIN.SDO_CODE_SIZE (layername)

Keywords and Parameters

layername

Specifies the name of the data set layer. Data type is VARCHAR2.

Returns

This function returns the required size in bytes for the SDO_MAXCODE column.
Data type is INTEGER.

Usage Notes

The SDO_CODE column is used to store the bit-interleaved cell ID of a tile that covers a geometry. The SDO_MAXCODE column is SDO_CODE padded out one place farther than the longest allowable code name for the index. Both columns are defined as RAW data types, with a maximum of 255 bytes. Use the SDO_ADMIN.
SDO_CODE_SIZE() function to fine-tune the size of the column.

Always declare the SDO_CODE column to raw(255).

Related Topics

None.

---

SDO_ADMIN.SDO_VERSION

Purpose

This function returns the current installed version of Spatial.

Syntax

SDO_ADMIN.SDO_VERSION

Keywords and Parameters

None.

Returns

This function returns a string describing the version of Spatial installed on the local system.
Data type is VARCHAR2.

Usage Notes

The following version strings can be returned by this procedure:

8.0.5.0.0

8.1.0.0.0

8.1.3.0.0

8.1.5.0.0

This information is useful in when migrating data between systems, or when upgrading. See Appendix B for more information about migration.

Related Topics

None.

---

SDO_ADMIN.UPDATE_INDEX

Purpose

This procedure tessellates a single geometric object in a geometry table or view and adds the tiles to the spatial index table. If the object already exists and has index entries, those entries are deleted and replaced by the newly generated tiles.

Syntax

SDO_ADMIN.UPDATE_INDEX (layername, GID)

Keywords and Parameters

layername	Specifies the name of the data set layer. The layer name is used to construct the name of the geometry table. Data type is VARCHAR2.
GID	Specifies the geometric object identifier. Data type is NUMBER.

Usage Notes

Considert the following when using this procedure:

• The <layername>_SDOINDEX table must exist prior to calling this procedure. Use the SQL CREATE TABLE statement to create the spatial index table.

• For performance reasons, create an index on the SDO_GID column in the
  <layername>_SDOGEOM table before calling this procedure.

• The values of the SDO_LEVEL and SDO_NUMTILES columns must be set in the <layername>_SDOLAYER table before calling this procedure. This procedure generates either fixed-size or hybrid tiles depending on values stored in the <layername>_SDOLAYER table as follows:

  SDO_LEVEL	SDO_NUMTILES	Action
  NULL	NULL	Error.
  >= 1	NULL	Perform indexing with fixed-size tiles (recommended for the relational model).
  >= 1	>= 1	Perform hybrid indexing. The SDO_LEVEL column defines the partition bucket size. The SDO_NUMTILES column defines the number of tiles to generate per geometry. Note: hybrid indexing is for experimentation purposes only in the relational model.
  NULL	>= 1	Not supported.

• SDO_ADMIN.UPDATE_INDEX() does not perform an implicit commit after it executes and therefore the transaction can be rolled back.

Example 13-4 tessellates the polygon for geometry 25 and adds the generated tiles to the LAYER1_SDOINDEX table.

Example 13-4 Update an Index

SQL> EXECUTE SDO_ADMIN.UPDATE_INDEX('layer1', 25);
SQL> COMMIT;

Related Topics

• SDO_ADMIN.POPULATE_INDEX( ) procedure

---

SDO_ADMIN.UPDATE_INDEX_FIXED

Purpose

This procedure is provided for compatibility with Spatial Cartridge release 8.0.3 tables, but it has been replaced by enhanced features in the SDO_ADMIN.UPDATE_INDEX() procedure to support schema changes as shown in Section 10.1.

This procedure tessellates a single geometric object in a geometry table or view and adds the fixed-sized tiles to the spatial index table. By default, these tiles will replace existing ones for the same geometry; or optionally, existing tiles can be left alone.

Syntax

SDO_ADMIN.UPDATE_INDEX_FIXED (layername, GID, tile_size, [replace_flag,] [sdo_tile_flag] [sdo_maxcode_flag])

Keywords and Parameters

layername	Specifies the name of the data set layer. The layer name is used to construct the name of the geometry table. Data type is VARCHAR2.
GID	Specifies the geometric object identifier. Data type is NUMBER.
tile_size	Specifies the number of tessellations required to achieve the desired fixed-size tiles. Each tessellation subdivides the tiles from the previous level into four smaller tiles. Data type is INTEGER.
replace_flag	Specifies whether or not to delete tiles for the GID before adding new ones. If TRUE, tiles are deleted prior to inserting new entries into the spatial index table. If FALSE, new tiles are simply added to the spatial index table. Default value is TRUE. Data type is BOOLEAN.
sdo_tile_flag	For internal use only. Not supported in this release. Default value is FALSE. Data type is BOOLEAN.
sdo_maxcode_flag	Specifies whether or not the SDO_MAXCODE column is populated. If TRUE, SDO_MAXCODE is populated. If FALSE, the column is not populated. Set this flag to FALSE for the recommended indexing with fixed-size tiles. Default value is TRUE. Data type is BOOLEAN.

Usage Notes

Consider the following when using this procedure:

• For performance reasons, set the replace_flag to FALSE when the spatial index table contains no entries for the specified GID.

• For performance reasons, create an index on the SDO_GID column in the
  <layername>_SDOGEOM table before calling this procedure.

• SDO_ADMIN.UPDATE_INDEX_FIXED() does not perform an implicit commit after it executes and therefore this transaction can be rolled back.

Example 13-5 tessellates the polygon for geometry 25 and adds the generated tiles to the LAYER1_SDOINDEX table.

Example 13-5 Update an Index with Fixed-Size Tiles

SQL> EXECUTE SDO_ADMIN.UPDATE_INDEX_FIXED ('layer1',25,4,FALSE,FALSE,FALSE);

Related Topics

• SDO_ADMIN.POPULATE_INDEX_FIXED( ) procedure

• SDO_TUNE.ESTIMATE_TILING_LEVEL( ) function

---

SDO_ADMIN.VERIFY_LAYER

Purpose

This procedure checks for the existence of the geometry and spatial index tables.

Syntax

SDO_ADMIN.VERIFY_LAYER (layername,[maxtiles])

Keywords and Parameters

layername	Specifies the name of the data set layer. The layer name is used to construct the name of the geometry and spatial index tables. Data type is VARCHAR2.
maxtiles	For internal use only. Not supported in this release.

Usage Notes

If this procedure does not find the geometry and spatial index tables, it generates the following error: SDO 13113 (Oracle table does not exist.)

Example 13-6 verifies the LAYER1 data set layer.

Example 13-6 Verify a Layer

SQL> EXECUTE SDO_ADMIN.VERIFY_LAYER('layer1');

Related Topics

None.

---