Oracle7 Spatial Data Option User's Guide and Reference

Library

Product

Contents

Index

Administrative Procedures

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

register and manipulate partitioned spatial tables (partitioned tables are used only for large volumes of point data)
tessellate entries in a geometry table and place them in a spatial index table
verify partitioned spatial table and spatial index table information

This chapter contains descriptions of the following administrative procedures for working with either partitioned point data or spatially indexed data:

Table 5¯1 Administrative Procedures

Procedure	Description
SDO_ADMIN.ALTER_HIGH_WATER_MARK	Alters the high water mark of a partitioned table.
SDO_ADMIN.DROP_PARTITION_INFO	Removes a partitioned table.
SDO_ADMIN.PARTITION	Places data into partition tables.
SDO_ADMIN.POPULATE_INDEX SDO_ADMIN.POPULATE_INDEX_FIXED	Generates a spatial index for the geometry table using either a set number of tiles, or a fixed size tile.
SDO_ADMIN.PROPAGATE_GRANTS	Propagate the grants on the registered base table to the various partitions.
SDO_ADMIN.REGISTER_PARTITION	Creates a partitioned spatial table.
SDO_ADMIN.REPARTITION	Reorganizes a table based on the sorted values of the data contained within it.
SDO_ADMIN.SDO_CODE_SIZE	Determines the required sizes for SDO_CODE and SDO_MAXCODE.
SDO_ADMIN.UPDATE_INDEX SDO_ADMIN.UPDATE_INDEX_FIXED	Updates the spatial index based on changes to the geometry table.
SDO_ADMIN.VERIFY_LAYER	Checks for the existence of geometry and spatial index tables.
SDO_ADMIN.VERIFY_PARTITIONS	Checks for the existence of a table.

Also see Appendix A, "Sample SQL Scripts" for additional administrative tools useful for working with partitioned point data.

SDO_ADMIN.ALTER_HIGH_WATER_MARK

Purpose

This procedure alters the high water mark of a partitioned spatial table. The high water mark defines how many records can be stored in a partition before it subdivides. The table must exist and be registered in the Spatial Data Option data dictionary.

Syntax

SDO_ADMIN.ALTER_HIGH_WATER_MARK (tablename, high_water_mark)

Keywords and Parameters

tablename	Specifies the name of the partitioned table. Datatype is VARCHAR2.
high_water_mark	Specifies the new high water mark for the table. Datatype is INTEGER.

Usage Notes

None

Example 5¯1

The following example changes the high water mark to 5000 records for the TABLE1 partitioned spatial table:

SQL> EXECUTE SDO_ADMIN.ALTER_HIGH_WATER_MARK('table1', 5000);

SDO_ADMIN.DROP_PARTITION_INFO

Purpose

This procedure removes a partitioned spatial table from the Spatial Data Option data dictionary. The table must exist and must be registered in the Spatial Data Option data dictionary.

Syntax

SDO_ADMIN.DROP_PARTITION_INFO (tablename)

Keywords and Parameters

tablename

Specifies the name of the partitioned table.
Datatype is VARCHAR2.

Usage Notes

Consider the following when using this procedure:

This procedure does not drop the spatial table and its associated partition tables from the user's schema. For a description of how to remove a partitioned spatial table from the user's schema, see the droppart.sql sample SQL script file described in Section A.3.

Example 5¯2

The following example removes the TABLE1 table from the Spatial Data Option data dictionary:

SQL> EXECUTE SDO_ADMIN.DROP_PARTITION_INFO('table1');

SDO_ADMIN.PARTITION

Purpose

This procedure places data into partition tables based on the sorted order of encoded dimensional values.

Syntax

SDO_ADMIN.PARTITION (source_table, tablename, parallel, guess [, plummet_flag] [,tablespace] )

Keywords and Parameters

source_table

Specifies the Oracle7 table or view of the table containing the partition key column.
Datatype is VARCHAR2.

tablename

Specifies the name of the table to partition.
Datatype is VARCHAR2.

parallel

Specifies the degree of parallelism for an operation on a single instance.
Datatype is INTEGER.

guess

Specifies the estimated largest common level of all the potential partitions to be created from data in the source_table. The common level of a partition is the number of levels of resolution of the common HHCODE for the partition.
Datatype is INTEGER.

plummet_flag

Specifies if the common HHCODE for all the potential partitions to be created from data in the source_table contains the maximum possible common level. If TRUE, the common HHCODE for each potential partition contains the maximum possible common level. If FALSE, the common HHCODE for each potential partition contains the minimum possible common level.
Default value is FALSE.
Datatype is BOOLEAN.

tablespace

Specifies the tablespace in which the partitions should be created. Default is the tablespace of the base table.

Usage Notes

Consider the following when using this procedure:

The maximum size of the partition tables is determined by the high water mark of the partitioned spatial table.
To perform this procedure, first load the original data into an Oracle7 table using a utility such as SQL*Loader. After the data is loaded, encode the data using the appropriate combination of Spatial Data Option data conversion functions (see Chapter 8.) The encoded data is used as the partition key column. The partition key column is provided as either a column in the Oracle7 table or as a view of that table.
For more information on specifying the degree of parallelism, see the Oracle7 Server Tuning manual.

Example 5¯3

The following example partitions the TABLE1 partitioned spatial table with data contained in the SOURCE1 Oracle7 table:

SQL> EXECUTE SDO_ADMIN.PARTITION('source1','table1',1,10,FALSE);

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 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 generate a fixed number of tiles.

Syntax

SDO_ADMIN.POPULATE_INDEX (layername, maxtiles, [synch_flag,])

Keywords and Parameters

layername

Specifies the name of the data set layer. The layername is used to construct the name of the geometry and spatial index tables.
Datatype is VARCHAR2.

maxtiles

Specifies the maximum number of tiles to generate for each geometric object.
Datatype 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.
Datatype is BOOLEAN.

Usage Notes

Consider the following when using this procedure:

The SQL CREATE TABLE command is used to create the spatial index table, <layername>_SDOINDEX, prior to calling this procedure.
For performance reasons, set the synch_flag to FALSE when the spatial index table contains zero rows.
For performance reasons, create an index on SDO_GID in the <layername>_SDOGEOM table before calling this routine.

Example 5¯4

The following example tessellates all the geometric objects in the LAYER1_SDOGEOM table and adds the generated tiles to the LAYER1_SDOINDEX table:

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

SDO_ADMIN.POPULATE_INDEX_FIXED

Purpose

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 layername is used to construct the name of the geometry and spatial index tables.
Datatype is VARCHAR2.

tile_size

Specifies the number of tessellations required to achieve the desired tile size (see the Usage Notes.) Datatype 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.
Datatype is BOOLEAN.

sdo_tile_flag

For internal use only. Not supported in this release. Default is FALSE.

sdo_maxcode_flag

Specifies whether the SDO_MAXCODE column is populated. If TRUE, SDO_MAXCODE is populated. If FALSE, the column is not populated. Datatype is BOOLEAN.
Default value is TRUE.

Usage Notes

Note The syntax and usage of this procedure is likely to change in a future release of Spatial Data Option.

Consider the following when using this procedure:

The SQL CREATE TABLE command is used to create the spatial index table, <layername>_SDOINDEX, prior to calling this procedure.
The layer is tessellated into equal-size 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 2.3.1, "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 SDO_GID in the <layername>_SDOGEOM table before calling this routine.

Example 5¯5

The following example tessellates all the geometric objects in the LAYER1_SDOGEOM table using up to 256 (44) fixed-size tiles and adds the generated tiles to the LAYER1_SDOINDEX table:

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

SDO_ADMIN.PROPAGATE_GRANTS

Purpose

This procedure is used to propagate the grants on the base table to the partitions.

Syntax

SDO_ADMIN.PROPAGATE_GRANTS (tablename)

Keywords and Parameters

tablename

Specifies the name of the partitioned table.
Datatype is VARCHAR2.

Usage Notes

Consider the following when using this procedure:

This procedure is used after calls to SDO_ADMIN.PARTITION() or SDO_ADMIN.REPARTITION(). It must be called by the owner of the partition.

This procedure must be compiled prior to use. See Section A.4, "sdogrant.sql Script".

Example 5¯6

The following example propages grants from the TABLE1 partitioned spatial table:

SQL>  EXECUTE SDO_ADMIN.PROPAGATE_GRANTS('TABLE1');

SDO_ADMIN.REGISTER_PARTITION_INFO

Purpose

This procedure creates a partitioned spatial table entry in the Spatial Data Option data dictionary, and defines the partition key column and the high water mark for the table.

Syntax

SDO_ADMIN.REGISTER_PARTITION_INFO (tablename, column, high_water_mark)

Keywords and Parameters

tablename

Specifies the name of the partitioned table.
Datatype is VARCHAR2.

column

Specifies the name of the partition key column for the table.
Datatype is VARCHAR2.

high_water_mark

Specifies the number of records to store in a partition before the partition subdivides. Datatype is INTEGER.

Usage Notes

Consider the following when using this procedure:

The SQL CREATE TABLE command is used to create the partitioned spatial table, with the partition key column defined as RAW(255), prior to calling this procedure.

Example 5¯7

The following example registers the TABLE1 partitioned spatial table:

SQL> EXECUTE SDO_ADMIN.REGISTER_PARTITION_INFO('table1', 
2> 'hhcolumn', 1000);

SDO_ADMIN.REPARTITION

Purpose

This procedure reorganizes a partitioned spatial table based on the sorted order of encoded dimensional values already contained in it. The table must exist and must be registered in the Spatial Data Option data dictionary.

Syntax

SDO_ADMIN.REPARTITION (tablename, parallel, [tablespace])

Keywords and Parameters

tablename

Specifies the name of the partitioned table.
Datatype is VARCHAR2.

parallel

Specifies the degree of parallelism for an operation on a single instance.
Datatype is INTEGER.

tablespace

Specifies the name of the tablespace in which to create the partition. Datatype is VARCHAR2.

Usage Notes

Consider the following when using this procedure:

The tablespace variable is optional. If you do not supply a tablespace name, the partitions are created in the same tablespace as the registered partition table.
The maximum size of the reorganized partition tables is determined by the high water mark of the partitioned spatial table.
For more information on specifying the degree of parallelism, see Chapter 6,"Parallel Query Option," in Oracle7 Server Tuning.

Example 5¯8

The following example repartitions the TABLE1 partitioned spatial table:

SQL> EXECUTE SDO_ADMIN.REPARTITION('table1', 1);

SDO_ADMIN.SDO_CODE_SIZE

Purpose

This function determines the size that the SDO_CODE column should be in the <layer>_SDOINDEX table.

Syntax

SDO_ADMIN.SDO_CODE_SIZE (layername)

Keywords and Parameters

layername

Specifies the name of the data set layer. Datatype is VARCHAR2.

Returns

This function returns the required size in bytes for the SDO_CODE column. The datatype is INTEGER.

Usage Notes

SDO_CODE is used to store the bit-interleaved cell id of a tile that covers a geometry. SDO_MAXCODE is SDO_CODE padded out one place farther than the longest allowable code name for the index. Both columns are defined as raw datatypes, with a maximum of 255 bytes. Use SDO_ADMIN.SDO_CODE_SIZE to fine-tune the size of the columns.

You should always set the SDO_MAXCODE column to one greater than the SDO_CODE column.

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. 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 (layername, GID, maxtiles, [replace_flag])

Keywords and Parameters

layername

Specifies the name of the data set layer. The layername is used to construct the name of the geometry table.
Datatype is VARCHAR2.

GID

Specifies the geometric object identifier.
Datatype is NUMBER.

maxtiles

Specifies the maximum number of tiles to generate for each geometric object.
Datatype is INTEGER.

replace_flag

Specifies whether 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.
Datatype is BOOLEAN.

Usage Notes

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 SDO_GID in the <layername>_SDOGEOM table before calling this routine.

Example 5¯9

The following example removes the existing tiles for geometry 25 from the LAYER1_SDOINDEX table, and then adds the generated tiles to the LAYER1_SDOINDEX table:

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

Example 5¯10

The following example tessellates the polygon for geometry 25 and adds the generated tiles to the LAYER1_SDOINDEX table:

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

SDO_ADMIN.UPDATE_INDEX_FIXED

Purpose

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 layername is used to construct the name of the geometry table.
Datatype is VARCHAR2.

GID

Specifies the geometric object identifier.
Datatype is NUMBER.

tile_size

Specifies the number of tessellations required to achieve the desired fixed-size tiles. Each tessellation creates subdivides the previous level into four new tiles.
Datatype is INTEGER.

replace_flag

Specifies whether 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.
Datatype is BOOLEAN.

sdo_tile_flag

For internal use only. Not supported in this release.
Datatype is BOOLEAN.
Default is FALSE.

sdo_maxcode_flag

Specifies whether the SDO_MAXCODE column is populated. If TRUE, SDO_MAXCODE is populated. If FALSE, the column is not populated.
Datatype is BOOLEAN.
Default is TRUE.

Usage Notes

Note The syntax and usage of this procedure is likely to change in a future release of Spatial Data Option.

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 SDO_GID in the <layername>_SDOGEOM table before calling this routine.

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 layername is used to construct the name of the geometry and spatial index tables.
Datatype is VARCHAR2.

maxtiles

For internal use only. Not supported in this release.

Usage Notes

Consider the following when using this procedure:

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 5¯11

The following example verifies the LAYER1 data set layer:

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

SDO_ADMIN.VERIFY_PARTITIONS

Purpose

This procedure checks if the partitioned spatial table exists, if it is registered in the Spatial Data Option data dictionary, and if the partition key column exists as defined in the Spatial Data Option data dictionary.

Syntax

SDO_ADMIN.VERIFY_PARTITIONS (tablename)

Keywords and Parameters

tablename

Specifies the name of the table.
Datatype is VARCHAR2.

Usage Notes

Consider the following when using this procedure:

This procedure can generate the following errors depending on the results of the verification:

SDO 13113 (Oracle table does not exist)
SDO 13108 (spatial table not found)
SDO 13111 (spatial table has no partition key defined)
SDO 13129 (HHCODE column not found)

Example 5¯12

The following example verifies the TABLE1 partitioned spatial table:

SQL> EXECUTE SDO_ADMIN.VERIFY_PARTITIONS('table1');

source_table	Specifies the Oracle7 table or view of the table containing the partition key column. Datatype is VARCHAR2.
tablename	Specifies the name of the table to partition. Datatype is VARCHAR2.
parallel	Specifies the degree of parallelism for an operation on a single instance. Datatype is INTEGER.
guess	Specifies the estimated largest common level of all the potential partitions to be created from data in the source_table. The common level of a partition is the number of levels of resolution of the common HHCODE for the partition. Datatype is INTEGER.
plummet_flag	Specifies if the common HHCODE for all the potential partitions to be created from data in the source_table contains the maximum possible common level. If TRUE, the common HHCODE for each potential partition contains the maximum possible common level. If FALSE, the common HHCODE for each potential partition contains the minimum possible common level. Default value is FALSE. Datatype is BOOLEAN.
tablespace	Specifies the tablespace in which the partitions should be created. Default is the tablespace of the base table.

layername	Specifies the name of the data set layer. The layername is used to construct the name of the geometry and spatial index tables. Datatype is VARCHAR2.
maxtiles	Specifies the maximum number of tiles to generate for each geometric object. Datatype 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. Datatype is BOOLEAN.

layername	Specifies the name of the data set layer. The layername is used to construct the name of the geometry and spatial index tables. Datatype is VARCHAR2.
tile_size	Specifies the number of tessellations required to achieve the desired tile size (see the Usage Notes.) Datatype 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. Datatype is BOOLEAN.
sdo_tile_flag	For internal use only. Not supported in this release. Default is FALSE.
sdo_maxcode_flag	Specifies whether the SDO_MAXCODE column is populated. If TRUE, SDO_MAXCODE is populated. If FALSE, the column is not populated. Datatype is BOOLEAN. Default value is TRUE.

tablename	Specifies the name of the partitioned table. Datatype is VARCHAR2.
column	Specifies the name of the partition key column for the table. Datatype is VARCHAR2.
high_water_mark	Specifies the number of records to store in a partition before the partition subdivides. Datatype is INTEGER.

layername	Specifies the name of the data set layer. The layername is used to construct the name of the geometry table. Datatype is VARCHAR2.
GID	Specifies the geometric object identifier. Datatype is NUMBER.
maxtiles	Specifies the maximum number of tiles to generate for each geometric object. Datatype is INTEGER.
replace_flag	Specifies whether 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. Datatype is BOOLEAN.

Administrative Procedures

Table 5¯1 Administrative Procedures

Purpose

Usage Notes

Related Topics

SDO_ADMIN.DROP_PARTITION_INFO

Purpose

Keywords and Parameters tablename Specifies the name of the partitioned table. Datatype is VARCHAR2.

Usage Notes

Related Topics

SDO_ADMIN.PARTITION

Purpose

Usage Notes

Example 5¯3

Related Topics

SDO_ADMIN.POPULATE_INDEX

Purpose

Usage Notes

Example 5¯4

Related Topics

SDO_ADMIN.POPULATE_INDEX_FIXED

Purpose

Usage Notes

Example 5¯5

Related Topics

SDO_ADMIN.PROPAGATE_GRANTS

Purpose

Keywords and Parameters tablename Specifies the name of the partitioned table. Datatype is VARCHAR2.

Usage Notes

Related Topics

SDO_ADMIN.REGISTER_PARTITION_INFO

Purpose

Usage Notes

Related Topics

SDO_ADMIN.REPARTITION

Purpose

Usage Notes

Related Topics

SDO_ADMIN.SDO_CODE_SIZE

Purpose

Keywords and Parameters layername Specifies the name of the data set layer. Datatype is VARCHAR2.

Returns

Purpose

Usage Notes

Example 5¯9

Example 5¯10

Related Topics

SDO_ADMIN.UPDATE_INDEX_FIXED

Purpose

Usage Notes

Related Topics

SDO_ADMIN.VERIFY_LAYER

Purpose

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

Usage Notes

Related Topics

Purpose

Keywords and Parameters tablename Specifies the name of the table. Datatype is VARCHAR2.

Usage Notes

Example 5¯12

Related Topics

Keywords and Parameters

tablename

Specifies the name of the partitioned table.
Datatype is VARCHAR2.

Keywords and Parameters

tablename

Specifies the name of the partitioned table.
Datatype is VARCHAR2.

Keywords and Parameters

layername

Specifies the name of the data set layer. Datatype is VARCHAR2.

Keywords and Parameters

layername

Specifies the name of the data set layer. The layername is used to construct the name of the geometry and spatial index tables.
Datatype is VARCHAR2.

maxtiles

For internal use only. Not supported in this release.

Keywords and Parameters

tablename

Specifies the name of the table.
Datatype is VARCHAR2.