trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Liu, Yuan (Yuan)" <yuan....@esgyn.cn>
Subject RE: How to get a sort_groupby plan
Date Thu, 18 Oct 2018 05:05:03 GMT
Hi Anoop,

I want to know which kind of SQLs is more suitable or better performance to use sort_groupby.

Best regards

Yuan

-----Original Message-----
From: Anoop Sharma <anoop.sharma@esgyn.com> 
Sent: Thursday, October 18, 2018 12:31 PM
To: dev@trafodion.apache.org
Subject: RE: How to get a sort_groupby plan

hi Yuan
  what was the reason or motivation behind your original question of ways to get a sort_groupby
plan?
Is it for a deeper understanding of a sort groupby plan, or to do perf comparison between
hash and sort groupby, or you felt that your original query would run better with sort groupby,
or something else?

anoop

-----Original Message-----
From: Liu, Yuan (Yuan) <yuan.liu@esgyn.cn>
Sent: Wednesday, October 17, 2018 7:03 PM
To: dev@trafodion.apache.org
Subject: RE: How to get a sort_groupby plan

Thank you Dave & Qifan.



Best regards

Yuan


-----Original Message-----
From: Dave Birdsall <dave.birdsall@esgyn.com>
Sent: Tuesday, October 16, 2018 12:40 AM
To: dev@trafodion.apache.org
Subject: RE: How to get a sort_groupby plan

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