Hint type |
Description |
|---|---|
USE_HASH_AGGREGATION |
Forces hash aggregation. Its inverse operation is NO_USE_HASH_AGGREGATION. |
NO_USE_HASH_AGGREGATION |
Avoid using hash aggregation. Its inverse operation is USE_HASH_AGGREGATION. |
GBY_PUSHDOWN |
Set to Use Two-Phase GROUP BY Push down. Its reverse operation is NO_GBY_PUSHDOWN. |
NO_GBY_PUSHDOWN |
Set to prohibit two-phase commit. GROUP BY Push down. Its reverse operation is GBY_PUSHDOWN. |
DISABLE_OP_RICH_FORMAT |
In the plan, roll back the physical operators specified in op_list to vectorization 1.0. |
USE_HASH_AGGREGATION Hint
The USE_HASH_AGGREGATION hint instructs the optimizer to forcibly use the HASH aggregation algorithm when generating a plan for this SQL statement. It specifies to use HASH AGGREGATE for GROUP BY/DISTINCT, for example, HASH GROUP BY, HASH DISTINCT. Its counterpart is the NO_USE_HASH_AGGREGATION hint.
Syntax
/*+USE_HASH_AGGREGATION([qb_name]) */
Examples
-- Use the USE_HASH_AGGREGATION hint to force the query optimizer to use the hash aggregation algorithm for aggregate calculations.
-- In this query, this means that the sum of salaries after grouping by e.department_id will be calculated using a hash table.
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 an SQL statement. When set, groupby/distinct operations will not use HASH AGGREGATE but instead employ MERGE GROUP BY or MERGE DISTINCT. Its reverse operation is USE_HASH_AGGREGATION.
Syntax
/*+ NO_USE_HASH_AGGREGATION([qb_name]) */
Examples
-- Use the NO_USE_HASH_AGGREGATION hint to instruct the query optimizer to avoid using hash aggregation algorithms.
-- The optimizer will choose another aggregation method, such as sorting aggregation, to compute the sum of salaries for the e.department_id group.
SELECT /*+ NO_USE_HASH_AGGREGATION */ e.department_id, sum(e.salary)
FROM employees e
WHERE e.department_id = 1001
GROUP BY e.department_id;
GBY_PUSHDOWN Hint
The GBY_PUSHDOWN hint instructs the optimizer to push down GROUP BY operations to data nodes in the distributed database whenever feasible. This allows each node to first group and aggregate the data within its local subset, then send the partial aggregated results to a central node for final aggregation. Its counterpart is the NO_GBY_PUSHDOWN hint.
Syntax
/*+ GBY_PUSHDOWN([qb_name]) */
Examples
SELECT /*+ GBY_PUSHDOWN */ department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;
NO_GBY_PUSHDOWN Hint
The NO_GBY_PUSHDOWN hint, in contrast to the GBY_PUSHDOWN hint, instructs the optimizer not to push down the GROUP BY operation to the data nodes. Instead, it requires the entire grouping and aggregation process to be completed on the central node. Its reverse operation is GBY_PUSHDOWN.
Syntax
/*+ NO_GBY_PUSHDOWN([qb_name]) */
Examples
SELECT /*+ NO_GBY_PUSHDOWN */ department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;
DISABLE_OP_RICH_FORMAT Hint
The DISABLE_OP_RICH_FORMAT hint is used to roll back the physical operators specified in op_list to vectorization version 1.0 in a plan.
Syntax
hint: disable_op_rich_format(op_list);
opt_list: op_name | op_list,op_name
op_name:
LIMIT
| MERGE_DISTINCT
| HASH_DISTINCT
| MATERIAL
| MERGE_JOIN
| GRANULE_ITERATOR
| EXPAND
| SORT
| HASH_UNION
| HASH_INTERSECT
| HASH_EXCEPT
| MERGE_UNION
| MERGE_INTERSECT
| MERGE_EXCEPT
| TABLE_SCAN
| HASH_JOIN
| NESTED_LOOP_JOIN
| SUBPLAN_FILTER
| SUBPLAN_SCAN
| UNPIVOT
| SCALAR_AGGREGATE
| MERGE_GROUP_BY
| HASH_GROUP_BY
| WINDOW_FUNCTION
Examples
SELECT /*+disable_op_rich_format('table_scan','hash_group_by')*/ * FROM t GROUP BY a;