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 Specifying the Query Block Where the Hint Is Located. @queryblock can be omitted, indicating that the hint 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                      
   ------------------------------------------------------
    Streaming (type: GATHER)
      Node/s: All datanodes
      ->  Hash 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
   (13 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                         
   ------------------------------------------------------------
    Streaming (type: GATHER)
      Node/s: All datanodes
      ->  Hash Join
            Hash Cond: (t2.c1 = t1.c1)
            ->  Seq Scan on t2
            ->  Hash
                  ->  Group
                        Group By Key: t1.c1
                        ->  Sort
                              Sort Key: t1.c1
                              ->  Hash Join
                                    Hash Cond: (t1.c1 = t3.c1)
                                    ->  Seq Scan on t1
                                    ->  Hash
                                          ->  Seq Scan on t3
   (15 rows)