Create Oracle Spatial function-based indexing

Summary

When creating a query layer that references an Oracle view containing an Oracle Spatial attribute, there may be the need to implement a spatial index. Without a spatial index, queries against tables and views with large quantities of attributes may experience some slower performance.

In these cases where performance is an issue, if the view derives an Oracle Spatial attribute, then a function-based index should be created on the attributes of the table that are used to derive the spatial attribute.

Procedure

In the following example, there is a table named 'ellipse', which does not have a spatial attribute. The view, named sdo_ellipse, creates the SDO_Geometry attribute.

1. Create the table and insert some rows.

DROP TABLE ellipse;
CREATE TABLE ellipse (
     id NUMBER(38) NOT NULL UNIQUE,
     name VARCHAR2(50),
     x NUMBER,
     y NUMBER,
     semi_major NUMBER,
     semi_minor NUMBER,
     azimuth NUMBER);
INSERT INTO ellipse VALUES (1, 'ellipse 1', 10, 10, 500, 250, 0);
INSERT INTO ellipse VALUES (2, 'ellipse 2', 10, 10, 500, 250, 90);
INSERT INTO ellipse VALUES (3, 'ellipse 3', 10, 10, 500, 250, 180);
INSERT INTO ellipse VALUES (4, 'ellipse 4', 10, 10, 500, 250, 45);

2. Create the view using the SDO_UTIL.ELLIPSE_POLYGON function. This function returns a polygon that approximates a specified ellipse. Consult the Oracle Spatial User's Guide and Reference for additional information.

DROP VIEW sdo_ellipse;
CREATE VIEW sdo_ellipse AS 
SELECT 
    id,
    name, 
    SDO_UTIL.ELLIPSE_POLYGON(x,y,semi_major,semi_minor,azimuth,0.1)
    shape
FROM ellipse;

At this point, there is no spatial attribute or spatial index. Drawing the layer in ArcGIS will be successful, however there may be some performance issues if the quantity of records within the table or view is large. The implementation of a function-based index may help to improve performance.

1. To create the function-based index, a row in the user_sdo_geom_metadata must be inserted for the table. Be sure to fully qualify the function being used. 

DROP INDEX sdo_ellipse_spindx;
DELETE FROM user_sdo_geom_metadata WHERE table_name = 'ELLIPSE';
INSERT INTO user_sdo_geom_metadata VALUES (
    'ellipse',
    'MDSYS.SDO_UTIL.ELLIPSE_POLYGON(x,y,semi_major,semi_minor,azimuth,0.1)',
    SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Lat', -90, 90, 0.05),
    SDO_DIM_ELEMENT('Long', -180, 180, 0.05)),
    8307);

2. As we are also using a view, for better performance, we want to add information to the user_sdo_geom_metatdata table for the view. Be sure to fully qualify the function being used.

DELETE FROM user_sdo_geom_metadata WHERE table_name = ‘sdo_ellipse’;
insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
    values (‘sdo_ellipse’,

‘SHAPE’,
 SDO_DIM_ARRAY(
      SDO_DIM_ELEMENT(‘Lat’, -90, 90, 0.05),
      SDO_DIM_ELEMENT(‘Long’, -180, 180, 0.05)),
      8307);
commit;

3. Now the spatial index can be created successfully.

CREATE INDEX sdo_ellipse_spindx
ON ellipse(SDO_UTIL.ELLIPSE_POLYGON(x,y,semi_major,semi_minor,azimuth,0.1))
INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

With the spatial index created, better performance is experienced in ArcGIS when displaying a large amount of data in the view/table. To learn more about Spatial Indexing, please consult this document.