Set a SQL hint to instruct the Oracle optimizer in choosing an execution plan

Summary

An Oracle hint provides directive to the optimizer in choosing an execution plan for the SQL statement being executed.

The Oracle INDEX hint instructs the optimizer to use an index scan for the specified table. Use the INDEX hint for function-based, domain, B-tree, bitmap, and bitmap join indexes.

When working with tables containing ST_Geometry attributes and a st_spatial_index, specify the Oracle INDEX hint and the name of the st_spatial_index to instruct the optimizer to access the data by way of the index.

As stated in Oracle's SQL Reference documentation, ('If the INDEX hint specifies a single available index, then the database performs a scan on this index. The optimizer does not consider a full table scan or a scan of another index on the table.') when an INDEX hint is specified the optimizer uses the index as the primary access path.

Note:
The optimizer in Oracle 12c can override this type of hint.

Procedure

The following example demonstrates a SQL statement which is querying the parcels table with two predicate filters, where owner equals 'ARATA' and where the envelope intersects a specific area.

The parcels table has an index on the shape and owner attributes. By including the INDEX hint "/*+ INDEX (parcels shape_idx) */"
within the SQL statement, it instructs the optimizer to use the shape's index as the access path (even if the selectivity and cost of the owner index is less than the cost of using the shape index).

SQL> SELECT /*+ INDEX (parcels shape_idx) */
  2  FROM parcels
  3  WHERE owner = 'ARATA'
  4  AND st_envintersects(shape, 10, 12, 12, 14) = 1;

For additional information on setting Oracle hints and other hints please see Oracle's documentation.