Try OpenEdge Now
skip to main content
SQL Development
Optimizing Query Performance : Understanding optimization : Optimizer phases : GROUP BY optimization : Re-order GROUP BY columns
 
Re-order GROUP BY columns
The SQL engine checks whether the GROUP-BY columns are the leading prefix components of an index, by using re-ordering of GROUP BY column. If they are the leading prefix components of an index, the SQL engine performs stream aggregation for these queries. If this optimization technique is not used, the SQL engine optimizer may choose hash aggregation. An index can be selected to perform GROUP BY c2, c1 if the index is defined on c1 and c2 as leading components. The SQL engine chooses stream aggregation as follows:
Create index idx2 on test1(c1,c2);

Select c1,c2,sum(c1), sum(c2) from test1 GROUP BY c2,c1;