Oracle7 Spatial Data Option User's Guide and Reference Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Sample SQL Scripts and Tuning Hints


This chapter provides supplemental information to aid in setup, maintenance, and tuning of a spatial database. The scripts and tuning suggestions provided are intended as guidelines that can be adapted to the specific needs of your database.

A.1 Sample SQL Scripts

The following sample SQL script files are provided to show how to use dynamic SQL in a PL/SQL block to create layer tables for spatially indexed data or to administer and manipulate all the partitions of a partitioned spatial table. The scripts are available after installation in the ORACLE_HOME/md/admin directory.

The following SQL scripts are described in this appendix:

A.1.1 altpart.sql Script

The altpart.sql script file shows how to use dynamic SQL in a PL/SQL procedure to modify all partitions of a Spatial Data Option partitioned table.

The Spatial Data Option data dictionary view used in this SQL script requires that a registered Spatial Data Option partitioned table is specified. If the table is not registered, you can use the USER_TABLES view to select all the partition tables from the user's schema. To use the USER_TABLES view, use the following syntax:

SQL> SELECT TABLENAME FROM user_tables WHERE TABLENAME LIKE 
2> '%tablename_P%';

A.1.2 cr_spatial_index.sql Script

The cr_spatial_index.sql script file shows an example of updating the spatial index for a geometry, and executing a commit after every 50 vertices have been entered.

The SDO_ADMIN.POPULATE_INDEX() and SDO_ADMIN.POPULATE_INDEX_FIXED() procedures operate as a single transaction. To reduce the amount of rollback required to execute these procedures, you can write a routine similar to that in cr_spatial_index.sql. This script loops and calls SDO_ADMIN.UPDATE_INDEX for a geometry, committing after every 50 entries.

-- cr_spatial_index.sql
--
-- Note: if geometries do not span more than 1 row, you can remove
-- the DISTINCT qualifier from the SELECT statement
--
declare
   cursor c1 is SELECT DISTICT sdogid from POLYGON_SDOGEOM;
   gid number;
   i number; 
begin
     i := 0;
     for r in c1 loop
       begin
        gid:= r.sdo_gid;
        sdo_admin.update_index_fixed('POLYGON', gid, 15, FALSE, FALSE, FALSE);
        exeption when others then
          dbms_output.put_line('error for gid'||to_char(gid)||':  '||SQLERRM );
       end;
       i:=  i + 1;
       if i = 50 then
          commit;
          i:= 0;
       end if;
     endloop;
commit;
end;
/


Note:

The cr_spatial_index.sql script is not available in your ORACLE_HOME/md/admin directory after installation. You must create this script yourself.

 

A.1.3 crlayer.sql Script

The crlayer.sql script file is a template used to create all the tables for a layer and populate the metadata in the SDODIM and SDOLAYER tables.

A.1.4 drppart.sql Script

The droppart.sql script file shows how to use dynamic SQL in a PL/SQL procedure to drop all partitions of a Spatial Data Option partitioned table. After running this procedure, you must run the SDO_ADMIN.DROP_PARTITION_INFO procedure.

The Spatial Data Option data dictionary view used in this SQL script requires that a registered Spatial Data Option partitioned table is specified. If the table is not registered, you can use the USER_TABLES view to select all the partition tables from the user's schema. To use the USER_TABLES view, use the following syntax:

SQL> SELECT TABLENAME FROM user_tables WHERE TABLENAME LIKE 
2> '%tablename_P%';

A.1.5 sdogrant.sql Script

The sdogrant.sql script file contains an administrative procedure, propagate_grants(), which is used after calling the sdo_admin.partition() or sdo_admin.repartition() procedures.

This procedure must first be compiled by running the sdogrant.sql file. The propagate_grants() procedure is only callable by the user who compiled it.

A.2 Tuning Tips and Tricks

The following information can be used as a guideline for tuning a spatial database.

A.2.1 Data Modeling

Data modeling is very important when designing a spatially enabled database. It is important to understand how the data will be queried. Assume your model uses line strings to represent both roads and rivers. If the user of your application asks for all the roads in a region independently from asking to see the rivers, then roads and rivers should be modeled in two separate Spatial Data Option layers. This way you do not have to sift through as much data during a query.

If the user of your application will always ask to see all the roads and rivers in a region, then it is appropriate to model roads and rivers in the same layer.

A.2.2 Understanding the Tiling Level

The following example explains how tiling is used in Spatial Data Option.

Assume you want all the roads (line strings) that overlap a county boundary (polygon) in a spatial database containing 10 million roads. Ignoring Spatial Data Option features for a moment, in purely mathematical terms, the problem translates into comparing all the line segments that make up each road, to the line segments and area of the county boundary to see if there is any intersection. This geometry to geometry comparison is very expensive.

Spatial Data Option simplifies this calculation by approximating each geometry with fixed-sized tiles. The primary filter in Spatial Data Option translates the problem to show all the roads that have a tile equal to a tile that approximates the polygon. The result of this is a superset of the final answer.

The secondary filter (a true geometry to geometry comparison) can now be applied to the candidates that returned from the Spatial Data Option primary filter, instead of to every road in the archive.

Picking the correct tile size for fixed tiling is one of the most important factors in obtaining good performance. If the tile size you select is too small, you could end up generating thousands of tiles per geometry. Also, the process of tiling a query window (like the county boundary mentioned previously) may become very time consuming.

At the same time, you do not want to choose tiles that are too big. This would defeat the purpose of the Spatial Data Option primary filter. If the tiles are too big, then too many geometries are returned from the primary filter and are sent to the more costly secondary filter.

Oracle recommends running the ESTIMATE_TILING_LEVEL function on your data set to get an initial tiling level estimate. This may not be your final answer, but it will be a good level to start your analysis. In general, it is also recommended that you take a random sample of your data and check the query performance at different levels of tiling. This would give an indication of what is the best tiling level for the total data set.

A.2.3 Database Sizing

Properly choosing rollback segments and tablespaces are important for getting good performance from Spatial Data Option. Therefore, it is very important to read the Oracle7 Server Administrator's Guide and understand the concepts of tablespaces and rollbacks.

Here are some general guidelines to consider:

A.2.4 Visualizing the Spatial Index (Drawing Tiles)

The Spatial Data Option spatial index is represented internally as a linear quadtree. The structure used to represent the linear quadtree is composed of two components: a data component and a metadata component. The data component of the linear quadtree is stored in the sdo_code column, and the metadata component is stored in the sdo_meta column.

The sdo_meta column is not required for spatial queries. However, by combining the sdo_meta column with the sdo_code column, the tiles of any geometry or for the entire data set can be decoded. This capability allows the tiles to be visualized.

The following SQL query can be used to decode all of the index entries in a <layername>_SDOINDEX table:

 
SELECT hhcellbndry (sdo_code || sdo_meta, 1, -180.000000000, 180.000000000,
hhlength (sdo_code || sdo_meta), 'MIN') min_x, hhcellbndry (sdo_code || sdo_meta, 1, -180.000000000, 180.000000000,
hhlength (sdo_code || sdo_meta), 'MAX') max_x, hhcellbndry (sdo_code || sdo_meta, 2, -90.000000000, 90.000000000,
hhlength (sdo_code || sdo_meta), 'MIN') min_y, hhcellbndry (sdo_code || sdo_meta, 2, -90.000000000, 90.000000000,
hhlength (sdo_code || sdo_meta), 'MAX') max_y FROM (SELECT DISTINCT sdo_code, sdo_meta FROM <layer_name>_sdoindex);

The following SQL query can be used to decode the index entries for a specific geometry stored in a <layername>_SDOINDEX table:

SELECT hhcellbndry (sdo_code || sdo_meta, 1, -180.000000000, 180.000000000,
hhlength (sdo_code || sdo_meta), 'MIN') min_x, hhcellbndry (sdo_code || sdo_meta, 1, -180.000000000, 180.000000000,
hhlength (sdo_code || sdo_meta), 'MAX') max_x, hhcellbndry (sdo_code || sdo_meta, 2, -90.000000000, 90.000000000,
hhlength (sdo_code || sdo_meta), 'MIN') min_y, hhcellbndry (sdo_code || sdo_meta, 2, -90.000000000, 90.000000000,
hhlength (sdo_code || sdo_meta), 'MAX') max_y FROM <layer_name>_sdoindex WHERE sdo_gid = <geometry id>;

In these examples, the dimension boundaries were assumed to be -180 to 180, and -90 and 90. The dimensional informatin is stored in the <layername>_SDODIM table.

A.2.5 Efficient Queries for Point Data

When querying point data with a rectangular query window, you can take advantage of the nature of these geometries to improve performance.

A rectangle can be defined by its lower-left and upper-right coordinates (X1, Y1, and X2, Y2). A point has a single set of coordinates (Px, Py). Instead of using the sdo_geom.relate()function in the secondary filter, you can use simple SQL comparison operators as follows:

where (X1 <= Px <= X2) 
 and  (X2 <= Py <= Y2);

Additional improvements can be obtained by not using the DISTINCT clause in your SQL queries concerning point data.

A.2.6 Efficient Queries for Line and Polygon Data

Testing has shown that significant performance improvements can be obtained by using the NO_MERGE optimizer instruction in queries involving either line or polygon data. The following example shows an optimized query for finding the cities intersected by a road (1234):

SELECT DISTINCT gid_a gid1
FROM (SELECT /*+ NO_MERGE  */
             DISTINCT a.sdo_gid gid_a,
                      b.sdo_gid gid_b
      FROM cities_sdoindex a,
           road_sdoindex b
      WHERE gid_b = 1234
        AND a.sdo_code = b.sdo_code),
WHERE sdo_geom.relate('cities', gid_a, 'ANYINTERACT',
      'road', 1234) <> 'FALSE'; 

A.2.7 Using Customized Geometry Types

Spatial Data Option supports three geometric types: points, lines, and polygons. If your data contains another type, such as a circle or arc, then you must choose the supported type which best approximates your desired type. For example, a circle can be defined as a multi-sided polygon. Obviously, the more coordinates in the element, the better the approximation will be.

Although customized types are not supported, you do not have to lose your knowledge of the type. After storing the approximated element, create another element in that geometry with etype=0. Spatial Data Option ignores elements of etype 0. You can then write your own routines to handle your specialized geometry type.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index