Oracle8 Spatial Cartridge User's Guide and Reference
Release 8.0.4

A53264-02

Library

Product

Contents

Index

Prev Next

5
Administrative Procedures

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

This chapter contains descriptions of the administrative procedures used for working with either spatially indexed geometric data or partitioned point data. These data structures are mutually exclusive and the procedures only work with the structure for which they are designed.

Table 5-1 lists the administrative procedures for working with spatially indexed geometry-based data. Table 5-2 later in this chapter lists procedures for working with partitioned point data.

Table 5-1 Administrative Procedures for Spatially Indexed Data
Procedure   Data Structure   Description  

SDO_ADMIN.POPULATE_INDEX

 

Geometric objects

 

Generates a spatial index for the geometry table using either a set number of tiles, or a fixed-size tile.

 

SDO_ADMIN.SDO_CODE_SIZE

 

Geometric objects

 

Determines the required sizes for SDO_CODE and SDO_MAXCODE.

 

SDO_ADMIN.UPDATE_INDEX

 

Geometric objects

 

Updates the spatial index based on changes to the geometry table.

 

SDO_ADMIN.VERIFY_LAYER

 

Geometric objects

 

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 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 layername 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:

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

Example 5-1

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

Related Topics


SDO_ADMIN.POPULATE_INDEX_FIXED

Purpose

This procedure is provided for compatibility with Spatial Cartridge release 8.0.3 tables. This procedure has been replaced by enhanced features in the SDO_ADMIN.POPULATE_INDEX() procedure, and by supporting schema changes as shown in Section 1.4.

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 layername 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.
Default value is TRUE.
Data type is BOOLEAN.

 

Usage Notes


Note:

This procedure is likely to be removed in a future release of Spatial Cartridge.

 

Consider the following when using this procedure:

Example 5-2 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.

Example 5-2

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

Related Topics


SDO_ADMIN.SDO_CODE_SIZE

Purpose

This function determines the size that the SDO_CODE 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_CODE 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 columns.

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

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 layername 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:

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

Example 5-3

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

Related Topics


SDO_ADMIN.UPDATE_INDEX_FIXED

Purpose

This procedure is provided for compatibility with Spatial Cartridge release 8.0.3 tables. This procedure has been replaced by enhanced features in the SDO_ADMIN.UPDATE_INDEX() procedure, and by supporting schema changes as shown in Section 1.4.

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.
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.
Default value is TRUE.
Data type is BOOLEAN.

 

Usage Notes


Note:

This procedure is likely to be removed in a future release of Spatial Cartridge.

 

Consider the following when using this procedure:

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

Example 5-4

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

Related Topics


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.
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 5-5 verifies the LAYER1 data set layer:

Example 5-5

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

Related Topics

None


Partitioned Point Data Procedures

Table 5-2 lists the procedures that can be used with partitioned point data. These procedures are neither required nor compatible with the geometry-based data format.

Table 5-2 Administrative Procedures for Partitioned Point Data
Procedure   Data Structure   Description  

SDO_ADMIN.ALTER_HIGH_WATER_MARK

 

Partitioned points

 

Alters the high-water mark of a partitioned table.

 

SDO_ADMIN.DROP_PARTITION_INFO

 

Partitioned points

 

Removes a partitioned table.

 

SDO_ADMIN.PARTITION

 

Partitioned points

 

Places data into partition tables.

 

SDO_ADMIN.PROPAGATE_GRANTS

 

Partitioned points

 

Propagates the grants on the registered underlying table to the various partitions.

 

SDO_ADMIN.REGISTER_PARTITION_
INFO

 

Partitioned points

 

Creates a partitioned spatial table.

 

SDO_ADMIN.REPARTITION

 

Partitioned points

 

Reorganizes a table based on the sorted values of the data contained within it.

 

SDO_ADMIN.VERIFY_PARTITIONS

 

Partitioned points

 

Checks for the existence of a table.

 

Also see Appendix A, "Sample SQL Scripts and Tuning Tips" 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 Cartridge data dictionary.

This procedure is for use only with partitioned point data.

Syntax

SDO_ADMIN.ALTER_HIGH_WATER_MARK (tablename, high_water_mark)

Keywords and Parameters

tablename

 

Specifies the name of the partitioned table.
Data type is VARCHAR2.

 

high_water_mark

 

Specifies the new high water mark for the table.
Data type is INTEGER.

 

Usage Notes

None

Example 5-6 changes the high-water mark to 5000 records for the TABLE1 partitioned spatial table.

Example 5-6

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

Related Topics


SDO_ADMIN.DROP_PARTITION_INFO

Purpose

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

This procedure is used only with partitioned point data.

Syntax

SDO_ADMIN.DROP_PARTITION_INFO (tablename)

Keywords and Parameters

tablename

 

Specifies the name of the partitioned table.
Data type is VARCHAR2.

 

Usage Notes

This procedure does not remove 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 drppart.sql sample SQL script file described in Section A.1.2.2.

Example 5-7 removes the table1 table from the Spatial Cartridge data dictionary.

Example 5-7

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

Related Topics


SDO_ADMIN.PARTITION

Purpose

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

This procedure is used only with partitioned point data.

Syntax

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

Keywords and Parameters

source_table

 

Specifies the Oracle8 table or view of the table containing the partition key column.
Data type is VARCHAR2.

 

tablename

 

Specifies the name of the table to partition.
Data type is VARCHAR2.

 

parallel

 

Specifies the degree of parallelism for an operation on a single instance.
Data type 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.
Data type 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.
Data type is BOOLEAN.

 

tablespace

 

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

 

Usage Notes

Consider the following when using this procedure:

Example 5-8 partitions the TABLE1 partitioned spatial table with data contained in the source1 table.

Example 5-8

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

Related Topics


SDO_ADMIN.PROPAGATE_GRANTS

Purpose

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

This procedure is used only with partitioned point data.

Syntax

SDO_ADMIN.PROPAGATE_GRANTS (tablename)

Keywords and Parameters

tablename

 

Specifies the name of the partitioned table.
Data type is VARCHAR2.

 

Usage Notes

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.1.2.3, "sdogrant.sql Script".

Example 5-9 propagates grants from the TABLE1 partitioned spatial table.

Example 5-9

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

Related Topics


SDO_ADMIN.REGISTER_PARTITION_INFO

Purpose

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

This procedure is used only with partitioned point data.

Syntax

SDO_ADMIN.REGISTER_PARTITION_INFO (tablename, column, high_water_mark)

Keywords and Parameters

tablename

 

Specifies the name of the partitioned table.
Data type is VARCHAR2.

 

column

 

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

 

high_water_mark

 

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

 

Usage Notes

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

Example 5-10 registers the TABLE1 partitioned spatial table.

Example 5-10

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

Related Topics


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 Cartridge data dictionary.

This procedure is used only with partitioned point data.

Syntax

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

Keywords and Parameters

tablename

 

Specifies the name of the partitioned table.
Data type is VARCHAR2.

 

parallel

 

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

 

tablespace

 

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

 

Usage Notes

Consider the following when using this procedure:

Example 5-11 repartitions the table1 partitioned spatial table.

Example 5-11

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

Related Topics


SDO_ADMIN.VERIFY_PARTITIONS

Purpose

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

This procedure is used only with partitioned point data.

Syntax

SDO_ADMIN.VERIFY_PARTITIONS (tablename)

Keywords and Parameters

tablename

 

Specifies the name of the table.
Data type is VARCHAR2.

 

Usage Notes

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

Example 5-12 verifies the TABLE1 partitioned spatial table:

Example 5-12

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

Related Topics




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index