trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Liu, Yuan (Yuan)" <>
Subject RE: How to get a sort_groupby plan
Date Thu, 18 Oct 2018 02:03:24 GMT
Thank you Dave & Qifan.

Best regards


-----Original Message-----
From: Dave Birdsall <> 
Sent: Tuesday, October 16, 2018 12:40 AM
Subject: RE: How to get a sort_groupby plan


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

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


-----Original Message-----
From: Qifan Chen <> 
Sent: Saturday, October 13, 2018 9:35 AM
Subject: Re: How to get a sort_groupby plan


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) <>
Sent: Saturday, October 13, 2018 10:57:37 AM
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






[易鲸捷logo中文 - 副本]

View raw message