Comments

ALL_ROWS Hint

The ALL_ROWS hint instructs the optimizer to optimize a statement block with a goal of best throughput, which is minimum total resource consumption. 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 = 107;

If you specify either the ALL_ROWS or the FIRST_ROWS 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 hint, then the optimizer gives precedence to the access paths and join operations specified by the hints.

APPEND Hint

The APPEND hint instructs the optimizer to use direct-path INSERT with the subquery syntax of the INSERT statement.

• Conventional INSERT is the default in serial mode. In serial mode, direct path can be used only if you include the APPEND hint.

• Direct-path INSERT is the default in parallel mode. In parallel mode, conventional insert can be used only if you specify the NOAPPEND hint.

The decision whether the INSERT will go parallel or not is independent of the APPEND hint.

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.

The APPEND hint is only supported with the subquery syntax of the INSERT statement, not the VALUES clause. If you specify the APPEND hint with the VALUES clause, it is ignored and conventional insert will be used. To use direct-path INSERT with the VALUES clause, refer to "APPEND_VALUES Hint".

APPEND_VALUES Hint

The APPEND_VALUES hint instructs the optimizer to use direct-path INSERT with the VALUES clause. If you do not specify this hint, then conventional INSERT is used.

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.

The APPEND_VALUES hint can be used to greatly enhance performance. Some examples of its uses are:

• In an Oracle Call Interface (OCI) program, when using large array binds or array binds with row callbacks

• In PL/SQL, when loading a large number of rows with a FORALL loop that has an INSERT statement with a VALUES clause

The APPEND_VALUES hint is only supported with the VALUES clause of the INSERT statement. If you specify the APPEND_VALUES hint with the subquery syntax of the INSERT statement, it is ignored and conventional insert will be used. To use direct-path INSERT with a subquery, refer to "APPEND Hint".

CACHE Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The CACHE hint instructs the optimizer to place the blocks retrieved for the table at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This hint is useful for small lookup tables.

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

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

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

CHANGE_DUPKEY_ERROR_INDEX Hint

The CHANGE_DUPKEY_ERROR_INDEX hint provides a mechanism to unambiguously identify a unique key violation for a specified set of columns or for a specified index. When a unique key violation occurs for the specified index, an ORA-38911 error is reported instead of an ORA-001.

This hint applies to INSERT, UPDATE operations. If you specify an index, then the index must exist and be unique. If you specify a column list instead of an index, then a unique index whose columns match the specified columns in number and order must exist.

This use of this hint results in error messages if specific rules are violated. Refer to IGNORE_ROW_ON_DUPKEY_INDEX Hint for details.

CLUSTER Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The CLUSTER hint instructs the optimizer to use a cluster scan to access the specified table. This hint applies only to tables in an index cluster.

CURSOR_SHARING_EXACT Hint

Oracle can replace literals in SQL statements with bind variables, when it is safe to do so. This replacement is controlled with the CURSOR_SHARING initialization parameter. The CURSOR_SHARING_EXACT hint instructs the optimizer to switch this behavior off. When you specify this hint, Oracle executes the SQL statement without any attempt to replace literals with bind variables.

DRIVING_SITE Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The DRIVING_SITE hint instructs the optimizer to execute the query at a different site than that selected by the database. This hint is useful if you are using distributed query optimization.

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 and the result set is returned to the local site.

DYNAMIC_SAMPLING Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The DYNAMIC_SAMPLING hint instructs the optimizer how to 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 tablespec.

The integer value is 0 to 10, indicating the degree of sampling.

If a cardinality statistic already exists for the table, then the optimizer uses it. Otherwise, the optimizer enables dynamic sampling to estimate the cardinality statistic.

If you specify tablespec and the cardinality statistic already exists, then:

• If there is no single-table predicate (a WHERE clause that evaluates only one table), then the optimizer trusts the existing statistics and ignores this hint. 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;
  

• If there is a single-table predicate, then the optimizer uses the existing cardinality statistic and estimates the selectivity of the predicate using the existing statistics.

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

SELECT /*+ DYNAMIC_SAMPLING(employees 1) */ *
  FROM employees 
  WHERE ...

FACT Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The FACT hint is used in the context of the star transformation. It instructs the optimizer that the table specified in tablespec should be considered as a fact table.

FIRST_ROWS Hint

The FIRST_ROWS hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first n rows most efficiently. For integer, specify the number of rows to return.

For example, the optimizer uses the query optimization approach to optimize the following 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 include any blocking operations, such as sorts or groupings. Such statements cannot be optimized for best response time, because Oracle Database must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any such statement, then the database optimizes for best throughput.

FULL Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The FULL hint instructs the optimizer to perform a full table scan for the specified table. For example:

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

Oracle Database 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.

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

HASH Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The HASH hint instructs the optimizer to use a hash scan to access the specified table. This hint applies only to tables in a hash cluster.

IGNORE_ROW_ON_DUPKEY_INDEX Hint

The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.

The semantic effect of this hint results in error messages if specific rules are violated:

• If you specify index, then the index must exist and be unique. Otherwise, the statement causes ORA-38913.

• You must specify exactly one index. If you specify no index, then the statement causes ORA-38912. If you specify more than one index, then the statement causes ORA-38915.

• You can specify either a CHANGE_DUPKEY_ERROR_INDEX or IGNORE_ROW_ON_DUPKEY_INDEX hint in an INSERT statement, but not both. If you specify both, then the statement causes ORA-38915.

As with all hints, a syntax error in the hint causes it to be silently ignored. The result will be that ORA-00001 will be caused, just as if no hint were used.

INDEX Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The INDEX hint instructs the optimizer to use an index scan for the specified table. You can use the INDEX hint for function-based, domain, B-tree, bitmap, and bitmap join indexes.

The behavior of the hint depends on the indexspec specification:

• If the INDEX hint specifies a single available index, then the database performs a scan on this index. The optimizer does not consider a full table scan or a scan of another index on the table.

• For a hint on a combination of multiple indexes, Oracle recommends using INDEX_COMBINE rather than INDEX, because it is a more versatile hint. If the INDEX 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 database can also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The database does not consider a full table scan or a scan on an index not listed in the hint.

• If the INDEX 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 database 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;

INDEX_ASC Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The INDEX_ASC hint instructs the optimizer to use an index scan for the specified table. If the statement uses an index range scan, then Oracle Database scans the index entries in ascending order of their indexed values. Each parameter serves the same purpose as in "INDEX Hint".

The default behavior for a range scan is to scan index entries in ascending order of their indexed values, or in descending order for a descending index. This hint does not change the default order of the index, and therefore does not specify anything more than the INDEX hint. However, you can use the INDEX_ASC hint to specify ascending range scans explicitly should the default behavior change.

INDEX_COMBINE Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The INDEX_COMBINE hint instructs the optimizer to use a bitmap access path for the table. If indexspec is omitted from the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of indexes has the best cost estimate for the table. If you specify indexspec, then the optimizer tries to use some Boolean combination of the specified indexes. Each parameter serves the same purpose as in "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_DESC Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The INDEX_DESC hint instructs the optimizer to use a descending index scan for the specified table. If the statement uses an index range scan and the index is ascending, 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. For a descending index, this hint effectively cancels out the descending order, resulting in a scan of the index entries in ascending order. Each parameter serves the same purpose as in "INDEX Hint". For example:

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

INDEX_FFS Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The INDEX_FFS hint instructs the optimizer to perform a fast full index scan rather than a full table scan.

Each parameter serves the same purpose as in "INDEX Hint". For example:

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

INDEX_JOIN Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The INDEX_JOIN hint 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.

Each parameter serves the same purpose as in "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_SS Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The INDEX_SS hint instructs the optimizer to perform 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.

Each parameter serves the same purpose as in "INDEX Hint". For example:

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

INDEX_SS_ASC Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The INDEX_SS_ASC hint instructs the optimizer to perform an index skip scan for the specified table. If the statement uses an index range scan, then Oracle Database scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition. Each parameter serves the same purpose as in "INDEX Hint".

The default behavior for a range scan is to scan index entries in ascending order of their indexed values, or in descending order for a descending index. This hint does not change the default order of the index, and therefore does not specify anything more than the INDEX_SS hint. However, you can use the INDEX_SS_ASC hint to specify ascending range scans explicitly should the default behavior change.

INDEX_SS_DESC Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The INDEX_SS_DESC hint instructs the optimizer to perform an index skip scan for the specified table. If the statement uses an index range scan and the index is ascending, 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. For a descending index, this hint effectively cancels out the descending order, resulting in a scan of the index entries in ascending order.

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';

LEADING Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The LEADING hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan. This hint is more versatile than the ORDERED hint. 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;

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 you specify the ORDERED hint, it overrides all LEADING hints.

MERGE Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

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.

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.

MODEL_MIN_ANALYSIS Hint

The MODEL_MIN_ANALYSIS hint instructs the optimizer to omit some compile-time optimizations of spreadsheet rules—primarily detailed dependency graph analysis. Other spreadsheet optimizations, such as creating filters to selectively populate spreadsheet access structures and limited rule pruning, are still used by the optimizer.

This hint reduces compilation time because spreadsheet analysis can be lengthy if the number of spreadsheet rules is more than several hundreds.

MONITOR Hint

The MONITOR hint forces real-time SQL monitoring for the query, even if the statement is not long running. This hint is valid only when the parameter CONTROL_MANAGEMENT_PACK_ACCESS is set to DIAGNOSTIC+TUNING.

NATIVE_FULL_OUTER_JOIN Hint

The NATIVE_FULL_OUTER_JOIN hint instructs the optimizer to use native full outer join, which is a native execution method based on a hash join.

NOAPPEND Hint

The NOAPPEND hint instructs the optimizer to use 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.

NOCACHE Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The NOCACHE hint instructs the optimizer to place the blocks retrieved for the table 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. For example:

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

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

NO_EXPAND Hint

(See "Specifying a Query Block in a Hint")

The NO_EXPAND hint instructs the optimizer not to consider 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. For example:

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

NO_FACT Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The NO_FACT hint is used in the context of the star transformation. It instruct the optimizer that the queried table should not be considered as a fact table.

NO_INDEX Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The NO_INDEX hint instructs the optimizer not to use one or more indexes for the specified table. For example:

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

Each parameter serves the same purpose as in "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 the database ignores both the NO_INDEX hint and the index hint for the specified indexes and considers those indexes for use during execution of the statement.

NO_INDEX_FFS Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The NO_INDEX_FFS hint instructs the optimizer to exclude a fast full index scan of the specified indexes on the specified table. 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;

NO_INDEX_SS Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The NO_INDEX_SS hint instructs the optimizer to exclude a skip scan of the specified indexes on the specified table. Each parameter serves the same purpose as in the "INDEX Hint".

NO_MERGE Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The NO_MERGE hint instructs the optimizer not to combine the outer query and any inline view queries into a single query.

This hint lets you have more influence over the way in which the view is accessed. For example, the following statement causes view seattle_dept not to be merged:

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;

When you use the NO_MERGE hint in the view query block, specify it without an argument. When you specify NO_MERGE in the surrounding query, specify it with the view name as an argument.

NO_MONITOR Hint

The NO_MONITOR hint disables real-time SQL monitoring for the query, even if the query is long running.

NO_NATIVE_FULL_OUTER_JOIN Hint

The NO_NATIVE_FULL_OUTER_JOIN hint instructs the optimizer to exclude the native execution method when joining each specified table. Instead, the full outer join is executed as a union of left outer join and anti-join.

NO_PARALLEL Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The NO_PARALLEL hint overrides a PARALLEL parameter in the DDL that created or altered the table. For example:

ALTER TABLE employees PARALLEL 8;
SELECT /*+ NO_PARALLEL(hr_emp) */ last_name
  FROM employees hr_emp;

NOPARALLEL Hint

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

NO_PARALLEL_INDEX Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The NO_PARALLEL_INDEX hint overrides a PARALLEL parameter in the DDL that created or altered the index, thus avoiding a parallel index scan operation.

NOPARALLEL_INDEX Hint

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

NO_PUSH_PRED Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The NO_PUSH_PRED hint instructs the optimizer not to push a join predicate into the view. 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;

NO_PUSH_SUBQ Hint

(See "Specifying a Query Block in a Hint")

The NO_PUSH_SUBQ hint instructs the optimizer to evaluate nonmerged subqueries as the last step in the execution plan. Doing so can improve performance if the subquery is relatively expensive or does not reduce the number of rows significantly.

NO_PX_JOIN_FILTER Hint

This hint prevents the optimizer from using parallel join bitmap filtering.

NO_QUERY_TRANSFORMATION Hint

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

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

NO_RESULT_CACHE Hint

The optimizer caches query results in the result cache if the RESULT_CACHE_MODE initialization parameter is set to FORCE. In this case, the NO_RESULT_CACHE hint disables such caching for the current query.

If the query is executed from OCI client and OCI client result cache is enabled, then the NO_RESULT_CACHE hint disables caching for the current query.

NO_REWRITE Hint

(See "Specifying a Query Block in a Hint")

The NO_REWRITE hint instructs the optimizer to disable query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. 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;

NOREWRITE Hint

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

NO_STAR_TRANSFORMATION Hint

(See "Specifying a Query Block in a Hint")

The NO_STAR_TRANSFORMATION hint instructs the optimizer not to perform star query transformation.

NO_UNNEST Hint

(See "Specifying a Query Block in a Hint")

Use of the NO_UNNEST hint turns off unnesting .

NO_USE_HASH Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The NO_USE_HASH hint instructs the optimizer to exclude hash joins when joining each specified table to another row source using the specified table as the inner table. For example:

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

NO_USE_MERGE Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The NO_USE_MERGE hint instructs the optimizer to exclude sort-merge joins when joining each specified table to another row source using the specified table as the inner table. 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;

NO_USE_NL Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The NO_USE_NL hint instructs the optimizer to exclude nested loops joins when joining each specified table to another row source using the specified table as the inner table. 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 > 2400;

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

NO_XML_QUERY_REWRITE Hint

The NO_XML_QUERY_REWRITE hint instructs the optimizer to prohibit the rewriting of XPath expressions in SQL statements. By prohibiting the rewriting of XPath expressions, this hint also prohibits the use of any XMLIndexes for the current query. For example:

SELECT /*+NO_XML_QUERY_REWRITE*/ XMLQUERY('<A/>' RETURNING CONTENT)
    FROM dual;

NO_XMLINDEX_REWRITE Hint

The NO_XMLINDEX_REWRITE hint instructs the optimizer not to use any XMLIndex indexes for the current query. For example:

SELECT /*+NO_XMLINDEX_REWRITE*/ count(*) 
  FROM warehouses
  WHERE existsNode(warehouse_spec, '/Warehouse/Building') = 1;

OPT_PARAM Hint

The OPT_PARAM hint lets you set an initialization parameter for the duration of the current query only. This hint is valid only for the following parameters: OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING, and STAR_TRANSFORMATION_ENABLED. For example, the following hint sets the parameter STAR_TRANSFORMATION_ENABLED to TRUE for the statement to which it is added:

SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ * FROM ... ;

Parameter values that are strings are enclosed in single quotation marks. Numeric parameter values are specified without quotation marks.

ORDERED Hint

The ORDERED hint instructs Oracle to join tables in the order in which they appear in the FROM clause. Oracle recommends that you use the LEADING hint, which is more versatile than the ORDERED hint.

When you omit the ORDERED hint from a SQL statement requiring a join, 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 that the optimizer does not know about the number of rows selected from each table. Such information lets you choose an inner and outer table better than the optimizer could.

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 = 'Taylor'
    AND o.customer_id = c.customer_id
    AND o.order_id = l.order_id;

PARALLEL Hint

Note on Parallel Hints 

Beginning with Oracle Database 11g Release 2, the PARALLEL and NO_PARALLEL hints are statement-level hints and supersede the earlier object-level hints: PARALLEL_INDEX, NO_PARALLEL_INDEX, and previously specified PARALLEL and NO_PARALLEL hints. For PARALLEL, if you specify integer, then that degree of parallelism will be used for the statement. If you omit integer, then the database computes the degree of parallelism. All the access paths that can use parallelism will use the specified or computed degree of parallelism.

In the syntax diagrams below, parallel_hint_statement shows the syntax for statement-level hints, and parallel_hint_object shows the syntax for object-level hints. Object-level hints are supported for backward compatibility, and are superseded by statement-level hints.

parallel_hint_statement::=

parallel_hint_object::=

(See "Specifying a Query Block in a Hint", tablespec::=)

The PARALLEL hint instructs the optimizer to use the specified number of concurrent servers for a parallel operation. The hint applies to the SELECT, INSERT, MERGE, UPDATE, and DELETE portions of a statement, as well as to the table scan portion. If any parallel restrictions are violated, then the hint is ignored.

For a statement-level PARALLEL hint:

• PARALLEL: The statement always is run parallel, and the database computes the degree of parallelism, which can be 2 or greater.

• PARALLEL DEFAULT: The same as PARALLEL. The DEFAULT keyword is included for completeness.

• PARALLEL AUTO: The database computes the degree of parallelism, which can be 1 or greater. If the computed degree of parallelism is 1, then the statement runs serially.

• PARALLEL MANUAL: The optimizer is forced to use the parallel settings of the objects in the statement.

• PARALLEL (integer): The optimizer uses the degree of parallelism specified by integer.

In the following example, the optimizer calculates the degree of parallelism. The statement always runs in parallel.

SELECT /*+ PARALLEL */ last_name
   FROM employees;

In the following example, the optimizer calculates the degree of parallelism, but that degree may be 1, in which case the statement will run serially.

SELECT /*+ PARALLEL (AUTO) */ last_name
   FROM employees;

In the following example, the PARALLEL hint advises the optimizer to use the degree of parallelization currently in effect for the table itself, which is 5:

CREATE TABLE parallel_table (col1 number, col2 VARCHAR2(10)) PARALLEL 5; 

SELECT /*+ PARALLEL (MANUAL) */ col2
   FROM parallel_table;

For an object-level PARALLEL hint, the integer value specifies the degree of parallelism for the specified 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 instructs 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;

Oracle ignores parallel hints on temporary tables. Refer to CREATE TABLE and Oracle Database Concepts for more information on parallel execution.

PARALLEL_INDEX Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans, full scans, and fast full scans for partitioned indexes.

The integer value indicates the degree of parallelism for the specified index. 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. For example, the following hint indicates three parallel execution processes are to be used:

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

PQ_DISTRIBUTE Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The PQ_DISTRIBUTE hint instructs the optimizer how to distribute rows among producer and consumer query servers. You can control the distribution of rows for either joins or for load.

Control of Distribution for Load You can control the distribution of rows for parallel INSERT ... SELECT and parallel CREATE TABLE ... AS SELECT statements to direct how rows should be distributed between the producer (query) and the consumer (load) servers. Use the upper branch of the syntax by specifying a single distribution method. The values of the distribution methods and their semantics are described in Table 3-22.

For example, in the following direct-load insert operation, the query and load portions of the operation are combined into each query server:

INSERT /*+ APPEND PARALLEL(target_table, 16) PQ_DISTRIBUTE(target_table, NONE) */
  INTO target_table
  SELECT * FROM source_table;
 

In the following table creation example, the optimizer uses the partitioning of target_table to distribute the rows:

CREATE /*+ PQ_DISTRIBUTE(target_table, PARTITION) */ TABLE target_table
  NOLOGGING PARALLEL 16
  PARTITION BY HASH (l_orderkey) PARTITIONS 512
  AS SELECT * FROM source_table; 

Control of Distribution for Joins You control the distribution method for joins by specifying two distribution methods, as shown in the lower branch of the syntax diagram, one distribution for the outer table and one distribution for the inner table.

• outer_distribution is the distribution for the outer table.

• inner_distribution is the distribution for the inner table.

The values of the distributions are HASH, BROADCAST, PARTITION, and NONE. Only six combinations table distributions are valid, as described in Table 3-23:

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;

PUSH_PRED Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The PUSH_PRED hint instructs the optimizer to push a join predicate into the view. 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;

PUSH_SUBQ Hint

(See "Specifying a Query Block in a Hint")

The PUSH_SUBQ hint instructs the optimizer to evaluate nonmerged subqueries 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 evaluating the subquery earlier can improve performance.

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

PX_JOIN_FILTER Hint

This hint forces the optimizer to use parallel join bitmap filtering.

QB_NAME Hint

(See "Specifying a Query Block in a Hint")

Use the QB_NAME hint to define a name for a query block. This name can then be used in a hint in the outer query or even in a hint in an inline view to affect query execution on the tables appearing in the named query block.

If two or more query blocks have the same name, or if the same query block is hinted twice with different names, then the optimizer ignores all the names and the hints referencing that query block. 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 in hints 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';

RESULT_CACHE Hint

The RESULT_CACHE hint instructs the database to cache the results of the current query or query fragment in memory and then to use the cached results in future executions of the query or query fragment. The hint is recognized in the top-level query, the subquery_factoring_clause, or FROM clause inline view. The cached results reside in the result cache memory portion of the shared pool.

A cached result is automatically invalidated whenever a database object used in its creation is successfully modified. This hint takes precedence over settings of the RESULT_CACHE_MODE initialization parameter.

The query is eligible for result caching only if all functions entailed in the query—for example, built-in or user-defined functions or virtual columns—are deterministic.

If the query is executed from OCI client and OCI client result cache is enabled, then RESULT_CACHE hint enables client caching for the current query.

RETRY_ON_ROW_CHANGE Hint

This hint is valid only for UPDATE and DELETE operations. It is not supported for INSERT or MERGE operations. When you specify this hint, the operation is retried when the ORA_ROWSCN for one or more rows in the set has changed from the time the set of rows to be modified is determined to the time the block is actually modified.

REWRITE Hint

(See "Specifying a Query Block in a Hint")

The REWRITE hint instructs 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.

STAR_TRANSFORMATION Hint

(See "Specifying a Query Block in a Hint")

The STAR_TRANSFORMATION hint instructs the optimizer to 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. For example:

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

Even if the hint is specified, there is no guarantee that the transformation will take place. The optimizer generates the subqueries only 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.

UNNEST Hint

(See "Specifying a Query Block in a Hint")

The UNNEST hint instructs the optimizer to unnest and merge 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.

Before a subquery is unnested, the optimizer first verifies whether the statement is valid. The statement must then pass heuristic and query optimization tests. The UNNEST hint instructs the optimizer 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.

USE_CONCAT Hint

(See "Specifying a Query Block in a Hint")

The USE_CONCAT hint instructs the optimizer to transform combined OR-conditions in the WHERE clause of a query into a compound query using the UNION ALL set operator. Without this hint, 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. For example:

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

USE_HASH Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The USE_HASH hint instructs the optimizer to join each specified table with another row source using a hash join. For example:

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

USE_MERGE Hint

(See "Specifying a Query Block in a Hint", tablespec::=)

The USE_MERGE hint instructs the optimizer to join each specified table with another row source using a sort-merge join. For example:

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

Use of the USE_NL and USE_MERGE hints is recommended with the LEADING and ORDERED hints. The optimizer uses those 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.

USE_NL Hint

The USE_NL hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the specified table as the inner table.

(See "Specifying a Query Block in a Hint", tablespec::=)

The USE_NL hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the specified table as the inner table.

Use of the USE_NL and USE_MERGE hints is recommended with the LEADING and ORDERED hints. The optimizer uses those 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.

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.

USE_NL_WITH_INDEX Hint

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The USE_NL_WITH_INDEX hint instructs the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table. 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 > 2400;

The following conditions apply:

• If no index is specified, then the optimizer must be able to use some index with at least one join predicate as the index key.

• If an index is specified, then the optimizer must be able to use that index with at least one join predicate as the index key.