Using Optimizer Hints

Optimizer hints are a means to indicate how the query engine should evaluate specific operations of a query plan statement. For example, you may know, based on the data in a given column, and the predicate(s) applied to this column, that scanning a certain index may be faster than another which was chosen automatically by the optimizer. This may be the case when database statistics are too old or missing. Optimizer hints are embedded in the SQL query, right after the SELECT keyword. The format of optimizer hints is as follows:

/*+ 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 ] )
HASHMATERIALIZE [ ( [schema.]table ) ]
NO_HASHMATERIALIZE [ ( [schema.]table ) ]
REOPTIMIZE { ALWAYS | NEVER }

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 an index scan operation that is reading data in ROWID order. KEY_ORDERED refers to an index scan operation 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.

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.

  • If the hint specifies a single index, then the optimizer uses that index. The optimizer does not consider any other index or indexes for the table.

  • If the hint specifies multiple indexes, then the optimizer chooses an optimal index plan based on the specified indexes. The optimizer does not guarantee that all specified indexes are going to be used in the plan.

  • If the hint specifies no indexes, then the optimizer chooses an optimal index plan based on all available indexes over the table. The optimizer does not consider a full scan over the table.

  • If the hint specifies a scan type (ID_ORDERED or KEY_ORDERED) for an index, then the optimizer considers that index for that type of scan only. ID_ORDERED refers to an index scan that is reading data in ROWID order. KEY_ORDERED refers to a query that is reading data in index key order.
    For example, if the hint specifies ‘KEY_ORDERED foo_a’, then the optimizer will use the foo_a index when it is doing a key ordered scan for ORDER BY or GROUP BY optimization.

  • If the hint does not specify a scan type (ID_ORDERED or KEY_ORDERED) for an index, then the optimizer considers that index for both key ordered and id ordered query plans,.
    Example: /*+ USE_INDEX(foo, foo_a) */

  • If the index_list specifies both ID_ORDERED and KEY_ORDERED specifications for an index, then the optimizer considers that index for both key ordered and id ordered query plans.
    Examples:
    /+ USE_INDEX(foo, ID_ORDERED foo_a, KEY_ORDERED foo_a) */
    /    + USE_INDEX(foo, ID_ORDERED foo_a), USE_INDEX(foo, KEY_ORDERED foo_a) */

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

See a full example of USE_INDEX in SELECT

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.

  • If the hint specifies a single index, then the optimizer does not consider that index. The optimizer considers the other indexes on the table.

  • If the hint specifies multiple indexes, then the optimizer does not consider any of the specified indexes. The optimizer still considers other indexes that are not specified.

  • If the hint specifies no indexes, the optimizer does not consider any of the indexes on the table. The optimizer will choose a full scan for the table.

  • If the hint specifies a scan type (KEY_ORDERED or ID_ORDERED) for an index, then the optimizer does not consider that index for that type of scan only. ID_ORDERED refers to an index scan that is reading data ROWID order. KEY_ORDERED refers to an index scan that is reading data in index key order.
    For example, if the hint specifies ‘ID_ORDERED foo_a’, the optimizer still considers foo_a while choosing a plan in the context of ORDER BY or GROUP BY optimization.

  • If the hint does not specify a scan type for an index, then the optimizer does not consider that index for both id ordered and key ordered query plans.
    Example: /*+ SKIP_INDEX(foo, foo_a) */

  • If the hint specifies both KEY_ORDERED and ID_ORDERED for an index, then the optimizer does not consider that index for both id ordered and key ordered query plans.
    Examples:
    /+ SKIP_INDEX(foo, KEY_ORDERED foo_a, ID_ORDERED foo_a) */
    /+ SKIP_INDEX(foo, KEY_ORDERED foo_a), SKIP_INDEX(foo, ID_ORDERED foo_a) */ )

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

See a full example of SKIP_INDEX in SELECT

NO_MERGE Hint

The NO_MERGE hint prevents the optimizer from merging derived queries or views in an OUTER JOIN.

Example 1

CREATE VIEW V1 AS
SELECT /*+ NO_MERGE */
FROM T2
WHERE F3 = FALSE;

SELECT F1, F2
FROM T1 LEFT OUTER JOIN (SELECT * FROM V1) T2 ON T1.F2 = T2.F2
WHERE T1.F3 = FALSE

Example 2

SELECT F1, F2
FROM T1
LEFT OUTER JOIN (SELECT /*+ NO_MERGE */ * FROM T2 WHERE T2.F3 = FALSE) T2 ON T1.F2 = T2.F2
WHERE T1.F3 = FALSE;

HASHMATERIALIZE and NO_HASHMATERIALIZE Hints

The HASHMATERIALIZE hint is useful for equi-joins when at least one of the tables doesn’t have a suitable index on the join column(s). This could be either a base or an unmerged derived table. The hint instructs the query optimizer to materialize the table and create a hash-index on the join column(s). The resulting join operation is called Hash-join.

Correspondingly, the NO_HASHMATERIALIZE hint disables the hash-join strategy for the table it references.

Example 1

Base tables are referenced as the only argument of the hint inside the SELECT clause of the query.

explain select /*+hashmaterialize(t1)*/ * from t1, t2 where t1.f1 = t2.f2;

 Project F1, F2, F1, F2
  Nested Loop Join (cost: 4903.0, est. rows: 1700)
    Table scan T2 (cost: 1201.0, est. rows: 100)
    Hash lookup on (T1.F1 = T2.F2) (cost: 3602.0, est. rows: 17)
      Materialize stream
        Table scan T1 (cost: 3601.0, est. rows: 300)

Example 2

The hint can be used inside a derived table without an argument. In this case it refers to the derived table itself, so that it is materialized in the parent query.

explain select * from (select /*+hashmaterialize*/ f1, f2 from t1 group by f1, f2) t1, t2 where t1.f1 = t2.f2;

 Project F1, F2, F1, F2
  Nested Loop Join (cost: 4903.0, est. rows: 30000)
    Table scan T2 (cost: 1201.0, est. rows: 100)
    Hash lookup on (T1.F1 = T2.F2) (cost: 3602.0, est. rows: 300)
      Materialize stream
        StreamedGrouping keys { T1.F1, T1.F2 } (cost: 3601.0, est. rows: 300)
          Sort T1.F1 ASC T1.F2 ASC
            Table scan T1 (cost: 3601.0, est. rows: 300)

REOPTIMIZE Hint

REOPTIMIZE { ALWAYS | NEVER }

The REOPTIMIZE hint can be used to force the optimizer to perform a full reoptimization on each execution of a query. A full reoptimization may help to improve the query plan generated by the optimizer in cases where user parameters or variables are used in the query and when the value of the parameters or variables can significantly influence the chosen query plan.

The optimizer will automatically reoptimize, without using a hint, when all of the following conditions are true:

  1. A user parameter or variable is used in the query

  2. An index scan is considered by the optimizer

  3. A table scan is chosen instead

This automatic reoptimization can be disabled by using the REOPTIMIZE NEVER hint.

Example

create table t1 (f1 int primary key, f2 string);
insert into t1 values (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five');

explain (analyze on) select /*+ REOPTIMIZE ALWAYS */ * from t1 where f1 < ?;
Enter value: 6
 Project T1.F1, T1.F2 (actual rows: 5, time: 37us, batches: 2)
  Filter (CAST(T1.F1 AS numeric(38,*)) < 6) (actual rows: 5, time: 35us, batches: 2)
    Table scan T1 (cost: 71.0, est. rows: 5, actual rows: 5 in 1 record, 1 data atoms (imported 0 (0KB)), time: 26us, batches: 1)

explain (analyze on) select /*+ REOPTIMIZE NEVER */ * from t1 where f1 < ?;
Enter value: 6
 Project T1.F1, T1.F2 (actual rows: 5, time: 46us, batches: 2)
  Record fetch T1 (cost: 33.4, est. rows: 2, actual rows: 5 in 1 record, 1 data atoms (imported 0 (0KB)), time: 46us, batches: 2)
    Bitmap filter
      Bitmap scan T1..PRIMARY_KEY (F1[-inf:<?:1]) (cost: 4.4, est. keys: 2, actual keys: 5 in 1 record, 1 index atoms (imported 0 (0KB)), time: 29us)