指定agg算法的Hint

功能描述

在进行agg算法时可以指定agg的方法。

语法格式

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

参数说明

• @queryblock 见指定Hint所处的查询块Queryblock章节，可省略，表示在当前查询块生效，当不指定时，hint没有括号"()"。

示例

1. 使用hash聚集。

   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_sort_agg聚集，mergejoin有序。

   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)