AGG Hint

Description

You can specify the AGG method when performing the AGG algorithm.

Syntax

use_hash_agg[(@queryblock)], use_sort_agg[(@queryblock)]

Parameters

For details about @queryblock, see Hint for Specifying the Query Block Where the Hint Is Located. This parameter can be omitted, indicating that it takes effect in the current query block. If it is not specified, the hint does not have parentheses (()).

Example

1. Use hash aggregation.

   gaussdb=# explain (costs off) select c1 from t2 where c1 in( select /*+ use_hash_agg */ t1.c1 from t1,t3 where t1.c1=t3.c1 group by 1);
                      QUERY PLAN                   
   ------------------------------------------------
    Hash Semi Join
      Hash Cond: (t2.c1 = t1.c1)
      ->  Seq Scan on t2
      ->  Hash
            ->  HashAggregate
                  Group By Key: t1.c1
                  ->  Hash Join
                        Hash Cond: (t1.c1 = t3.c1)
                        ->  Seq Scan on t1
                        ->  Hash
                              ->  Seq Scan on t3
   (11 rows)

2. Use use_sort_agg for aggregation and then perform merge join.

   gaussdb=# explain (costs off) select c1 from t2 where c1 in( select /*+ use_sort_agg */ t1.c1 from t1,t3 where t1.c1=t3.c1 group by 1);
                          QUERY PLAN                        
   ---------------------------------------------------------
    Hash Semi Join
      Hash Cond: (t2.c1 = t1.c1)
      ->  Seq Scan on t2
      ->  Hash
            ->  Group
                  Group By Key: t1.c1
                  ->  Merge Join
                        Merge Cond: (t1.c1 = t3.c1)
                        ->  Index Only Scan using it1 on t1
                        ->  Sort
                              Sort Key: t3.c1
                              ->  Seq Scan on t3
   (12 rows)