Hints related to query strategy are USE_HASH_AGGREGATION, NO_USE_HASH_AGGREGATION, USE_LATE_MATERIALIZATION, NO_USE_LATE_MATERIALIZATION, USE_NL_MATERIALIZATION, and NO_USE_NL_MATERIALIZATION.
USE_HASH_AGGREGATION hint
The USE_HASH_AGGREGATION hint instructs the optimizer to use the hash aggregation algorithm to execute the SQL statement when the optimizer generates a plan.
The syntax of the USE_HASH_AGGREGATION hint is as follows:
/*+ USE_HASH_AGGREGATION*/
Here is an example of the USE_HASH_AGGREGATION hint:
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 instructs the optimizer not to use the hash aggregation algorithm to execute the SQL statement when the optimizer executes the SQL statement.
The syntax of the NO_USE_HASH_AGGREGATION hint is as follows:
/*+ NO_USE_HASH_AGGREGATION*/
Here is an example of the NO_USE_HASH_AGGREGATION hint:
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 syntax of the USE_LATE_MATERIALIZATION hint is as follows:
/*+ USE_LATE_MATERIALIZATION*/
Here is an example of the USE_LATE_MATERIALIZATION hint:
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 view materialization.
The syntax of the NO_USE_LATE_MATERIALIZATION hint is as follows:
/*+ NO_USE_LATE_MATERIALIZATION*/
Here is an example of the NO_USE_LATE_MATERIALIZATION hint:
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 instructs the optimizer to generate a materialization operator to cache data when the optimizer specifies a right-side table (sub-tree).
The syntax of the USE_NL_MATERIALIZATION hint is as follows:
/*+ USE_NL_MATERIALIZATION ( [ @queryblock ] tablespec [ tablespec ]... ) */
Here is an example of the USE_NL_MATERIALIZATION hint:
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 instructs the optimizer not to generate a materialization operator to cache data when the optimizer specifies a right-side table (sub-tree).
The syntax of the NO_USE_NL_MATERIALIZATION hint is as follows:
/*+ NO_USE_NL_MATERIALIZATION ( [ @queryblock ] tablespec [ tablespec ]... ) */
Here is an example of the NO_USE_NL_MATERIALIZATION hint:
SELECT /*+ NO_USE_NL_MATERIALIZATION(departments) */ *
FROM employees, departments
WHERE employees.department_id = departments.department_id;
