Hint Type |
Description |
|---|---|
USE_HASH_AGGREGATION |
Forces the use of hash aggregation. Its reverse operation is NO_USE_HASH_AGGREGATION. |
NO_USE_HASH_AGGREGATION |
Avoids the use of hash aggregation. Its reverse operation is USE_HASH_AGGREGATION. |
GBY_PUSHDOWN |
Enables two-phase GROUP BY pushdown. Its reverse operation is NO_GBY_PUSHDOWN. |
NO_GBY_PUSHDOWN |
Disables two-phase GROUP BY pushdown. Its reverse operation is GBY_PUSHDOWN. |
DISABLE_OP_RICH_FORMAT |
In the plan, the physical operators specified in op_list are rolled back to vectorized 1.0. |
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. It sets GROUP BY/DISTINCT to use HASH AGGREGATE, such as HASH GROUP BY and HASH DISTINCT. Its reverse operation is NO_USE_HASH_AGGREGATION.
Syntax
/*+USE_HASH_AGGREGATION([qb_name]) */
Example
-- 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 will be calculated using a hash table after grouping by e.department_id.
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. It sets GROUPBY/DISTINCT to not use HASH AGGREGATE, but instead use MERGE GROUP BY and MERGE DISTINCT. Its reverse operation is USE_HASH_AGGREGATION.
Syntax
/*+ NO_USE_HASH_AGGREGATION([qb_name]) */
Example
-- Use the NO_USE_HASH_AGGREGATION hint to instruct the query optimizer to avoid using the hash aggregation algorithm.
-- The optimizer will choose other aggregation methods, such as sorted aggregation, to calculate the sum of salaries for e.department_id.
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 the GROUP BY operation to the data nodes in the distributed database when possible. This way, each node can perform grouping and aggregation on its portion of the data and then send the results to a central node for the final aggregation. Its reverse operation is NO_GBY_PUSHDOWN.
Syntax
/*+ GBY_PUSHDOWN([qb_name]) */
Example
SELECT /*+ GBY_PUSHDOWN */ department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;
NO_GBY_PUSHDOWN Hint
The NO_GBY_PUSHDOWN hint is the opposite of the GBY_PUSHDOWN hint. It instructs the optimizer not to push down the GROUP BY operation to the data nodes, but instead to perform the entire grouping and aggregation process at the central node. Its reverse operation is GBY_PUSHDOWN.
Syntax
/*+ NO_GBY_PUSHDOWN([qb_name]) */
Example
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 vectorized 1.0 in the 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
Example
SELECT /*+disable_op_rich_format('table_scan','hash_group_by')*/ * FROM t GROUP BY a;