15
Administrative Functions and Procedures for Relational Model
The SDO_ADMIN procedures create and maintain spatial structures in the database, and they 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 15-1 lists the administrative functions and procedures for working with spatially indexed geometry-based data.
Table 15-1 Administrative Procedures for Spatially Indexed Data
SDO_ADMIN.POPULATE_INDEX
Format
SDO_ADMIN.POPULATE_INDEX (layername)
Description
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.
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 before 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:
- 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 operation is executed after the procedure is called.
- SDO_ADMIN.POPULATE_INDEX operates as a single transaction. To reduce the number of rollback operations required to execute this procedure, you can write a routine that loops and calls SDO_ADMIN.UPDATE_INDEX repeatedly. See Section A.2.1 for more information.
Example 15-1 tessellates all the geometric objects in the LAYER1_SDOGEOM table and adds the generated tiles to the LAYER1_SDOINDEX table.
Example 15-1 Populate an Index
SQL> EXECUTE SDO_ADMIN.POPULATE_INDEX('layer1');
SQL> COMMIT;
Related Topics
SDO_ADMIN.POPULATE_INDEX_FIXED
Format
SDO_ADMIN.POPULATE_INDEX_FIXED (layername, tile_size, [synch_flag,] [sdo_tile_flag,]
[sdo_maxcode_flag])
Description
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 12.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.
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 added to the spatial index table.
Data type is BOOLEAN. Default = TRUE. |
|
sdo_tile_flag |
For internal use only. Not supported in this release.
Default = 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.
Data type is BOOLEAN. Default = TRUE. |
Usage Notes
Note:
This procedure is likely to be removed in a future release of Spatial.
|
Consider the following when using this procedure:
- The SQL CREATE TABLE statement is used to create the spatial index table, <layername>_SDOINDEX, before 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 13.3.2.
- 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 operation is executed after the procedure is called.
- SDO_ADMIN.POPULATE_INDEX_FIXED operates as a single transaction. To reduce the number of rollback operations required to execute this procedure, you can write a routine that loops and calls
SDO_ADMIN.UPDATE_INDEX_FIXED repeatedly. See Section A.2.1 for more information.
Example 15-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 15-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
- SDO_TUNE.ESTIMATE_TILING_LEVEL
SDO_ADMIN.POPULATE_INDEX_FIXED_POINTS
Format
SDO_ADMIN.POPULATE_INDEX_FIXED_POINTS (layername, sdo_tile_flag, commit_count)
Description
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.
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.
Data type is BOOLEAN. Default = FALSE. |
|
commit_count |
Specifies how many points to index before updating and committing the data.
Data type is NUMBER. Default = 50. |
Usage Notes
Consider the following when using this procedure:
- The <layername>_SDOLAYER, <layername>_SDOGEOM, and <layername>_SDODIM tables must be populated before calling this procedure.
- The <layername>_SDOINDEX table must be created before 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 15-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 15-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.SDO_CODE_SIZE
Format
SDO_ADMIN.SDO_CODE_SIZE (layername)
Description
Determines the size that the SDO_MAXCODE column should be in the <layername>_SDOINDEX table.
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
Format
SDO_ADMIN.SDO_VERSION
Description
Returns the current installed version of Spatial.
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:
- 8.0.5.0.0
- 8.1.0.0.0
- 8.1.3.0.0
- 8.1.5.0.0
- 8.1.6.0.0
-
This information is useful when migrating data between systems, or when upgrading. See Appendix B for more information about migration.
Related Topics
None.
SDO_ADMIN.UPDATE_INDEX
Format
SDO_ADMIN.UPDATE_INDEX (layername, GID)
Description
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.
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
Consider the following when using this procedure:
- The <layername>_SDOINDEX table must exist before 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_ADMIN.UPDATE_INDEX does not perform an implicit commit operation after it executes; therefore, the transaction can be rolled back.
Example 15-4 tessellates the polygon for geometry 25 and adds the generated tiles to the LAYER1_SDOINDEX table.
Example 15-4 Update an Index
SQL> EXECUTE SDO_ADMIN.UPDATE_INDEX('layer1', 25);
SQL> COMMIT;
Related Topics
SDO_ADMIN.UPDATE_INDEX_FIXED
Format
SDO_ADMIN.UPDATE_INDEX_FIXED (layername, GID, tile_size, [replace_flag,] [sdo_tile_flag] [sdo_maxcode_flag])
Description
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 12.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.
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 before new entries are inserted into the spatial index table. If FALSE, new tiles are added to the spatial index table.
Data type is BOOLEAN. Default = TRUE. |
|
sdo_tile_flag |
For internal use only. Not supported in this release.
Data type is BOOLEAN. Default = 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 indexing with fixed-size tiles.
Data type is BOOLEAN. Default = TRUE. |
Usage Notes
Note:
This procedure is likely to be removed in a future release of Spatial.
|
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 operation after it executes; therefore, this transaction can be rolled back.
Example 15-5 tessellates the polygon for geometry 25 and adds the generated tiles to the LAYER1_SDOINDEX table.
Example 15-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
- SDO_TUNE.ESTIMATE_TILING_LEVEL
SDO_ADMIN.VERIFY_LAYER
Format
SDO_ADMIN.VERIFY_LAYER (layername,[maxtiles])
Description
Checks for the existence of the geometry and spatial index tables.
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 15-6 verifies the LAYER1 data set layer.
Example 15-6 Verify a Layer
SQL> EXECUTE SDO_ADMIN.VERIFY_LAYER('layer1');
Related Topics
None.
