更新时间:2024-06-03 GMT+08:00

指定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)