Optimized hints: Group by hint

USE_HASH_AGGREGATION hint

The USE_HASH_AGGREGATION hint directs the optimizer to use the HASH aggregation algorithm when generating a plan to execute the SQL statement. When you set GROUP BY/DISTINCT to use HASH AGGREGATE, the settings are equivalent to HASH GROUP BY and HASH DISTINCT. You can use the NO_USE_HASH_AGGREGATION hint to undo the settings.

Syntax

/*+USE_HASH_AGGREGATION([qb_name]) */

Examples

-- Use the USE_HASH_AGGREGATION hint to direct the query optimizer to use the hash aggregation algorithm for aggregation computation.
-- In this query, the total salary in each department is calculated in 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 directs the optimizer to avoid using the HASH aggregation algorithm when executing the SQL statement. When you set groupby/distinct to not use HASH AGGREGATE, the settings are equivalent to MERGE GROUP BY and MERGE DISTINCT. You can use the USE_HASH_AGGREGATION hint to undo the settings.

Syntax

/*+ NO_USE_HASH_AGGREGATION([qb_name]) */

Examples

-- Use the NO_USE_HASH_AGGREGATION hint to direct the query optimizer to avoid using the hash aggregation algorithm.
-- The optimizer will select other aggregation methods, such as sorting aggregation, to calculate the total salary in each department.
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 directs the optimizer to pushdown the GROUP BY operation to data nodes in the distributed database when possible. In this way, each node can perform grouping and aggregation on the data part it owns and send the local aggregation results to the central node for the final aggregation. You can use the NO_GBY_PUSHDOWN hint to undo the settings.

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, which is the opposite of the GBY_PUSHDOWN hint, directs the optimizer to perform the GROUP BY operation in the central node instead of pushing it down to data nodes. You can use the GBY_PUSHDOWN hint to undo the settings.

Syntax

/*+ NO_GBY_PUSHDOWN([qb_name]) */

Examples

SELECT /*+ NO_GBY_PUSHDOWN */ department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;