HOW TO
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.
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.
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);
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.
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);
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;
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.
Article ID: 000011694
Get help from ArcGIS experts
Download the Esri Support App