What are the hints and what are the different types of hints (INTERVIEW Question)
Ans: Hints(Optimizer hints) are used to alter the execution plan of the sql statments.
We
should first get the explain plan of our SQL and determine what changes
can be done to make the code operate without using hints if possible.
However, Oracle hints such as ORDERED, LEADING, INDEX, FULL, and the
various AJ and SJ Oracle hints can tame a wild optimizer and give you
optimal 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.
The following syntax is used for hints:
select /*+ HINT */ name
from emp
where id =1;
Where HINT is replaced by the hint text.
When the syntax of the hint text is incorrect, the hint text is ignored and will not be used.
here you can find undocumented hints.
Hints for Optimization Approaches and Goals
ALL_ROWS
The ALL_ROWS hint explicitly chooses the cost-based approach to
optimize a statement block with a goal of best throughput (that is,
minimum total resource consumption).
FIRST_ROWS 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 you should give a parameter
with this hint: FIRST_ROWS(n) means that the optimizer will determine an
executionplan to give a fast response for returning the first n rows.
CHOOSE
The CHOOSE hint causes the optimizer to choose between the rule-based
approach and the cost-based approach for a SQL statement based on the
presence of statistics for the tables accessed by the statement
RULE
The RULE hint explicitly chooses rule-based optimization for a
statement block. This hint also causes the optimizer to ignore any other
hints specified for the statement block. The RULE hint does not work
any more in Oracle 10g.
Hints for Access Paths
FULL The
FULL hint explicitly chooses a full table scan for the specified table.
The syntax of the FULL hint is FULL(table) where table specifies the
alias of the table (or table name if alias does not exist) on which the
full table scan is to be performed.
ROWID The ROWID hint
explicitly chooses a table scan by ROWID for the specified table. The
syntax of the ROWID hint is ROWID(table) where 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)
CLUSTER The
CLUSTER hint explicitly chooses a cluster scan to access the specified
table. The syntax of the CLUSTER hint is CLUSTER(table) 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 scan.
HASH_AJ 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(table) where table specifies the name or
alias of the table to be accessed.(depricated in Oracle 10g)
INDEX
The INDEX hint explicitly chooses an index scan for the specified
table. The syntax of the INDEX hint is INDEX(table index) where:table
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:
NO_INDEX
The NO_INDEX hint explicitly disallows a set of indexes for the
specified table. The syntax of the NO_INDEX hint is NO_INDEX(table
index)
INDEX_ASC The INDEX_ASC hint explicitly chooses an index
scan for the specified table. If the statement uses an index range scan,
Oracle scans the index entries in ascending order of their indexed
values.
INDEX_COMBINE 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 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).
INDEX_JOIN
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 of indexes must exist that contain all the columns required to
resolve the query.
INDEX_DESC The INDEX_DESC hint explicitly
chooses an index scan for the specified table. If the statement uses an
index range scan, Oracle scans the index entries in descending order of
their indexed values.
INDEX_FFS This hint causes a fast full index scan to be performed rather than a 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)
NO_INDEX_SS
The NO_INDEX_SS hint causes the optimizer to exclude a skip scan of
the specified indexes on the specified table. (from Oracle 10g)
Hints for Query Transformations
NO_QUERY_TRANSFORMATION Prevents the optimizer performing query transformations. (from Oracle 10g)
USE_CONCAT
The USE_CONCAT hint forces combined OR conditions in the WHERE clause
of a query to be transformed into a compound query 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.
NO_EXPAND The NO_EXPAND hint prevents the optimizer from
considering OR-expansion for queries having OR conditions or IN-lists
in the WHERE clause. Usually, the optimizer considers using OR expansion
and uses this method if it decides that the cost is lower than not
using it.
REWRITE The REWRITE hint forces the optimizer to
rewrite a query in terms of materialized views, when possible, without
cost consideration. Use the 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.
NOREWRITE
/ NO_REWRITE In Oracle 10g renamed to NO_REWRITE. The
NOREWRITE/NO_REWRITE hint disables query rewrite for the query block,
overriding the setting of the parameter QUERY_REWRITE_ENABLED.
MERGE The MERGE hint lets you merge views in a query.
NO_MERGE
The NO_MERGE hint causes Oracle not to merge mergeable views. This
hint is most often used to reduce the number of possible permutations
for a query and make optimization faster.
FACT The FACT hint
indicated that the table should be considered as a fact table. This is
used in the context of the star transformation.
NO_FACT The
NO_FACT hint is used in the context of the star transformation to
indicate to the transformation that the hinted table should not be
considered as a fact table.
STAR_TRANSFORMATION The
STAR_TRANSFORMATION hint makes the optimizer use the best plan in which
the transformation has been used. Without the hint, 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 blocks.
Hints for Join Orders
LEADING
Give this hint to indicate the leading table in a join. This will
indicate only 1 table. If you want to specify the whole order of tables,
you can use the ORDERED hint. Syntax: LEADING(table)
ORDERED
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 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
USE_NL
The USE_NL hint causes Oracle to join each specified table to another
row source with a nested loops join using the specified table 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)
USE_MERGE
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
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)
USE_HASH
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 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 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.
NOPARALLEL / NO_PARALLEL The NOPARALLEL
hint allows you to disable parallel scanning of a table, even if the
table was created with a PARALLEL clause. In Oracle 10g this hint was
renamed to NO_PARALLEL.
PQ_DISTRIBUTE The PQ_DISTRIBUTE hint
improves the performance of parallel join operations. Do this by
specifying how rows of joined tables should be distributed among
producer and consumer query servers. Using this hint overrides decisions
the optimizer would normally make.
NO_PARALLEL_INDEX The
NO_PARALLEL_INDEX hint overrides a PARALLEL attribute setting on an
index to avoid a parallel index scan operation.
Additional Hints
APPEND
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
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.
CACHE
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
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.
NOCACHE 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
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.
NO_PUSH_PRED The NO_PUSH_PRED hint prevents pushing of a join predicate into the view.
PUSH_SUBQ The PUSH_SUBQ hint causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan.
NO_PUSH_SUBQ The NO_PUSH_SUBQ hint causes non-merged subqueries to be evaluated as the last step in the execution plan.
QB_NAME Specifies a name for a query block. (from Oracle 10g)
CURSOR_SHARING_EXACT
Oracle can replace literals in SQL statements with bind variables, if
it is safe to do so. This is controlled with the CURSOR_SHARING 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.
DRIVING_SITE
The DRIVING_SITE hint forces query execution to be done for the table
at a different site than that selected by Oracle
DYNAMIC_SAMPLING
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 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.
SPREAD_MIN_ANALYSIS This hint omits some of the compile
time optimizations of the rules, mainly detailed dependency graph
analysis, on spreadsheets. Some 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
MERGE_AJ
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 hint is
MERGE_AJ(table) where table specifies the name or alias of the table to
be accessed.(depricated in Oracle 10g)
AND_EQUAL 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 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)
STAR The STAR hint forces the
large table to be joined last using a nested loops join on the index.
The optimizer will consider different permutations of the small tables.
(depricated in Oracle 10g)
BITMAP Usage: BITMAP(table_name index_name) Uses a bitmap index to 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 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)
ORDERED_PREDICATES (depricated in Oracle 10g)
EXPAND_GSET_TO_UNION (depricated in Oracle 10g)
