Oracle8(TM) Server Spatial Cartridge User's Guide and Reference Release 8.0.3 A53264_01 |
|
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 listed in Table 5-1 for working with either partitioned point data or spatially indexed data:
Also see Appendix A, "Sample SQL Scripts" for additional administrative tools useful for working with partitioned point data.
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.
SDO_ADMIN.ALTER_HIGH_WATER_MARK (tablename, high_water_mark)
tablename |
Specifies the name of the partitioned table. |
high_water_mark |
Specifies the new high water mark for the table. |
None
Example 5-1 changes the high water mark to 5000 records for the TABLE1 partitioned spatial table:
SQL> EXECUTE SDO_ADMIN.ALTER_HIGH_WATER_MARK('table1', 5000);
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.
SDO_ADMIN.DROP_PARTITION_INFO (tablename)
tablename |
Specifies the name of the partitioned table. |
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.4.
Example 5-2 removes the TABLE1 table from the Spatial Cartridge data dictionary:
SQL> EXECUTE SDO_ADMIN.DROP_PARTITION_INFO('table1');
This procedure places data into partition tables based on the sorted order of encoded dimensional values.
SDO_ADMIN.PARTITION (source_table, tablename, parallel, guess [, plummet_flag] [,tablespace] )
Consider the following when using this procedure:
Example 5-3 partitions the TABLE1 partitioned spatial table with data contained in the SOURCE1 Oracle8 table:
SQL> EXECUTE SDO_ADMIN.PARTITION('source1','table1',1,10,FALSE);
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.
SDO_ADMIN.POPULATE_INDEX (layername, maxtiles, [synch_flag,])
Consider the following when using this procedure:
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 function, you can write a routine that loops and calls SDO_ADMIN.UPDATE_INDEX()
repeatedly. See Section A.2, "cr_spatial_index.sql Script" for more information.
Example 5-4 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);
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.
SDO_ADMIN.POPULATE_INDEX_FIXED (layername, tile_size, [synch_flag,] [sdo_tile_flag,] [sdo_maxcode_flag])
Note:
The syntax and usage of this procedure is likely to change in a future release of Spatial Cartridge. |
Consider the following when using this procedure:
SDO_ADMIN.POPULATE_INDEX_FIXED()
behaves similarly to the CREATE INDEX statement in SQL. An implicit commit is executed after the procedures are called.
SDO_ADMIN.POPULATE_INDEX_FIXED()
operates as a single transaction. To reduce the amount of rollback required to execute this function, you can write a routine that loops and calls SDO_ADMIN.UPDATE_INDEX_FIXED(
) repeatedly. See Section A.2, "cr_spatial_index.sql Script" for more information.
Example 5-5 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,FALSE,FALSE,FALSE);
This procedure is used to propagate the grants on the underlying table to the partitions.
SDO_ADMIN.PROPAGATE_GRANTS (tablename)
tablename |
Specifies the name of the partitioned table. |
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.5, "sdogrant.sql Script".
Example 5-6 propagates grants from the TABLE1 partitioned spatial table:
SQL> EXECUTE SDO_ADMIN.PROPAGATE_GRANTS('TABLE1');
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.
SDO_ADMIN.REGISTER_PARTITION_INFO (tablename, column, high_water_mark)
Consider the following when using this procedure:
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-7 registers the TABLE1 partitioned spatial table:
SQL> EXECUTE SDO_ADMIN.REGISTER_PARTITION_INFO('table1',
2> 'hhcolumn', 1000);
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.
SDO_ADMIN.REPARTITION (tablename, parallel, [tablespace])
Consider the following when using this procedure:
Example 5-8 repartitions the TABLE1 partitioned spatial table:
SQL> EXECUTE SDO_ADMIN.REPARTITION('table1', 1);
This function determines the size that the SDO_CODE column should be in the <layername>_SDOINDEX table.
SDO_ADMIN.SDO_CODE_SIZE (layername)
layername |
Specifies the name of the data set layer. Data type is VARCHAR2. |
This function returns the required size in bytes for the SDO_CODE column. The data type is INTEGER.
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 data types, 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.
None
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.
SDO_ADMIN.UPDATE_INDEX (layername, GID, maxtiles, [replace_flag])
SDO_ADMIN.UPDATE_INDEX()
does not perform an implicit commit after it executes and therefore the transaction can be rolled back.
SDO_ADMIN.POPULATE_INDEX()
operates as a single transaction. To reduce the amount of rollback required to execute the function, you can write a routine that loops and calls SDO_ADMIN.UPDATE_INDEX()
repeatedly. See Section A.2, "cr_spatial_index.sql Script" for more information.
Example 5-9 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 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);
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.
SDO_ADMIN.UPDATE_INDEX_FIXED (layername, GID, tile_size, [replace_flag,] [sdo_tile_flag] [sdo_maxcode_flag])
Note:
The syntax and usage of this procedure is likely to change in a future release of Spatial Cartridge. |
SDO_ADMIN.UPDATE_INDEX_FIXED()
does not perform an implicit commit after it executes and therefore this transactions can be rolled back.
SDO_ADMIN.POPULATE_INDEX_FIXED()
operates as a single transaction. To reduce the amount of rollback required to execute this function, you can write a routine that loops and calls SDO_ADMIN.UPDATE_INDEX_FIXED(
) repeatedly. See Section A.2, "cr_spatial_index.sql Script" for more information.
Example 5-11 tessellates the polygon for geometry 25 and adds the generated tiles to the LAYER1_SDOINDEX table:
SQL> EXECUTE SDO_ADMIN.UPDATE_INDEX_FIXED ('layer1',25,4,FALSE,FALSE,FALSE);
This procedure checks for the existence of the geometry and spatial index tables.
SDO_ADMIN.VERIFY_LAYER (layername,[maxtiles])
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-12 verifies the LAYER1 data set layer:
SQL> EXECUTE SDO_ADMIN.VERIFY_LAYER('layer1');
None
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.
SDO_ADMIN.VERIFY_PARTITIONS (tablename)
tablename |
Specifies the name of the table. |
Consider the following when using this procedure:
This procedure can generate the following errors depending on the results of the verification:
Example 5-13 verifies the TABLE1 partitioned spatial table:
SQL> EXECUTE SDO_ADMIN.VERIFY_PARTITIONS('table1');