Oracle8 Spatial Cartridge Oracle8 Spatial Cartridge User's Guide and Reference Release 8.0.5 A53264-03 |
| Library | Product | Contents

# A
Sample SQL Scripts and Tuning Tips

This appendix 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

Spatial Cartridge provides sample SQL script files 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.

### A.1.1 Scripts for Spatial Indexing

This section describes the cr_spatial_index.sql and crlayer.sql scripts.

#### A.1.1.1 cr_spatial_index.sql Script

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

The procedures SDO_ADMIN.POPULATE_INDEX() and SDO_ADMIN.POPULATE_INDEX_FIXED() 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_FIXED()` for each GID, committing after every 50 GIDs.

    -- 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 DISTINCT sdo_gid 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);
            exception 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;
         end loop;
    commit;
    end;
    /

When you call the `SDO_ADMIN.UPDATE_INDEX_FIXED()` procedure for a large data set, you may get a "snapshot too old" error message from the Oracle server. You can avoid this error by creating more or larger rollback segments. You can also try to increase the number of GIDs before committing the transaction.

#####

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.1.2 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 <layername>_SDODIM and <layername>_SDOLAYER tables.

### A.1.2 Scripts for Partitioned Point Data

This section describes the following scripts:

- altpart.sql

- drppart.sql

- sdogrant.sql

Although the scripts described in this section are available, the recommended approach is to use Oracle8 partitioning and spatial indexing.

#### A.1.2.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 Cartridge partitioned table.

The Spatial Cartridge data dictionary view used in this SQL script requires that a registered Spatial Cartridge 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.2 drppart.sql Script

The drppart.sql script file shows how to use dynamic SQL in a PL/SQL procedure to drop (remove) all partitions of a Spatial Cartridge partitioned table. After running this procedure, you must run the `SDO_ADMIN.DROP_PARTITION_INFO()` procedure.

The Spatial Cartridge data dictionary view used in this SQL script requires that a registered Spatial Cartridge 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.3 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

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 spatial database. You should group geometries into layers based on the similarity of their attributes. Assume your data model uses line strings to represent both roads and rivers. The attributes of a road and the attributes of a river are different. Therefore, these geometries should be modeled in two different layers.

In practice, however, if the user of your application will always ask to see both the roads and rivers in a particular region (area of interest), then it may be appropriate to model roads and rivers in the same layer with a common set of attributes.

It is equally important to understand how the data in the various layers will be queried. If the user of your application is interested in querying the data based on a relationship between the layers, then you should index the layers with the same tiling level. For example, a query such as, "which roads cross rivers?" can achieve better performance if the roads and rivers layers are tiled at the same level.

It is not always critical to tile all your layers to the same level. You may find times when you have two layers that are optimally tiled to different levels (for example zipcode boundaries may be tiled to level 5 and and major roads may be tiled to level 7). If you want to ask the question, give me all the major roads that intersect a particular postal code boundary, it is not necessary to re-tile all the zipcode boundaries to level 7. You can move the postal code boundary of interest to a temporary layer and just re-tile that one geometry to level 7. You can then perform the query.

### A.2.2 Understanding the Tiling Level

The following example explains how tiling is used in Spatial Cartridge.

Assume you want all the roads (line strings) that overlap a county boundary (polygon) in a spatial database containing 10 million roads. Ignoring Spatial Cartridge 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 Cartridge simplifies this calculation by approximating each geometry with fixed-sized tiles. The primary filter in Spatial Cartridge 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 Cartridge primary filter, instead of to every road in the database.

Picking the correct tile size for fixed tiling is one of the most important factors in attaining 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 Cartridge 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.

Keep in mind that the tile size you choose should also depend on if the query window (area of interest) is already defined in the database. If the query window is defined in the database, (that is, if the spatial tables and spatial indexes already exist), then you should choose a smaller tile size. Assume the State layer and the Highway layer are already defined in the database. You could perform a spatial join query such as, "which interstate highways go through the state?" without incurring the overhead of tiling because the query window is already defined in the database. If, on the other hand, you are creating the query window dynamically, you have to factor in the time it takes to define and index the query window. In this case, you should choose a larger tile size to reduce the time it takes to define and index the query window.

Oracle recommends running the `SDO_TUNE.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 Cartridge. Therefore, it is very important to read the Oracle8 Administrator's Guide and understand the concepts of tablespaces and rollbacks.

Here are some general guidelines to consider:

- Always make sure that you have enough rollback space to run the SDO_ADMIN.POPULATE_INDEX_FIXED() procedure. To reduce rollback segment size, run the SDO_ADMIN.UPDATE_INDEX_FIXED() procedure in a loop (see Section A.1.1.1).

- Create separate tablespaces for Data Layers, Indexes, and Rollback Segments.

- Properly define initial extents, next extents, and pctincrease for data layer tables.

- Define the initial extent as small as possible when you create the <layername>_SDOLAYER and the <layername>_SDODIM tables. These tables contain a few rows each and a small initial extent will reduce the amount of wasted space.

- Use the SDO_ADMIN.VALIDATE_GEOMETRY() procedure to ensure correctness of geometries in the data sets. Entering incorrect data may lead to unexpected behavior in index creation and in the SDO_GEOM.RELATE() functions.

- Always build a B-tree index on the SDO_GID column of the <layername>_SDOGEOM table before attempting to call the `SDO_ADMIN.POPULATE_INDEX_FIXED()`, `SDO_ADMIN.UPDATE_INDEX_FIXED()`, `SDO_ADMIN.POPULATE_INDEX()`, or `SDO_ADMIN.UPDATE_INDEX()` procedure.

- For fixed-size tiling, always build a B-tree index on the SDO_CODE column of the <layername>_SDOINDEX table before trying any queries using this table.

- Always build a B-tree index on the SDO_GID column of the <layername>_
  SDOINDEX table if individual SDO_GIDs will be used as query windows for other Spatial Cartridge layers.

- Visualizing the indexing tiles, as described in Section A.2.8, can lead to a greater understanding of the tuning process with respect to the size of the tiles.

- For variable-sized tiling, always build a B-tree index on the SDO_GROUPCODE column of the <layername>_SDOINDEX table before trying any queries using this table.

### A.2.4 Tuning Point Data

Point data, unlike line and polygon data, has the unique characteristic of containing one tile per point. This section describes how to improve the performance of queries on point data.

#### A.2.4.1 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 (Xmin, Ymin and Xmax, Ymax). A point has a single set of coordinates (Px, Py). When your area-of-interest is a rectangle, instead of using the SDO_GEOM.RELATE()function in the secondary filter, you can use simple SQL comparison operators as follows:

    SELECT sdo_gid, sdo_x1, sdo_y1
    FROM  cities_sdogeom,
          (SELECT a.sdo_gid gid1
          FROM cities_sdoindex a,
               window_sdoindex b
          WHERE b.sdo_gid = [area of interest id]
            AND a.sdo_code = b.sdo_code)
               WHERE sdo_gid = gid1
                AND sdo_x1 BETWEEN Xmin AND Xmax
                AND sdo_y1 BETWEEN Ymin AND Ymax;

The DISTINCT clause is not necessary in the primary filter of the query because a point contains only a single tile in the spatial index.

#### A.2.4.2 Efficient Schema for Point Layers

Because a point is always referenced by only one tile in a spatial index, for additional performance, you can place the columns normally found in the <layername>_SDOINDEX table in the <layername>_SDOGEOM table. This will save you the cost of joining the <layername>_SDOINDEX and <layername>_SDOGEOM tables.

You still need to create an updatable view for the <layername>_SDOINDEX table that selects the appropriate columns from the <layername>_SDOGEOM table. This is because functions such as `SDO_ADMIN.UPDATE_INDEX_FIXED()` and `SDO_ADMIN.POPULATE_INDEX_FIXED()` expect a <layername>_SDOINDEX table to exist. Create the view using "instead of" triggers for insert, delete, and update such that the appropriate columns in the <layername>_SDO_GEOM table are updated. The following example shows how to use "instead of" triggers:

    CREATE OR REPLACE TRIGGER mytrig INSTEAD OF INSERT ON points_sdoindex
         REFERENCING new AS n
         FOR EACH ROW
         BEGIN
              UPDATE points_sdogeom SET points_sdogeom.sdo_code = :n.sdo_gid;
         END;
    CREATE OR REPLACE TRIGGER mydeltrig INSTEAD OF DELETE ON points_sdoindex
        REFERENCING old AS n
        FOR EACH ROW
        BEGIN
              UPDATE points_sdogeom SET points_sdogeom.sdo_code = NULL
              WHERE  points_sdogeom.sdo_gid = :n.sdo_gid;
        END;

The following example shows a window query of a layer containing point data when the window layer contains one rectangle:

    SELECT sdo_gid, sdo_x1, sdo_y1
    FROM  points_sdogeom a,
          window_sdoindex b
    WHERE b.sdo_gid = [area of interest id]
      AND a.sdo_code = b.sdo_code)
      AND sdo_x1 BETWEEN Xmin AND Xmax
      AND sdo_y1 BETWEEN Ymin AND Ymax;

### A.2.5 Tuning Spatial Join Queries

There are some helpful hints you can place in your spatial join queries to improve performance. The remainder of this section describes some of the hints you can use. For more information on hints, see the Oracle8 Tuning manual.

#### A.2.5.1 Using the NO_MERGE, INDEX, and USE_NL Hints

A spatial join takes place between two layers. When the two layers being joined are line or polygon layers, the spatial join query contains two DISTINCT clauses: one in the inner SELECT clause and the other in the outer SELECT clause. The Oracle optimizer ignores the inner DISTINCT clause to save on the cost of sorting. However, if the inner DISTINCT clause is ignored, the secondary filter gets called many more times than it needs to be. This can have a significant impact on performance because the secondary filter is an expensive operation. Use the NO_MERGE hint to prevent the optimizer from ignoring the inner DISTINCT clause.

In a spatial join, all the tiles from one layer are compared to all the tiles from another layer. The Oracle server performs a full table scan on one <layername>_SDOINDEX table, (preferably the smaller of the two), and an index lookup on the other <layername>_SDOINDEX table. Use the INDEX and USE_NL hints to force the optimizer to perform the full table scan on the smaller of the two <layername>_SDOINDEX tables being compared.

The following example shows a spatial join between line (road) and polygon (county) data. The query answers the question, "which counties intersect major roads?"

    SELECT /*+ cost
               ordered use_nl(COUNTY_sdogeom)
               index (COUNTY_sdogeom NAME_OF_SDO_GID_INDEX)
           */
           COUNTY_sdogeom.SDO_GID,
           COUNTY_sdogeom.SDO_ESEQ,
           COUNTY_sdogeom.SDO_SEQ,
           COUNTY_sdogeom.SDO_X1,COUNTY_sdogeom.SDO_Y1,
           COUNTY_sdogeom.SDO_X2,COUNTY_sdogeom.SDO_Y2,
           COUNTY_sdogeom.SDO_X3,COUNTY_sdogeom.SDO_Y3,
           COUNTY_sdogeom.SDO_X4,COUNTY_sdogeom.SDO_Y4,
           COUNTY_sdogeom.SDO_X5,COUNTY_sdogeom.SDO_Y5,
           COUNTY_sdogeom.SDO_X6,COUNTY_sdogeom.SDO_Y6,
           COUNTY_sdogeom.SDO_X7,COUNTY_sdogeom.SDO_Y7,
           COUNTY_sdogeom.SDO_X8,COUNTY_sdogeom.SDO_Y8
    FROM (SELECT DISTINCT gid_a gid1
          FROM (SELECT /*+ index (a NAME_OF_SDO_CODE_INDEX)
                           index (b NAME_OF_SDO_CODE_INDEX)
                           use_nl (a b)
                           no_merge */
                DISTINCT a.sdo_gid gid_a,
                         b.sdo_gid gid_b
                FROM COUNTY_SDOINDEX a,
                     MAJOR_ROAD_SDOINDEX b
                WHERE a.sdo_code = b.sdo_code)
          WHERE sdo_geom.relate('COUNTY', gid_a, 'ANYINTERACT',
                                'MAJOR_ROAD',gid_b) <> 'FALSE'),
          COUNTY_sdogeom
    WHERE COUNTY_sdogeom.sdo_gid = gid1;

#### A.2.5.2 Spatial Join Queries with Point Layers

The following example shows a spatial join between line (road) and point (street address) data. The query answers the question, "which addresses are on a major road?"

    SELECT /*+ cost
               ordered use_nl (STREET_ADDRESS_sdogeom)
               index (STREET_ADDRESS_sdogeom NAME_OF_SDO_GID_INDEX)
           */
           STREET_ADDRESS_sdogeom.SDO_GID,
           STREET_ADDRESS_sdogeom.SDO_X1,
           STREET_ADDRESS_sdogeom.SDO_Y1
    FROM (SELECT DISTINCT gid_a gid1
          FROM (SELECT /*+ index (a NAME_OF_SDO_CODE_INDEX)
                           index (b NAME_OF_SDO_CODE_INDEX)
                           use_nl (a b) */
                         a.sdo_gid gid_a,
                         b.sdo_gid gid_b
                FROM STREET_ADDRESS_SDOINDEX a,
                     MAJOR_ROAD_SDOINDEX b
                WHERE a.sdo_code = b.sdo_code)
          WHERE sdo_geom.relate('STREET_ADDRESS', gid_a, 'ANYINTERACT',
                                'MAJOR_ROAD',gid_b) <> 'FALSE'),
          COUNTY_sdogeom
    WHERE COUNTY_sdogeom.sdo_gid = gid1;

The inner DISTINCT clause is not necessary for spatial joins where one of the layers contains point data. Therefore, the NO_MERGE hint is not necessary. This is because points contain only one tile in the spatial index.

The following example shows a spatial join between polygon (county) and point (street address) data. The query generates a report that displays how many addresses are associated with each county.

If you can assume that each street address is associated with a single county, you can significantly speed up this query. Because points contain only a single tile in the spatial index, any street address tile that matches only one county tile in the primary filter does not need to go through the expensive secondary filter.

    SELECT county_gid, count(street_gid)
    FROM  (SELECT poly.sdo_gid county_gid, street.sdo_gid street_gid
           FROM  STREET_ADDRESS_sdoindex street,
                 (SELECT sdo_code county_sdo_code,
                         count(sdo_gid) interacts
                  FROM CENSUS_COUNTY_sdoindex
                  GROUP by sdo_code
                 ) counts,
                 CENSUS_COUNTY_sdoindex  poly
           WHERE street.sdo_code = counts.county_sdo_code
             AND poly.sdo_code   = street.sdo_code
             AND (counts.interacts = 1
                  OR
                  sdo_geom.relate('STREET_ADDRESS', street.sdo_gid,
                                  'ANYINTERACT',
                                  'CENSUS_COUNTY',poly.sdo_gid) <> 'FALSE'
                 )
          )
    GROUP BY county_gid;

### A.2.6 Using Customized Geometry Types

Spatial Cartridge supports three geometry types: points, lines, and polygons. If your data contains another type, such as a circle or arc, then you must choose the supported type that 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 Cartridge ignores elements of ETYPE=0. You can then write your own routines to handle your specialized geometry type.

### A.2.7 Performing Secondary Filter Queries and the Redo Log

When the Oracle server processes SQL statements that require sorting, such as statements containing an ORDER BY or DISTINCT clause, the Oracle server stores the result set in a temporary storage area. The result set is then sorted. If the SORT_AREA_SIZE is insufficient for holding the result set in memory, then some data may be written to disk and an entry is written in the redo log.

The RELATE() and INTERACT() secondary filters issue SQL statements internally that contain DISTINCT and ORDER BY clauses. If the SORT_AREA_SIZE initialization parameter is too small for processing the secondary filters, then some sorting may occur on disk, which causes entries to be written in the redo log. This may affect performance. For better performance, increase the SORT_AREA_SIZE parameter to force sorting to occur in memory.

### A.2.8 Visualizing the Spatial Index (Drawing Tiles)

To select an appropriate tiling level, it may help to visualize the tiles covering your geometries. Through visualization, you can determine how many tiles are used for each object, the size of the tiles, and how well the edges of your geometry are covered. The basic algorithm is:

1. Select the edges of the tiles represented by the index entries.

2. Plot the tiles on a two-dimensional grid.

3. Plot your geometries on the same grid.

The Spatial Cartridge 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 of the entire data set can be decoded. This capability allows the tiles to be visualized.

Two Spatial Cartridge internal functions have been made visible in order to describe the tiles. These functions were part of a previous release of Oracle Spatial Data Option, and are currently reserved for internal use only. The functions are not recommended for general use, except for this visualization example. Use the following syntax for the internal functions:

hhcellbndry (sdo_code || sdo_meta, sdo_dimnum, sdo_lb, sdo_ub,
hhlength(sdo_code || sdo_meta) {'MIN' | 'MAX'})

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

If you used `SDO_ADMIN.UPDATE_INDEX_FIXED()` or SDO_ADMIN.POPULATE_INDEX_FIXED() to generate your spatial index, replace "sdo_code || sdo_meta" with sdo_tile in the SQL statements that follow.

The following SQL query can be used to decode all the index entries in a
<layername>_SDOINDEX table. The example returns the coordinates of the lower-left and upper-right corners of each tile.

    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 <layername>_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 <layername>_sdoindex
    WHERE sdo_gid = <geometry id>;

Prev | Next
| Copyright © 1998 Oracle Corporation. All Rights Reserved. |
| Library | Product | Contents