All Products
Search
Document Center

Hints related to query policies

Last Updated: Jun 18, 2021

USE_JIT Hint

The USE_JIT hint instructs the server to forcibly execute expressions by using the JIT compilation when the server is executing SQL statements.

The USE_JIT hint uses the following syntax:

/*+ USE_JIT  */

The following statement provides an example:

SELECT /*+ USE_JIT*/ e.department_id, sum(e.salary)
 FROM employees e
 WHERE e.department_id = 1001;
 GROUP BY e.department_id;

NO_USE_JIT Hint

The NO_USE_JIT hint prevents the server from using the JIT compilation to execute expressions when the server is executing SQL statements.

The NO_USE_JIT hint uses the following syntax:

/*+ NO_USE_JIT*/

The following statement provides an example:

SELECT /*+NO_USE_JIT*/ e.department_id, sum(e.salary)
 FROM employees e
 WHERE e.department_id = 1001;
 GROUP BY e.department_id;

USE_HASH_AGGREGATION Hint

The USE_HASH_AGGREGATION hint instructs the optimizer to forcibly execute the SQL statement by using the hash aggregation algorithm when the optimizer is generating a plan.

The USE_HASH_AGGREGATION hint uses the following syntax:

/*+ USE_HASH_AGGREGATION */

The following statement provides an example:

SELECT /*+ USE_HASH_AGGREGATION */ e.department_id, sum(e.salary)
 FROM employees e
 WHERE e.department_id = 1001;
 GROUP BY e.department_id;

NO_USE_HASH_AGGREGATION Hint

The NO_USE_HASH_AGGREGATION hint prevents the optimizer from using the hash aggregation algorithm to run an SQL statement when the optimizer is executing the statement.

The NO_USE_HASH_AGGREGATION hint uses the following syntax:

/*+ NO_USE_HASH_AGGREGATION */

The following statement provides an example:

SELECT /*+ NO_USE_HASH_AGGREGATION */ e.department_id, sum(e.salary)
 FROM employees e
 WHERE e.department_id = 1001;
 GROUP BY e.department_id;

USE_LATE_MATERIALIZATION Hint

The USE_LATE_MATERIALIZATION hint instructs the optimizer to delay view materialization.

The USE_LATE_MATERIALIZATION hint uses the following syntax:

/*+ USE_LATE_MATERIALIZATION  */

The following statement provides an example:

SELECT /*+ USE_LATE_MATERIALIZATION*/ e.department_id, sum(e.salary)
 FROM employees e
 WHERE e.department_id = 1001;
 GROUP BY e.department_id;

NO_USE_LATE_MATERIALIZATION Hint

The NO_USE_LATE_MATERIALIZATION hint instructs the optimizer to disable the delay of view materialization.

The NO_USE_LATE_MATERIALIZATION hint uses the following syntax:

/*+ NO_USE_LATE_MATERIALIZATION  */

The following statement provides an example:

SELECT /*+ NO_USE_LATE_MATERIALIZATION*/ e.department_id, sum(e.salary)
 FROM employees e
 WHERE e.department_id = 1001;
 GROUP BY e.department_id;

USE_NL_MATERIALIZATION Hint

The USE_NL_MATERIALIZATION hint forcibly instructs the optimizer to generate a materialize operator to cache data when the optimizer specifies a table as an internal table or a subtree.

The USE_NL_MATERIALIZATION hint uses the following syntax:

/*+ USE_NL_MATERIALIZATION ( [ @ queryblock ] tablespec [ tablespec ]... ) */

The following statement provides an example:

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

NO_USE_NL_MATERIALIZATION Hint

The NO_USE_NL_MATERIALIZATION hint prevents the optimizer from generating a materialize operator to cache data when the optimizer specifies a table as an internal table or a subtree.

The NO_USE_NL_MATERIALIZATION hint uses the following syntax:

/*+ NO_USE_NL_MATERIALIZATION ( [ @ queryblock ] tablespec [ tablespec ]... ) */

The following statement provides an example:

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