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 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) 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 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 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)); ; Thanks --Qifan ________________________________ From: Liu, Yuan (Yuan) 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） 上海易鲸捷信息技术有限公司 地址：上海市浦东新区金科路2889号长泰广场A座603 手机：13671935540 邮箱：yuan.liu@esgyn.cn [易鲸捷logo中文 - 副本]