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) <[email protected]> Sent: Saturday, October 13, 2018 10:57:37 AM To: [email protected] 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 邮箱：[email protected]<mailto:[email protected]> [易鲸捷logo中文 - 副本]
