17
Optimizer Hints

Optimizer hints can be used with SQL statements to alter execution plans. This chapter explains how to use hints to force various approaches.

The chapter contains the following sections:

Understanding Optimizer Hints

Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know. Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the specific criteria.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.

See "Using Optimizer Hints" for the discussion of the types and usage of hints. The hints are grouped into the following categories:

Hints for Optimization Approaches and Goals
Hints for Access Paths
Hints for Query Transformations
Hints for Join Orders
Hints for Join Operations
Hints for Parallel Execution

Additional Hints

Note:

The use of hints involves extra code that must be managed, checked, and controlled.

See Also:

Chapter 6, "Automatic Performance Diagnostics" for information on analyzing and tuning SQL statements.
Oracle Enterprise Manager Concepts for information on monitoring and tuning with Oracle Enterprise Manager features

Type of Hints

Hints falls into the following general classifications:

Single-table

Single-table hints are specified on one table or view. INDEX and USE_NL are examples of single-table hints.
Multi-table

Multi-table hints are like single-table hints, except that the hint can specify one or more tables or views. LEADING is an example of a multi-table hint. Note that USE_NL(table1 table2) is not considered a multi-table hint because it is actually a shortcut for USE_NL(table1) and USE_NL(table2).
Query block

Query block hints operate on single query blocks. STAR_TRANSFORMATION and UNNEST are examples of query block hints.
Statement

Statement hints apply to the entire SQL statement. ALL_ROWS is an example of a statement hint.

Specifying Hints

Hints apply only to the optimization of the block of a statement in which they appear. A statement block is any one of the following statements or parts of statements:

A simple SELECT, UPDATE, or DELETE statement
A parent statement or subquery of a complex statement
A part of a compound query

For example, a compound query consisting of two component queries combined by the UNION operator has two blocks, one for each component query. For this reason, hints in the first component query apply only to its optimization, not to the optimization of the second component query.

The following sections discuss the use of hints in more detail.

Hint Syntax

You can send hints for a SQL statement to the optimizer by enclosing them in a comment within the statement.

See Also:

Oracle Database SQL Reference for more information on comments

A block in a statement can have only one comment containing hints following the SELECT, UPDATE, MERGE, or DELETE keyword.

Exception:

The APPEND hint always follows the INSERT keyword, and the PARALLEL hint can follow the INSERT keyword.

The following syntax shows hints contained in both styles of comments that Oracle supports within a statement block.

{DELETE|INSERT|MERGE|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

{DELETE|INSERT|MERGE|SELECT|UPDATE} --+ hint [text] [hint[text]]...

where:

DELETE, INSERT, SELECT, MERGE, and UPDATE are keywords that begin a statement block. Comments containing hints can appear only after these keywords.
+ causes Oracle to interpret the comment as a list of hints. The plus sign must immediately follow the comment delimiter; no space is permitted.
hint is one of the hints discussed in this section. If the comment contains multiple hints, then each hint must be separated from the others by at least one space.
text is other commenting text that can be interspersed with the hints.

The --+ hint format requires that the hint be on only one line.

If you specify hints incorrectly, then Oracle ignores them but does not return an error. For example:

Oracle ignores hints if the comment containing them does not follow a DELETE, INSERT, SELECT, MERGE, or UPDATE keyword.
Oracle ignores hints containing syntax errors, but considers other correctly specified hints within the same comment.
Oracle ignores combinations of conflicting hints, but considers other hints within the same comment.
Oracle ignores hints in all SQL statements in those environments that use PL/SQL version 1, such as Forms version 3 triggers, Oracle Forms 4.5, and Oracle Reports 2.5. These hints can be passed to the server, but the server ignores them.
See Also:

"Using Optimizer Hints" for the syntax of each hint

"INDEX" and following sections, for conditions specific to index type

Specifying a Full Set of Hints

When using hints, in some cases, you might need to specify a full set of hints in order to ensure the optimal execution plan. For example, if you have a very complex query, which consists of many table joins, and if you specify only the INDEX hint for a given table, then the optimizer needs to determine the remaining access paths to be used, as well as the corresponding join methods. Therefore, even though you gave the INDEX hint, the optimizer might not necessarily use that hint, because the optimizer might have determined that the requested index cannot be used due to the join methods and access paths selected by the optimizer.

In Example 17-1, the ORDERED hint specifies the exact join order to be used; the join methods to be used on the different tables are also specified.

Example 17-1 Specifying a Full Set of Hints

SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk) 
           USE_MERGE(j) FULL(j) */
    e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
  FROM employees e1, employees e2, job_history j
  WHERE e1.employee_id = e2.manager_id
    AND e1.employee_id = j.employee_id
    AND e1.hire_date = j.start_date
  GROUP BY e1.first_name, e1.last_name, j.job_id
  ORDER BY total_sal;

Specifying a Query Block in a Hint

To identify a query block in a query, an optional query block name can be used in a hint to specify the query block to which the hint applies. The syntax of the query block argument is of the form @queryblock, where queryblock is an identifier that specifies a query block in the query. The queryblock identifier can either be system-generated or user-specified.

The system-generated identifier can be obtained by using EXPLAIN PLAN for the query. Pre-transformation query block names can be determined by running EXPLAIN PLAN for the query using the NO_QUERY_TRANSFORMATION hint. See "NO_QUERY_TRANSFORMATION".
The user-specified name can be set with the QB_NAME hint. See "QB_NAME".

In Example 17-2, the query block name is used with the NO_UNNEST hint to specify a query block in a SELECT statement on the view.

Example 17-2 Using a Query Block in a Hint

CREATE OR REPLACE VIEW v AS
  SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
    FROM employees e1,
      ( SELECT *
          FROM employees e3) e2, job_history j
         WHERE e1.employee_id = e2.manager_id
          AND e1.employee_id = j.employee_id
          AND e1.hire_date = j.start_date
          AND e1.salary = ( SELECT max(e2.salary) 
FROM employees e2 
WHERE e2.department_id = e1.department_id ) 
  GROUP BY e1.first_name, e1.last_name, j.job_id
  ORDER BY total_sal;

After running EXPLAIN PLAN for the query and displaying the plan table output, you can determine the system-generated query block identifier. For example, a query block name is displayed in the following plan table output:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'SERIAL'));
...
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
...
  10 - SEL$4        / E2@SEL$4

After the query block name is determined it can be used in the following SQL statement:

SELECT /*+ NO_UNNEST( @SEL$4 ) */
  *
 FROM v;

Specifying Global Table Hints

Hints that specify a table generally refer to tables in the DELETE, SELECT, or UPDATE query block in which the hint occurs, not to tables inside any views referenced by the statement. When you want to specify hints for tables that appear inside views, Oracle recommends using global hints instead of embedding the hint in the view. Table hints described in this chapter can be transformed into a global hint by using an extended tablespec syntax that includes view names with the table name.

In addition, an optional query block name can precede the tablespec syntax. See "Specifying a Query Block in a Hint".

Hints that specify a table use the following syntax:

Figure 17-1 Tablespec Syntax

tablespec::=

Text description of the illustration tablespec.gif

where:

view specifies a view name
table specifies the name or alias of the table

If the view path is specified, the hint is resolved from left to right, where the first view must be present in the FROM clause, and each subsequent view must be specified in the FROM clause of the preceding view.

For example, in Example 17-3 a view v is created to return the first and last name of the employee, his or her first job and the total salary of all direct reports of that employee for each employee with the highest salary in his or her department. When querying the data, you want to force the use of the index emp_job_ix for the table e3 in view e2.

Example 17-3 Using Global Hints Example

CREATE OR REPLACE VIEW v AS
SELECT
    e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1,
        ( SELECT *
          FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
     AND e1.employee_id = j.employee_id
     AND e1.hire_date = j.start_date
     AND e1.salary = ( SELECT
                       max(e2.salary)
                       FROM employees e2
                       WHERE e2.department_id = e1.department_id)
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;

By using the global hint structure, you can avoid the modification of view v with the specification of the index hint in the body of view e2. To force the use of the index emp_job_ix for the table e3, you can use one of the following:

SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */  * 
  FROM v;

SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ * 
  FROM v;

SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ * 
  FROM v;

The global hint syntax also applies to unmergeable views as in Example 17-4.

Example 17-4 Using Global Hints with NO_MERGE

CREATE OR REPLACE VIEW v1 AS
  SELECT *
    FROM employees
    WHERE employee_id < 150;

CREATE OR REPLACE VIEW v2 AS
  SELECT v1.employee_id employee_id, departments.department_id department_id
    FROM v1, departments
    WHERE v1.department_id = departments.department_id;

SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.employees emp_emp_id_pk)
                        FULL(v2.departments) */ *
  FROM v2
  WHERE department_id = 30;

The hints cause v2 not to be merged and specify access path hints for the employee and department tables. These hints are pushed down into the (nonmerged) view v2.

See Also:

"Using Hints with Views"

Specifying Complex Index Hints

Hints that specify an index can use either a simple index name or a parenthesized list of columns as follows:

Figure 17-2 Indexspec Syntax

indexspec::=

Text description of the illustration indexspec.gif

where:

table specifies the name
column specifies the name of a column in the specified table
- The columns can optionally be prefixed with table qualifiers allowing the hint to specify bitmap join indexes where the index columns are on a different table than the indexed table. If tables qualifiers are present, they must be base tables, not aliases in the query.
- Each column in an index specification must be a base column in the specified table, not an expression. Function-based indexes cannot be hinted using a column specification unless the columns specified in the index specification form the prefix of a function-based index.
index specifies an index name

The hint is resolved as follows:

If an index name is specified, only that index is considered.
If a column list is specified and an index exists whose columns match the specified columns in number and order, only that index is considered. If no such index exists, then any index on the table with the specified columns as the prefix in the order specified is considered. In either case, the behavior is exactly as if the user had specified the same hint individually on all the matching indexes.

For example, in Example 17-3 the job_history table has a single-column index on the employee_id column and a concatenated index on employee_id and start_date columns. To use either of these indexes, the query can be hinted as follows:

SELECT /*+ INDEX(v.j jhist_employee_ix (employee_id start_date)) */ * FROM v;

Using Hints with Views

Oracle does not encourage the use of hints inside or on views (or subqueries). This is because you can define views in one context and use them in another. Also, such hints can result in unexpected execution plans. In particular, hints inside views or on views are handled differently, depending on whether the view is mergeable into the top-level query.

If you want to specify a hint for a table in a view or subquery, then the global hint syntax is recommended. See "Specifying Global Table Hints".

If you decide, nonetheless, to use hints with views, the following sections describe the behavior in each case.

Hints and Complex Views

By default, hints do not propagate inside a complex view. For example, if you specify a hint in a query that selects against a complex view, then that hint is not honored, because it is not pushed inside the view.

Note:

If the view is a single-table, then the hint is not propagated.

Unless the hints are inside the base view, they might not be honored from a query against the view.

Hints and Mergeable Views

This section describes hint behavior with mergeable views.

Optimization Approaches and Goal Hints in Views

Optimization approach and goal hints can occur in a top-level query or inside views.

If there is such a hint in the top-level query, then that hint is used regardless of any such hints inside the views.
If there is no top-level optimizer mode hint, then mode hints in referenced views are used as long as all mode hints in the views are consistent.
If two or more mode hints in the referenced views conflict, then all mode hints in the views are discarded and the session mode is used, whether default or user-specified.

Access Path and Join Hints on Views

Access path and join hints on referenced views are ignored, unless the view contains a single table (or references an Additional Hints view with a single table). For such single-table views, an access path hint or a join hint on the view applies to the table inside the view.

Access Path and Join Hints Inside Views

Access path and join hints can appear in a view definition.

If the view is an inline view (that is, if it appears in the FROM clause of a SELECT statement), then all access path and join hints inside the view are preserved when the view is merged with the top-level query.
For views that are non-inline views, access path and join hints in the view are preserved only if the referencing query references no other tables or views (that is, if the FROM clause of the SELECT statement contains only the view).

Parallel Execution Hints on Views

PARALLEL, NO_PARALLEL, PARALLEL_INDEX, and NO_PARALLEL_INDEX hints on views are applied recursively to all the tables in the referenced view. Parallel execution hints in a top-level query override such hints inside a referenced view.

Parallel Execution Hints Inside Views

PARALLEL, NO_PARALLEL, PARALLEL_INDEX, and NO_PARALLEL_INDEX hints inside views are preserved when the view is merged with the top-level query. Parallel execution hints on the view in a top-level query override such hints inside a referenced view.

Hints and Nonmergeable Views

With nonmergeable views, optimization approach and goal hints inside the view are ignored; the top-level query decides the optimization mode.

Because nonmergeable views are optimized separately from the top-level query, access path and join hints inside the view are preserved. For the same reason, access path hints on the view in the top-level query are ignored.

However, join hints on the view in the top-level query are preserved because, in this case, a nonmergeable view is similar to a table.

Using Optimizer Hints

This section discusses how to use the optimizer hints. The hints can be categorized as follows:

Hints for Optimization Approaches and Goals

The hints described in this section let you choose between optimization approaches and goals.

If a SQL statement has a hint specifying an optimization approach and goal, then the optimizer uses the specified approach regardless of the presence or absence of statistics, the value of the OPTIMIZER_MODE initialization parameter, and the OPTIMIZER_MODE parameter of the ALTER SESSION statement.

Note:

The optimizer goal applies only to queries submitted directly. Use hints to specify the access path for any SQL statements submitted from within PL/SQL. The ALTER SESSION... SET OPTIMIZER_MODE statement does not affect SQL that is run from within PL/SQL.

If you specify either the ALL_ROWS or the FIRST_ROWS(n) hint in a SQL statement, and if the data dictionary does not have statistics about tables accessed by the statement, then the optimizer uses default statistical values, such as allocated storage for such tables, to estimate the missing statistics and to subsequently choose an execution plan. These estimates might not be as accurate as those gathered by the DBMS_STATS package, so you should use the DBMS_STATS package to gather statistics.

If you specify hints for access paths or join operations along with either the ALL_ROWS or FIRST_ROWS(n) hint, then the optimizer gives precedence to the access paths and join operations specified by the hints.

See "Optimization Approaches and Goal Hints in Views" for hint behavior with mergeable views.

ALL_ROWS

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

all_rows_hint::=

Text description of the illustration all_rows_hint.gif

For example, the optimizer uses the query optimization approach to optimize this statement for best throughput:

SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
  FROM employees
  WHERE employee_id = 7566;

FIRST_ROWS(n)

The FIRST_ROWS(n) hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first n rows most efficiently.

first_rows_hint::=

Text description of the illustration first_rows_hint.gif

where integer specifies the number of rows to return.

For example, the optimizer uses the query optimization approach to optimize this statement for best response time:

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
  FROM employees
  WHERE department_id = 20;

In this example each department contains many employees. The user wants the first 10 employees of department 20 to be displayed as quickly as possible.

The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following syntax:

Set operators (UNION, INTERSECT, MINUS, UNION ALL)
GROUP BY clause
FOR UPDATE clause
Aggregate functions
DISTINCT operator
ORDER BY clauses, when there is no index on the ordering columns

These statements cannot be optimized for best response time, because Oracle must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any of these statements, then the optimizer uses the query optimization approach and optimizes for best throughput.

Note:

The FIRST_ROWS hint, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability.

RULE

The RULE hint disables the use of the query optimizer. This hint is unsupported and should not be used.

rule_hint::=

Text description of the illustration rule_hint.gif

Hints for Access Paths

Each hint described in this section suggests an access path for a table.

Specifying one of these hints causes the optimizer to choose the specified access path only if the access path is available based on the existence of an index or cluster and on the syntactic constructs of the SQL statement. If a hint specifies an unavailable access path, then the optimizer ignores it.

You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. The table name within the hint should not include the schema name if the schema name is present in the statement.

See "Access Path and Join Hints on Views" and "Access Path and Join Hints Inside Views" for hint behavior with mergeable views.

Note:

For access path hints, Oracle ignores the hint if you specify the SAMPLE option in the FROM clause of a SELECT statement.

See Also:

Oracle Database SQL Reference for more information on the SAMPLE option

FULL

The FULL hint explicitly chooses a full table scan for the specified table.

full_hint::=

Text description of the illustration full_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ FULL(e) */ employee_id, last_name
  FROM employees e 
  WHERE last_name LIKE :b1;

Oracle performs a full table scan on the employees table to execute this statement, even if there is an index on the last_name column that is made available by the condition in the WHERE clause.

Note:

Because the employees table has alias e the hint must refer to the table by its alias rather than by its name. Also, do not specify schema names in the hint even if they are specified in the FROM clause.

CLUSTER

The CLUSTER hint explicitly chooses a cluster scan to access the specified table. It applies only to clustered objects.

cluster_hint::=

Text description of the illustration cluster_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

HASH

The HASH hint explicitly chooses a hash scan to access the specified table. It applies only to tables stored in a cluster.

hash_hint::=

Text description of the illustration hash_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

INDEX

The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for the combination of multiple indexes, because it is a more versatile hint.

index_hint::=

Text description of the illustration index_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints". For a description of the indexspec syntax, see "Specifying Complex Index Hints".

This hint can optionally specify one or more indexes:

If this hint specifies a single available index, then the optimizer performs a scan on this index. The optimizer does not consider a full table scan or a scan on another index on the table.
If this hint specifies a list of available indexes, then the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The optimizer can also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan or a scan on an index not listed in the hint.
If this hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The optimizer can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.

For example:

SELECT /*+ INDEX (employees emp_department_ix)*/ 
       employee_id, department_id 
  FROM employees 
  WHERE department_id > 50;

NO_INDEX

The NO_INDEX hint explicitly disallows a set of indexes for the specified table.

no_index_hint::=

Text description of the illustration no_index_hint.gif

Each parameter serves the same purpose as in the INDEX hint with the following modifications:

If this hint specifies a single available index, then the optimizer does not consider a scan on this index. Other indexes not specified are still considered.
If this hint specifies a list of available indexes, then the optimizer does not consider a scan on any of the specified indexes. Other indexes not specified in the list are still considered.
If this hint specifies no indexes, then the optimizer does not consider a scan on any index on the table. This behavior is the same as a NO_INDEX hint that specifies a list of all available indexes for the table.

The NO_INDEX hint applies to function-based, B-tree, bitmap, cluster, or domain indexes. If a NO_INDEX hint and an index hint (INDEX, INDEX_ASC, INDEX_DESC, INDEX_COMBINE, or INDEX_FFS) both specify the same indexes, then both the NO_INDEX hint and the index hint are ignored for the specified indexes and the optimizer considers the specified indexes.

For example:

SELECT /*+ NO_INDEX(employees emp_empid) */ employee_id 
  FROM employees 
  WHERE employee_id > 200;

INDEX_ASC

The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values.

index_asc_hint::=

Text description of the illustration index_asc_hint.gif

Each parameter serves the same purpose as in the INDEX hint.

Because the default behavior for a range scan is to scan index entries in ascending order of their indexed values, this hint does not specify anything more than the INDEX hint. However, you might want to use the INDEX_ASC hint to specify ascending range scans explicitly should the default behavior change.

INDEX_COMBINE

The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever boolean combination of indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some boolean combination of those particular indexes.

index_combine_hint::=

Text description of the illustration index_combine_hint.gif

Each parameter serves the same purpose as in the INDEX hint.

For example:

SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *
  FROM employees e
  WHERE manager_id = 108
     OR department_id = 110;

INDEX_JOIN

The INDEX_JOIN hint 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_join_hint::=

Text description of the illustration index_join_hint.gif

Each parameter serves the same purpose as in the INDEX hint.

For example, the following query uses an index join to access the manager_id and department_id columns, both of which are indexed in the employees table.

SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id
  FROM employees e
  WHERE manager_id < 110
    AND department_id < 50;

INDEX_DESC

The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.

index_desc_hint::=

Text description of the illustration index_desc_hint.gif

Each parameter serves the same purpose as in the INDEX hint.

For example:

SELECT /*+ INDEX_DESC(e emp_name_ix) */ *
  FROM employees e;

INDEX_FFS

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

index_ffs_hint::=

Text description of the illustration index_ffs_hint.gif

Each parameter serves the same purpose as in the INDEX hint.

For example:

SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name
  FROM employees e;

See Also:

"Full Scans"

NO_INDEX_FFS

The NO_INDEX_FFS hint causes the optimizer to exclude a fast full index scan of the specified indexes on the specified table.

no_index_ffs_hint::=

Text description of the illustration no_index_ffs_hint.gif

Each parameter serves the same purpose as in the INDEX hint.

For example:

SELECT /*+ NO_INDEX_FFS(items item_order_ix) */ order_id
  FROM order_items items;

INDEX_SS

The INDEX_SS hint explicitly chooses an index skip scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition.

index_ss_hint::=

Text description of the illustration index_ss_hint.gif

Each parameter serves the same purpose as in the INDEX hint.

For example:

SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name
  FROM employees e
  WHERE first_name = 'Steven';

INDEX_SS_ASC

The INDEX_SS_ASC hint explicitly chooses an index skip scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition.

index_ss_asc_hint::=

Text description of the illustration index_ss_asc_hint.gif

Each parameter serves the same purpose as in the INDEX hint.

Because the default behavior for a range scan is to scan index entries in ascending order of their indexed values, this hint does not specify anything more than the INDEX_SS hint. However, you might want to use the INDEX_SS_ASC hint to specify ascending range scans explicitly should the default behavior change.

INDEX_SS_DESC

The INDEX_SS_DESC hint explicitly chooses an index skip scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.

index_ss_desc_hint::=

Text description of the illustration index_ss_desc_hint.gif

Each parameter serves the same purpose as in the INDEX hint.

For example:

SELECT /*+ INDEX_SS_DESC(e emp_name_ix) */ last_name
  FROM employees e
  WHERE first_name = 'Steven';

NO_INDEX_SS

The NO_INDEX_SS hint causes the optimizer to exclude a skip scan of the specified indexes on the specified table.

no_index_ss_desc_hint::=

Text description of no_index_ss_hint.gif follows

Text description of the illustration no_index_ss_hint.gif

Each parameter serves the same purpose as in the INDEX hint.

Hints for Query Transformations

Each hint described in this section suggests a SQL query transformation.

NO_QUERY_TRANSFORMATION

The NO_QUERY_TRANSFORMATION hint causes the optimizer to skip all query transformations including but not limited to OR expansion, view merging, subquery unnesting, star transformation and materialized view rewrite.

no_query_transformation::=

Text description of no_query_transformatn_hint.gif follows

Text description of the illustration no_query_transformatn_hint.gif

For example:

SELECT /*+ NO_QUERY_TRANSFORMATION */ employee_id, last_name
  FROM (SELECT *
        FROM employees e) v
  WHERE v.last_name = 'Smith';

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. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them; the USE_CONCAT hint overrides the cost consideration.

use_concat_hint::=

Text description of the illustration use_concat_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint".

For example:

SELECT /*+ USE_CONCAT */ *
  FROM employees e
  WHERE manager_id = 108
     OR department_id = 110;

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.

no_expand_hint::=

Text description of the illustration no_expand_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint".

For example:

SELECT /*+ NO_EXPAND */ *
  FROM employees e, departments d
  WHERE e.manager_id = 108
     OR d.department_id = 110;

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.

Oracle does not consider views outside of the list. If you do not specify a view list, then Oracle searches for an eligible materialized view and always uses it regardless of the cost of the final plan.

rewrite_hint::=

Text description of the illustration rewrite_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint".

See Also:

Oracle Database Concepts and Oracle Database Advanced Replication for more information on materialized views
Oracle Data Warehousing Guide for more information on using REWRITE with materialized views

NO_REWRITE

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

Note:

The NO_REWRITE hint disables the use of function-based indexes.

no_rewrite_hint::=

Text description of the illustration no_rewrite_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint".

For example:

SELECT /*+ NO_REWRITE */ sum(s.amount_sold) AS dollars
  FROM sales s, times t
  WHERE s.time_id = t.time_id
  GROUP BY t.calendar_month_desc;

Note:

The NOREWRITE hint has been deprecated. Use the NO_REWRITE hint.

MERGE

The MERGE hint lets you merge views in a query.

If a view's query block contains a GROUP BY clause or DISTINCT operator in the SELECT list, then the optimizer can merge the view into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated.

merge_hint::=

Text description of the illustration merge_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary 
  FROM employees e1, 
    (SELECT department_id, avg(salary) avg_salary  
       FROM employees e2 
       GROUP BY department_id) v 
  WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;

When the MERGE hint is used without an argument, it should be placed in the view query block. When MERGE is used with the view name as an argument, it should be placed in the surrounding query.

NO_MERGE

The NO_MERGE hint causes Oracle not to merge mergeable views.

no_merge_hint::=

Text description of the illustration no_merge_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

This hint lets the user have more influence over the way in which the view is accessed.

For example:

SELECT /*+NO_MERGE(seattle_dept)*/ e1.last_name, seattle_dept.department_name 
  FROM employees e1, 
    (SELECT location_id, department_id, department_name 
       FROM departments 
       WHERE location_id = 1700) seattle_dept 
  WHERE e1.department_id = seattle_dept.department_id;

This causes view seattle_dept not to be merged.

When the NO_MERGE hint is used without an argument, it should be placed in the view query block. When NO_MERGE is used with the view name as an argument, it should be placed in the surrounding query.

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.

Even if the hint is given, there is no guarantee that the transformation will take place. The optimizer only generates the subqueries if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.

star_transformation_hint::=

Text description of the illustration star_transformation_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint".

For example:

SELECT /*+ STAR_TRANSFORMATION */  *
  FROM sales s, times t, products p, channels c
  WHERE s.time_id = t.time_id
    AND s.prod_id = p.product_id
    AND s.channel_id = c.channel_id
    AND p.product_status = 'obsolete';

See Also:

Oracle Database Concepts for a full discussion of star transformation.
Oracle Database Reference for more information on the STAR_TRANSFORMATION_ENABLED initialization parameter.

NO_STAR_TRANSFORMATION

The NO_STAR_TRANSFORMATION hint causes the optimizer to not do star query transformation.

no_star_transformation_hint::=

Text description of the illustration no_star_transformation_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint".

FACT

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

fact_hint::=

Text description of the illustration fact_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

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.

no_fact_hint::=

Text description of the illustration no_fact_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

UNNEST

The UNNEST hint specifies subquery unnesting. Subquery unnesting unnests and merges the body of the subquery into the body of the query block that contains it, allowing the optimizer to consider them together when evaluating access paths and joins.

If the UNNEST hint is used, Oracle first verifies if the statement is valid. If the statement is not valid, then subquery unnesting cannot proceed. The statement must then must pass a heuristic and query optimization tests.

The UNNEST hint tells Oracle to check the subquery block for validity only. If the subquery block is valid, then subquery unnesting is enabled without checking the heuristics or costs.

See Also:

Oracle Database SQL Reference for more information on unnesting nested subqueries and the conditions that make a subquery block valid
"Subquery Unnesting"

unnest_hint::=

Text description of the illustration unnest_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint".

NO_UNNEST

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

no_unnest_hint::=

Text description of the illustration no_unnest_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint".

Hints for Join Orders

The hints in this section suggest join orders:

The LEADING hint is more versatile and preferred to the ORDERED hint.

LEADING

The LEADING hint specifies the set of tables to be used as the prefix in the execution plan. This hint is more versatile than the ORDERED hint.

The LEADING hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflicting LEADING hints, then all of them are ignored. If the ORDERED hint is specified, it overrides all LEADING hints.

leading_hint::=

Text description of the illustration leading_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ LEADING(e j) */ *
    FROM employees e, departments d, job_history j
    WHERE e.department_id = d.department_id
      AND e.hire_date = j.start_date;

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, then the optimizer chooses the order in which to join the tables. You might 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 lets you choose an inner and outer table better than the optimizer could.

ordered_hint::=

Text description of the illustration ordered_hint.gif

The following query is an example of the use of the ORDERED hint:

SELECT  /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
  FROM customers c, order_items l, orders o
  WHERE c.cust_last_name = :b1
    AND o.customer_id = c.customer_id
    AND o.order_id = l.order_id;

Hints for Join Operations

Each hint described in this section suggests a join operation for a table.

Use of the USE_NL and USE_MERGE hints is recommended with any join order hint. See "Hints for Join Orders". Oracle uses these hints when the referenced table is forced to be the inner table of a join; the hints are ignored if the referenced table is the outer table.

See "Access Path and Join Hints on Views" and "Access Path and Join Hints Inside Views" for hint behavior with mergeable views.

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.

use_nl_hint::=

Text description of the illustration use_nl_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

In the following example where a nested loop is forced through a hint, orders is accessed through a full table scan and the filter condition l.order_id = h.order_id is applied to every row. For every row that meets the filter condition, order_items is accessed through the index order_id.

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
  FROM orders h ,order_items l
  WHERE l.order_id = h.order_id;

Adding an INDEX hint to the query could avoid the full table scan on orders, resulting in an execution plan similar to one used on larger systems, even though it might not be particularly efficient here.

NO_USE_NL

The NO_USE_NL hint causes the optimizer to exclude nested loops join to join each specified table to another row source using the specified table as the inner table.

When this hint is used, only hash join and sort-merge joins will be considered for the specified tables. However, in some cases tables can only be joined using nested loops. In such cases, the optimizer ignores the hint for those tables.

no_use_nl_hint::=

Text description of the illustration no_use_nl_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ NO_USE_NL(l h) */ *
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id
    AND l.order_id > 3500;

USE_NL_WITH_INDEX

The USE_NL_WITH_INDEX hint will cause the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table but only under the following condition. If no index is specified, the optimizer must be able to use some index with at least one join predicate as the index key. If an index is specified, the optimizer must be able to use that index with at least one join predicate as the index key.

use_nl_with_index_hint::=

Text description of the illustration use_nl_with_index_hint.gif

For example:

SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ *
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id
    AND l.order_id > 3500;

USE_MERGE

The USE_MERGE hint causes Oracle to join each specified table with another row source using a sort-merge join.

use_merge_hint::=

Text description of the illustration use_merge_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ USE_MERGE(employees departments) */ * 
  FROM employees, departments 
  WHERE employees.department_id = departments.department_id;

NO_USE_MERGE

The NO_USE_MERGE hint causes the optimizer to exclude sort-merge join to join each specified table to another row source using the specified table as the inner table.

no_use_merge_hint::=

Text description of the illustration no_use_merge_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ NO_USE_MERGE(e d) */ *
  FROM employees e, departments d
  WHERE e.department_id = d.department_id
  ORDER BY d.department_id;

USE_HASH

The USE_HASH hint causes Oracle to join each specified table with another row source using a hash join.

use_hash_hint::=

Text description of the illustration use_hash_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ USE_HASH(l h) */ *
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id
    AND l.order_id > 3500;

NO_USE_HASH

The NO_USE_HASH hint causes the optimizer to exclude hash join to join each specified table to another row source using the specified table as the inner table.

no_use_hash_hint::=

Text description of the illustration no_use_hash_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ NO_USE_HASH(e d) */ *
  FROM employees e, departments d
  WHERE e.department_id = d.department_id;

Hints for Parallel Execution

The hints described in this section determine how statements are parallelized or not parallelized when using parallel execution.

See "Parallel Execution Hints on Views" and "Parallel Execution Hints Inside Views" for hint behavior with mergeable views.

See Also:

Oracle Data Warehousing Guide for more information on parallel execution

PARALLEL

The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the SELECT, INSERT, UPDATE, and DELETE portions of a statement, as well as to the table scan portion.

Note:

The number of servers that can be used is twice the value in the PARALLEL hint, if sorting or grouping operations also take place.

If any parallel restrictions are violated, then the hint is ignored.

parallel_hint::=

Text description of the illustration parallel_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

The integer value specifies the degree of parallelism for the given table. Specifying DEFAULT or no value signifies that the query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism. In the following example, the PARALLEL hint overrides the degree of parallelism specified in the employees table definition:

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */ last_name
  FROM employees hr_emp;

In the next example, the PARALLEL hint overrides the degree of parallelism specified in the employees table definition and tells the optimizer to use the default degree of parallelism determined by the initialization parameters.

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */ last_name
  FROM employees hr_emp;

NO_PARALLEL

The NO_PARALLEL hint overrides a PARALLEL specification in the table clause.

no_parallel_hint::=

Text description of the illustration no_parallel_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

The following example illustrates the NO_PARALLEL hint:

SELECT /*+ NO_PARALLEL(hr_emp) */ last_name
  FROM employees hr_emp;

Note:

The NOPARALLEL hint has been deprecated. Use the NO_PARALLEL hint.

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.

Use the EXPLAIN PLAN statement to identify the distribution chosen by the optimizer. The optimizer ignores the distribution hint, if both tables are serial.

pq_distribute_hint::=

Text description of the illustration pq_distribute_hint.gif

where:

outer_distribution is the distribution for the outer table.
inner_distribution is the distribution for the inner table.

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

See Also:

Oracle Database Concepts for more information on how Oracle parallelizes join operations

There are six combinations for table distribution. Only a subset of distribution method combinations for the joined tables is valid, as explained in Table 17-1.

Table 17-1 Distribution Hint Combinations

Distribution	Interpretation
Hash, Hash	Maps the rows of each table to consumer query servers, using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This hint is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort merge join.
Broadcast, None	All rows of the outer table are broadcast to each query server. The inner table rows are randomly partitioned. This hint is recommended when the outer table is very small compared to the inner table. As a general rule, use the Broadcast/None hint when inner table size number of query servers > outer table size*.
None, Broadcast	All rows of the inner table are broadcast to each consumer query server. The outer table rows are randomly partitioned. This hint is recommended when the inner table is very small compared to the outer table. As a general rule, use the None/Broadcast hint when inner table size number of query servers < outer table size*.
Partition, None	Maps the rows of the outer table, using the partitioning of the inner table. The inner table must be partitioned on the join keys. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers. Note: The optimizer ignores this hint if the inner table is not partitioned or not equijoined on the partitioning key.
None, Partition	Maps the rows of the inner table using the partitioning of the outer table. The outer table must be partitioned on the join keys. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers. Note: The optimizer ignores this hint if the outer table is not partitioned or not equijoined on the partitioning key.
None, None	Each query server performs the join operation between a pair of matching partitions, one from each table. Both tables must be equipartitioned on the join keys.

For example: Given two tables, r and s, that are joined using a hash-join, the following query contains a hint to use hash distribution:

SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ column_list
  FROM r,s
  WHERE r.c=s.c;

To broadcast the outer table r, the query is:

SELECT /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */ column_list
  FROM r,s
  WHERE r.c=s.c;

PARALLEL_INDEX

The PARALLEL_INDEX hint specifies the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes.

parallel_index_hint::=

Text description of the illustration parallel_index_hint.gif

The integer value specifies the degree of parallelism for the given index. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism.

For example:

SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */

In this example, there are three parallel execution processes to be used.

NO_PARALLEL_INDEX

The NO_PARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation.

no_parallel_index_hint::=

Text description of the illustration no_parallel_index_hint.gif

Note:

The NOPARALLEL_INDEX hint has been deprecated. Use the NO_PARALLEL_INDEX hint.

Additional Hints

Several additional hints are included in this section:

APPEND

The APPEND hint lets you enable direct-path INSERT if your database is running in serial mode. Your database is in serial mode if you are not using Enterprise Edition. Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode.

In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.

append_hint::=

Text description of the illustration append_hint.gif

See Also:

Oracle Database Administrator's Guide for information on direct-path inserts

NOAPPEND

The NOAPPEND hint enables conventional INSERT by disabling parallel mode for the duration of the INSERT statement. (Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).

noappend_hint::=

Text description of the illustration noappend_hint.gif

CACHE

The CACHE hint specifies that the blocks retrieved for the table 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.

cache_hint::=

Text description of the illustration cache_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

In the following example, the CACHE hint overrides the table's default caching specification:

SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name
  FROM employees hr_emp;

NOCACHE

The NOCACHE hint specifies that the blocks retrieved for the 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.

nocache_hint::=

Text description of the illustration nocache_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ FULL(hr_emp) NOCACHE(hr_emp) */ last_name
  FROM employees hr_emp;

Note:

The CACHE and NOCACHE hints affect system statistics table scans(long tables) and table scans(short tables), as shown in the V$SYSSTAT view.

Automatic Caching of Small Tables

Small tables are automatically cached, according to the criteria in Table 17-2.

Table 17-2 Table Caching Criteria

Table Size	Size Criteria	Caching
Small	Number of blocks < 20 or 2% of total cached blocks, whichever is larger	If `STATISTICS_LEVEL` is se to `TYPICAL` or higher, Oracle decides whether to cache a table depending on the table scan history. The table is cached only if a future table scan is likely to find the cached blocks. If `STATISTICS_LEVEL` is set to `BASIC`, the table is not cached.
Medium	Larger than a small table, but < 10% of total cached blocks	Oracle decides whether to cache a table on the basis of its table scan and workload history. It caches the table only if a future table scan is likely to find the cached blocks.
Large	> 10% of total cached blocks	Not cached

Automatic caching of small tables is disabled for tables that are created or altered with the CACHE attribute.

PUSH_PRED

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

push_pred_hint::=

Text description of the illustration push_pred_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *
    FROM employees e,
         (SELECT manager_id
            FROM employees
         ) v
  WHERE e.manager_id = v.manager_id(+)
    AND e.employee_id = 100;

When the PUSH_PRED hint is used without an argument, it should be placed in the view query block. When PUSH_PRED is used with the view name as an argument, it should be placed in the surrounding query.

NO_PUSH_PRED

The NO_PUSH_PRED hint prevents pushing of a join predicate into the view.

no_push_pred_hint::=

Text description of the illustration no_push_pred_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ NO_MERGE(v) NO_PUSH_PRED(v) */ *
    FROM employees e,
         (SELECT manager_id
            FROM employees
         ) v
  WHERE e.manager_id = v.manager_id(+)
    AND e.employee_id = 100;

When the NO_PUSH_PRED hint is used without an argument, it should be placed in the view query block. When NO_PUSH_PRED is used with the view name as an argument, it should be placed in the surrounding query.

PUSH_SUBQ

The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest possible step in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves performance to evaluate the subquery earlier.

This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.

push_subq_hint::=

Text description of the illustration push_subq_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint".

NO_PUSH_SUBQ

The NO_PUSH_SUBQ hint causes non-merged subqueries to be evaluated as the last step in the execution plan. If the subquery is relatively expensive or does not reduce the number of rows significantly, then it improves performance to evaluate the subquery last.

no_push_subq_hint::=

Text description of the illustration no_push_subq_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint".

QB_NAME

Use the QB_NAME hint to define a name for a query block. This name can then be used in another query block to hint tables appearing in the named query block.

qb_name::=

Text description of the illustration qb_name_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint".

If two or more query blocks have the same name, or if the same query block is hinted twice with different names, all the names and the hints referencing them are ignored. Query blocks that are not named using this hint have unique system-generated names. These names can be displayed in the plan table and can also be used to hint tables within the query block, or in query block hints.

For example:

SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name
  FROM employees e
  WHERE last_name = 'Smith';

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.

cursor_sharing_exact_hint::=

Text description of the illustration cursor_sharing_exact_hint.gif

DRIVING_SITE

The DRIVING_SITE hint forces query execution to be done for the table at a different site than that selected by Oracle.

driving_site_hint::=

Text description of the illustration driving_site_hint.gif

For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

For example:

SELECT /*+ DRIVING_SITE(departments) */ * 
  FROM employees, departments@rsite 
  WHERE employees.department_id = departments.department_id;

If this query is executed without the hint, then rows from departments are sent to the local site, and the join is executed there. With the hint, the rows from employees are sent to the remote site, and the query is executed there, returning the result to the local site.

This hint is useful if you are using distributed query optimization.

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.

dynamic_sampling_hint::=

Text description of the illustration dynamic_sampling_hint.gif

integer is a value from 0 to 10 indicating the degree of sampling. For a description of the queryblock syntax, see "Specifying a Query Block in a Hint". For a description of the tablespec syntax, see "Specifying Global Table Hints".

If the cardinality statistic exists, it is used. Otherwise, the DYNAMIC_SAMPLING hint enables dynamic sampling to estimate the cardinality statistic.

To apply dynamic sampling to a specific table, use the following form of the hint:

SELECT /*+ dynamic_sampling(employees 1) */ * 
  FROM employees 
  WHERE ..,

If there is a table hint, dynamic sampling is used unless the table is analyzed and there are no predicates on the table. For example, the following query will not result in any dynamic sampling if employees is analyzed:

SELECT /*+ dynamic_sampling(e 1) */ count(*) 
  FROM employees e;

The cardinality statistic is used, if it exists. If there is a predicate, dynamic sampling is done with a table hint and cardinality is not estimated.

See Also:

"Estimating Statistics with Dynamic Sampling" for information about dynamic sampling and the sampling levels that can be set

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.

This hint reduces compilation time because spreadsheet analysis may be lengthy if the number of rules is significantly large, such as more than several hundreds.

spread_min_analysis_hint::=

Text description of the illustration spread_min_analysis_hint.gif

17 Optimizer Hints

Type of Hints

Example 17-1 Specifying a Full Set of Hints

Specifying a Query Block in a Hint

Example 17-2 Using a Query Block in a Hint

Figure 17-1 Tablespec Syntax

Example 17-3 Using Global Hints Example

Example 17-4 Using Global Hints with NO_MERGE

Figure 17-2 Indexspec Syntax

Hints and Mergeable Views

Optimization Approaches and Goal Hints in Views

Access Path and Join Hints on Views

Access Path and Join Hints Inside Views

Parallel Execution Hints on Views

Parallel Execution Hints Inside Views

Hints and Nonmergeable Views

Table 17-1 Distribution Hint Combinations

Automatic Caching of Small Tables

Table 17-2 Table Caching Criteria

17
Optimizer Hints