Hints related to query strategies

Hints related to query strategies include the USE_HASH_AGGREGATION, NO_USE_HASH_AGGREGATION, USE_LATE_MATERIALIZATION, NO_USE_LATE_MATERIALIZATION, USE_NL_MATERIALIZATION, NO_USE_NL_MATERIALIZATION, PUSH_SUBQ, and NO_PUSH_SUBQ hints.

USE_HASH_AGGREGATION Hint

The USE_HASH_AGGREGATION hint instructs the optimizer to force the use of the HASH aggregation algorithm when generating a plan for the SQL statement.

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 to avoid using the HASH aggregation algorithm when executing 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 materializing views.

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 prohibit delaying materializing views.

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 forces the optimizer to generate a materialization operator to cache data when a table is specified as an internal table (subtree).

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 forces the optimizer to avoid generating a materialization operator to cache data when a table is specified as an internal table (subtree).

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;

PUSH_SUBQ Hint

The PUSH_SUBQ hint instructs the optimizer to execute unrewritten subqueries as early as possible. Typically, these subqueries, if not rewritten into joins, are executed after all table joins in the execution plan. If a subquery has a low computation cost and can filter a large amount of data, early execution may improve plan performance. However, if the subquery is rewritten into a join (using /+unnest/), this hint becomes ineffective. The following scenarios are applicable:

• The subquery has a low execution cost but can quickly filter a large amount of data.
• The subquery's results need to be used early to narrow down the data scope.

Syntax:

/*+ PUSH_SUBQ[(@qb_name)] */

Parameter description:

• @qb_name: an optional parameter that specifies the alias of the subquery (for more information, see the QB_NAME parameter above). This helps clarify the target of the hint. If omitted, the hint applies to the subquery it is in by default.

Here is an example:

Use the PUSH_SUBQ hint in the query to instruct the optimizer to execute the subquery as early as possible to filter data from the tbl1 table.

SELECT /*+ PUSH_SUBQ(@"SEL$2") */ *
FROM tbl1, tbl2
WHERE tbl1.col1 = (SELECT MAX(tbl3.col1)
                   FROM tbl3
                   WHERE tbl3.col2 = tbl2.col2);

NO_PUSH_SUBQ Hint

The NO_PUSH_SUBQ hint is the opposite of the PUSH_SUBQ hint. It instructs the optimizer to execute unrewritten subqueries last. This is applicable when the subquery has a high cost or cannot significantly reduce the number of rows. The following scenarios are applicable:

• The subquery has a high execution cost or its result does not significantly reduce the data volume.
• The subquery needs to be executed after other filtering conditions take effect to reduce the input data volume.

Syntax:

/*+ NO_PUSH_SUBQ[(@qb_name)] */

Parameter description:

• @qb_name: an optional parameter that specifies the alias of the subquery.

Here is an example:

Use the NO_PUSH_SUBQ hint in the query to instruct the optimizer to execute the subquery last.

SELECT /*+ NO_PUSH_SUBQ */ *
FROM tbl1, tbl2
WHERE tbl1.col1 = (SELECT MAX(tbl3.col1)
                   FROM tbl3
                   WHERE tbl3.col2 = tbl2.col2);