How To: Set an Oracle Hint in a SQL Statement to Use the St_Spatial

Summary

SQL developers may require the ability to provide an Oracle hint in a SQL statement to direct the optimizer to use a spatial index.

Based upon the selectivity and cost of the spatial operator such as ST_EnvIntersects, ST_Overlaps, etc., Oracle's optimizer may either choose to use the spatial index or perform a full table scan. The decision on the best access path is determined by the table object's statistics.

A SQL developer can use an Oracle hint when they know the name of the index to provide with the optimizer INDEX hint.

Procedure

Add an optimizer INDEX hint to a sql statement to ensure Oracle uses the spatial index verses performing a full table scan.

Given the statement below, Oracle's optimizer selects the best access path based upon the table road's statistics. Using auto trace in SQL*Plus, see a full table scan is performed.

Code:
SQL> set autotrace traceonly explain
SQL> SELECT r.name, r.shape 
  2  FROM road r
  3  WHERE st_envintersects
  4    (r.shape,-119.34,27.97,-109.80,37.37) = 1;

 -------------------------------------------------------------------------
 | Id | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------
 |  0 | SELECT STATEMENT  |      | 71702 |    23M|   820   (1)| 00:00:10 |
 | *1 |  TABLE ACCESS FULL| ROAD | 71702 |    23M|   820   (1)| 00:00:10 |
 -------------------------------------------------------------------------

If the objective is to provide a hint to the Oracle optimizer to use the spatial index, an index hint must be added to the SQL statement. The optimizer INDEX hint requires the name of the index, in this case the name of the spatial index created on the road's shape attribute.

Again, using auto trace in SQL*Plus, note that the optimizer selects to use the domain index verses performing a full table scan.

Code:
SQL> SELECT /*+ INDEX (r a10_ix1) */ 
  2    r.name, r.shape 
  3  FROM road r
  4  WHERE st_envintersects
  5    (r.shape,-119.34,27.97,-109.80,37.37) = 1;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         | 71702 |    23M|  9614   (2)| 00:01:56 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ROAD    | 71702 |    23M|  9614   (2)| 00:01:56 |
|*  2 |   DOMAIN INDEX (Sel: 100)   | A10_IX1 |       |       |  4834   (3)| 00:00:59 |
---------------------------------------------------------------------------------------

For additional information on how to use optimizer hints in a SQL statement, refer to the Oracle documentation.

Article ID:000009237

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Set an Oracle hint in a SQL statement to use the St_Spatial_Index

Summary

Procedure

Discover more on this topic