Group By Hint

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]) */

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 grouped 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 the SQL statement. It sets GROUP BY/DISTINCT to not use HASH AGGREGATE, instead using MERGE GROUP BY and 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 the hash aggregation algorithm.
-- The optimizer will choose other aggregation methods, such as sorted aggregation, to calculate the sum of salaries grouped by 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 the GROUP BY operation down to the data nodes in a distributed database when feasible. This way, each node can perform grouping and aggregation on its local 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]) */

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 is the opposite of the GBY_PUSHDOWN hint. It instructs the optimizer to complete the entire grouping and aggregation process at the central node rather than pushing the GROUP BY operation down to the data nodes. 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;