trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dave Birdsall <dave.birds...@esgyn.com>
Subject RE: How to get a sort_groupby plan
Date Mon, 15 Oct 2018 16:39:49 GMT
Hi,

If the data is already in sorted order, you might get a sort group by plan.

For example:

Create table t1 (a int not null, b int);

Insert a million rows into t1

Prepare select a,count(*) from t1 group by a;

You are likely to get a sort group by plan for this, since the data is already ordered by
a.

If the table is partitioned, you might still get a hash group by plan though.

Dave

-----Original Message-----
From: Qifan Chen <qifan.chen@esgyn.com> 
Sent: Saturday, October 13, 2018 9:35 AM
To: dev@trafodion.apache.org
Subject: Re: How to get a sort_groupby plan

External

Hi Yuan,


In this plan, I think the compiles does the right thing in selecting the hash_groupby because
a sort groupby would be more expensive and the sorting work (on 1) does not help the final
sort (on 2).


Neverthless, you can use a "control query shape" statment to force the sort groupby.


control query shape sort(sort_groupby(cut));

<compile the query>;


Thanks --Qifan

________________________________
From: Liu, Yuan (Yuan) <yuan.liu@esgyn.cn>
Sent: Saturday, October 13, 2018 10:57:37 AM
To: dev@trafodion.apache.org
Subject: How to get a sort_groupby plan


Hi trafodioneers,



I am trying to get a sort_groupby query plan, but I always get hash_groupby plan.

Do you have any idea about how to get a sort_groupby plan?



>>explain options 'f' select a.INDUSTRYPHY,sum(a.REGCAP) from DMA_ENTTYPE_STAT a group
by 1 order by 2;



LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD

---- ---- ---- --------------------  --------  --------------------  ---------



3    .    4    root                                                  2.50E+001

2    .    3    sort                                                  2.50E+001

1    .    2    hash_groupby                                          2.50E+001

.    .    1    trafodion_scan                  DMA_ENTTYPE_STAT      5.03E+00







Best regards



刘源(Yuan)

上海易鲸捷信息技术有限公司<http://www.esgyn.cn/>

地址:上海市浦东新区金科路2889号长泰广场A座603

手机:13671935540

邮箱:yuan.liu@esgyn.cn<mailto:yuan.liu@esgyn.cn>

[易鲸捷logo中文 - 副本]


Mime
View raw message