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 .

See Also:

for information on that hint and for information on direct-path inserts

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 .

CACHE Hint

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.

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

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.

IGNORE_ROW_ON_DUPKEY_INDEX Hint

Note:

The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in does not apply for these three hints.

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 .

· You must specify exactly one index. If you specify no index, then the statement causes . If you specify more than one index, then the statement causes .

· 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 .

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

Note:

This hint disables both APPEND mode and parallel DML.

INDEX_COMBINE Hint

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 . 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 Hint

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

DYNAMIC_SAMPLING Hint

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

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.

PUSH_PRED Hint

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

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.

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

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

USE_CONCAT 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_NL_WITH_INDEX Hint

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.

MERGE Hint

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.