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

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

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

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