1

How to use hints in Oracle sql for performance

With hints one can influence the optimizer. The usage of hints (with exception of the RULE-hint) causes
Oracle to use the Cost Based optimizer.

Hints for Optimization Approaches and Goals

The ALL_ROWS hint explicitly chooses the cost-based approach to
ALL_ROWS optimize a statement block with a goal of best throughput (that is,
minimum total resource consumption).

The FIRST_ROWS hint explicitly chooses the cost-based approach to

optimize a statement block with a goal of best response time
(minimum resource usage to return first row). In newer Oracle version
FIRST_ROWS
you should give a parameter with this hint: FIRST_ROWS(n) means
that the optimizer will determine an execution plan to give a fast
response for returning the first n rows.

The CHOOSE hint causes the optimizer to choose between the rule-
based approach and the cost-based approach for a SQL statement
CHOOSE
based on the presence of statistics for the tables accessed by the
statement

The RULE hint explicitly chooses rule-based optimization for a

statement block. This hint also causes the optimizer to ignore any
RULE
other hints specified for the statement block. The RULE hint does not
work any more in Oracle 10g.

Hints for Access Paths

The FULL hint explicitly chooses a full table scan for the specified
table. The syntax of the FULL hint is FULL(table) where table
FULL
specifies the alias of the table (or table name if alias does not exist) on
which the full table scan is to be performed.

The ROWID hint explicitly chooses a table scan by ROWID for the
specified table. The syntax of the ROWID hint is ROWID(table) where
ROWID table specifies the name or alias of the table on which the table
access by ROWID is to be performed. (This hint depricated in Oracle
10g)

The CLUSTER hint explicitly chooses a cluster scan to access the

specified table. The syntax of the CLUSTER hint is CLUSTER(table)
CLUSTER
where table specifies the name or alias of the table to be accessed by
a cluster scan.

HASH The HASH hint explicitly chooses a hash scan to access the specified
table. The syntax of the HASH hint is HASH(table) where table
specifies the name or alias of the table to be accessed by a hash

1
 2

scan.

The HASH_AJ hint transforms a NOT IN subquery into a hash anti-

join to access the specified table. The syntax of the HASH_AJ hint is
HASH_AJ
HASH_AJ(table) where table specifies the name or alias of the table
to be accessed.(depricated in Oracle 10g)

The INDEX hint explicitly chooses an index scan for the specified
table. The syntax of the INDEX hint is INDEX(table index) where:table
INDEX specifies the name or alias of the table associated with the index to be
scanned and index specifies an index on which an index scan is to be
performed. This hint may optionally specify one or more indexes:

The NO_INDEX hint explicitly disallows a set of indexes for the

NO_INDEX specified table. The syntax of the NO_INDEX hint is NO_INDEX(table
index)

The INDEX_ASC hint explicitly chooses an index scan for the

INDEX_ASC specified table. If the statement uses an index range scan, Oracle
scans the index entries in ascending order of their indexed values.

If no indexes are given as arguments for the INDEX_COMBINE hint,

the optimizer will use on the table whatever boolean combination of
bitmap indexes has the best cost estimate. If certain indexes are given
INDEX_COMBINE
as arguments, the optimizer will try to use some boolean combination
of those particular bitmap indexes. The syntax of INDEX_COMBINE is
INDEX_COMBINE(table index).

Explicitly instructs the optimizer to use an index join as an access

path. For the hint to have a positive effect, a sufficiently small number
INDEX_JOIN
of indexes must exist that contain all the columns required to resolve
the query.

The INDEX_DESC hint explicitly chooses an index scan for the

INDEX_DESC specified table. If the statement uses an index range scan, Oracle
scans the index entries in descending order of their indexed values.

This hint causes a fast full index scan to be performed rather than a
INDEX_FFS
full table.

NO_INDEX_FFS Do not use fast full index scan (from Oracle 10g)

INDEX_SS Exclude range scan from query plan (from Oracle 10g)

INDEX_SS_ASC Exclude range scan from query plan (from Oracle 10g)

INDEX_SS_DESC Exclude range scan from query plan (from Oracle 10g)

The NO_INDEX_SS hint causes the optimizer to exclude a skip scan

NO_INDEX_SS
of the specified indexes on the specified table. (from Oracle 10g)

2
 3

Hints for Query Transformations

Prevents the optimizer performing query transformations. (from Oracle
NO_QUERY_TRANSFORMATION
10g)

The USE_CONCAT hint forces combined OR conditions in the

WHERE clause of a query to be transformed into a compound query
USE_CONCAT using the UNION ALL set operator. Normally, this transformation
occurs only if the cost of the query using the concatenations is
cheaper than the cost without them.

The NO_EXPAND hint prevents the optimizer from considering OR-

expansion for queries having OR conditions or IN-lists in the WHERE
NO_EXPAND
clause. Usually, the optimizer considers using OR expansion and
uses this method if it decides that the cost is lower than not using it.

The REWRITE hint forces the optimizer to rewrite a query in terms of

materialized views, when possible, without cost consideration. Use the
REWRITE REWRITE hint with or without a view list. If you use REWRITE with a
view list and the list contains an eligible materialized view, then Oracle
uses that view regardless of its cost.

In Oracle 10g renamed to NO_REWRITE. The

NOREWRITE/NO_REWRITE hint disables query rewrite for the query
NOREWRITE / NO_REWRITE
block, overriding the setting of the parameter
QUERY_REWRITE_ENABLED.

MERGE The MERGE hint lets you merge views in a query.

The NO_MERGE hint causes Oracle not to merge mergeable views.

NO_MERGE This hint is most often used to reduce the number of possible
permutations for a query and make optimization faster.

The FACT hint indicated that the table should be considered as a fact
FACT
table. This is used in the context of the star transformation.

The NO_FACT hint is used in the context of the star transformation to

NO_FACT indicate to the transformation that the hinted table should not be
considered as a fact table.

The STAR_TRANSFORMATION hint makes the optimizer use the

best plan in which the transformation has been used. Without the hint,
STAR_TRANSFORMATION the optimizer could make a query optimization decision to use the best
plan generated without the transformation, instead of the best plan for
the transformed query.

NO_STAR_TRANSFORMATION Do not use star transformation (from Oracle 10g)

UNNEST The UNNEST hint specifies subquery unnesting.

NO_UNNEST Use of the NO_UNNEST hint turns off unnesting for specific subquery

3
 4

blocks.

Hints for Join Orders

Give this hint to indicate the leading table in a join. This will indicate
LEADING only 1 table. If you want to specify the whole order of tables, you can
use the ORDERED hint. Syntax: LEADING(table)

The ORDERED hint causes Oracle to join tables in the order in which
they appear in the FROM clause. If you omit the ORDERED hint from
a SQL statement performing a join , the optimizer chooses the order in
which to join the tables. You may want to use the ORDERED hint to
ORDERED
specify a join order if you know something about the number of rows
selected from each table that the optimizer does not. Such information
would allow you to choose an inner and outer table better than the
optimizer could.

Hints for Join Operations

The USE_NL hint causes Oracle to join each specified table to
another row source with a nested loops join using the specified table
USE_NL as the inner table. The syntax of the USE_NL hint is USE_NL(table
table) where table is the name or alias of a table to be used as the
inner table of a nested loops join.

NO_USE_NL Do not use nested loop (from Oracle 10g)

USE_NL_WITH_INDEX Specifies a nested loops join. (from Oracle 10g)

The USE_MERGE hint causes Oracle to join each specified table with
another row source with a sort-merge join. The syntax of the
USE_MERGE USE_MERGE hint is USE_MERGE(table table) where table is a table
to be joined to the row source resulting from joining the previous
tables in the join order using a sort-merge join.

NO_USE_MERGE Do not use merge (from Oracle 10g)

The USE_HASH hint causes Oracle to join each specified table with
another row source with a hash join. The syntax of the USE_HASH
USE_HASH hint is USE_HASH(table table) where table is a table to be joined to
the row source resulting from joining the previous tables in the join
order using a hash join.

NO_USE_HASH Do not use hash (from Oracle 10g)

Hints for Parallel Execution

PARALLEL The PARALLEL hint allows you to specify the desired number of
concurrent query servers that can be used for the query. The syntax is
PARALLEL(table number number). The PARALLEL hint must use the
table alias if an alias is specified in the query. The PARALLEL hint can
then take two values separated by commas after the table name. The

4
 5

first value specifies the degree of parallelism for the given table, the
second value specifies how the table is to be split among the
instances of a parallel server. Specifying DEFAULT or no value
signifies the query coordinator should examine the settings of the
initialization parameters (described in a later section) to determine the
default degree of parallelism.

The NOPARALLEL hint allows you to disable parallel scanning of a

NOPARALLEL / NO_PARALLEL table, even if the table was created with a PARALLEL clause. In
Oracle 10g this hint was renamed to NO_PARALLEL.

The PQ_DISTRIBUTE hint improves the performance of parallel join

operations. Do this by specifying how rows of joined tables should be
PQ_DISTRIBUTE
distributed among producer and consumer query servers. Using this
hint overrides decisions the optimizer would normally make.

The NO_PARALLEL_INDEX hint overrides a PARALLEL attribute

NO_PARALLEL_INDEX
setting on an index to avoid a parallel index scan operation.

Additional Hints

When the APPEND hint is used with the INSERT statement, data is
appended to the table. Existing free space in the block is not used. If a
APPEND table or an index is specified with nologging, this hint applied with an
insert statement produces a direct path insert which reduces
generation of redo.

NOAPPEND Overrides the append mode.

The CACHE hint specifies that the blocks retrieved for the table in the
hint are placed at the most recently used end of the LRU list in the
CACHE buffer cache when a full table scan is performed. This option is useful
for small lookup tables. In the following example, the CACHE hint
overrides the table default caching specification.

The NOCACHE hint specifies that the blocks retrieved for this table
are placed at the least recently used end of the LRU list in the buffer
NOCACHE
cache when a full table scan is performed. This is the normal behavior
of blocks in the buffer cache.

PUSH_PRED The PUSH_PRED hint forces pushing of a join predicate into the view.

The NO_PUSH_PRED hint prevents pushing of a join predicate into

NO_PUSH_PRED
the view.

The PUSH_SUBQ hint causes nonmerged subqueries to be evaluated

PUSH_SUBQ
at the earliest possible place in the execution plan.

The NO_PUSH_SUBQ hint causes non-merged subqueries to be

NO_PUSH_SUBQ
evaluated as the last step in the execution plan.

QB_NAME Specifies a name for a query block. (from Oracle 10g)

5
 6

Oracle can replace literals in SQL statements with bind variables, if it

is safe to do so. This is controlled with the CURSOR_SHARING
CURSOR_SHARING_EXACT startup parameter. The CURSOR_SHARING_EXACT hint causes this
behavior to be switched off. In other words, Oracle executes the SQL
statement without any attempt to replace literals by bind variables.

The DRIVING_SITE hint forces query execution to be done for the

DRIVING_SITE
table at a different site than that selected by Oracle

The DYNAMIC_SAMPLING hint lets you control dynamic sampling to

improve server performance by determining more accurate predicate
selectivity and statistics for tables and indexes. You can set the value
DYNAMIC_SAMPLING of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the
level, the more effort the compiler puts into dynamic sampling and the
more broadly it is applied. Sampling defaults to cursor level unless
you specify a table.

This hint omits some of the compile time optimizations of the rules,
mainly detailed dependency graph analysis, on spreadsheets. Some
SPREAD_MIN_ANALYSIS optimizations such as creating filters to selectively populate
spreadsheet access structures and limited rule pruning are still used.
(from Oracle 10g)

Hints with unknown status

The MERGE_AJ hint transforms a NOT IN subquery into a merge

anti-join to access the specified table. The syntax of the MERGE_AJ
MERGE_AJ
hint is MERGE_AJ(table) where table specifies the name or alias of
the table to be accessed.(depricated in Oracle 10g)

The AND_EQUAL hint explicitly chooses an execution plan that uses

an access path that merges the scans on several single-column
indexes. The syntax of the AND_EQUAL hint is AND_EQUAL(table
index index) where table specifies the name or alias of the table
AND_EQUAL
associated with the indexes to be merged. and index specifies an
index on which an index scan is to be performed. You must specify at
least two indexes. You cannot specify more than five. (depricated in
Oracle 10g)

The STAR hint forces the large table to be joined last using a nested
STAR loops join on the index. The optimizer will consider different
permutations of the small tables. (depricated in Oracle 10g)

Usage: BITMAP(table_name index_name) Uses a bitmap index to

BITMAP
access the table. (depricated ?)

HASH_SJ Use a Hash Anti-Join to evaluate a NOT IN sub-query. Use this hint in
the sub-query, not in the main query. Use this when your high volume
NOT IN sub-query is using a FILTER or NESTED LOOPS join. Try

6
 7

MERGE_AJ if HASH_AJ refuses to work.(depricated in Oracle 10g)

NL_SJ Use a Nested Loop in a sub-query. (depricated in Oracle 10g)

NL_AJ Use an anti-join in a sub-query. (depricated in Oracle 10g)