Using Optimizer Hints

Optimizer hints are a means to indicate how the optimizer should evaluate the SELECT statement. For example, you may know, based on the data in a given column, that one index may be more selective than another for the particular query you are executing. An optimizer hint is useful in this situation. An optimizer hint is embedded in the SQL query, right after the SELECT keyword. It has the following format:

/*+ hint[,hint]... */

Where the following values (case-insensitive) are allowed to substitute for hint:

ORDERED
USE_INDEX ( [schema.]table [ , index_list ] ) SKIP_INDEX ( [schema.]table [ , index_list ] )

table is the name of a table or its alias, as specified in the AS alias clause of the SELECT statement. The table name may optionally be schema qualified. If a table has an alias then all index hints on that table must reference the alias.

index_list is a list of one or more index specifications separated by commas, where each index specification has the format:

[ ID_ORDERED ] index_name
[ KEY_ORDERED ] index_name

ID_ORDERED and KEY_ORDERED designate the scan type for the index. ID_ORDERED refers to a query that is reading data that corresponds to the range specified over a given index in ROWID order. KEY_ORDERED refers to a query that is reading data in index key order.

If you specify conflicting hints (for example, /*+ USE_INDEX(foo, foo_a), SKIP_INDEX(foo, foo_a) */), where foo is a table and foo_a is an index on a column in table foo, an error will be thrown.

If you specify an optimizer hint in a view or derived table, then the view or derived table will not be merged with the outer query results. For example, if you have the query:

SELECT * FROM (SELECT * FROM foo WHERE foo.a = 12);

In this case, we merge the plan for the inner select query (the derived table) with the plan for the outer select query, and then process the resulting query plan. If the inner select query contains hints as below, then the query blocks won't be merged.

SELECT * FROM (SELECT /*+ USE_INDEX(foo) */ * FROM foo WHERE foo.a = 12);

The difference between processing queries with or without merging is that they will have different query plans and may result in different run times.

ORDERED Hint

The ORDERED hint tells the optimizer to join tables in the order that they are specified in the FROM clause.

Example:

SELECT /*+ ORDERED */ COUNT(*) 
FROM t1, t2, t4, t3 WHERE t1.col = t2.col AND t1.col = t3.col AND t1.col = t4.col;

USE_INDEX Hint

USE_INDEX ( [schema.]table [ , index_list ] )

Note: You can use [schema.]table or you may need to use table alias. This is needed if you do a join between the same table more than once.

The USE_INDEX hint tells the optimizer to use an index (or a set of indexes) for a table.

In the examples above, foo is a table and foo_a is an index on a column in table foo.

SKIP_INDEX Hint

SKIP_INDEX ( [schema.]table [ , index_list ] )

Note: You can use [schema.]table or you may need to use table alias. This is needed if you do a join between the same table more than once.

The SKIP_INDEX hint tells the optimizer to not use an index (or a set of indexes) for a table.

In the examples above, foo is a table and foo_a is an index on a column in table foo.

See SELECT for examples.