Using the Equi-Constant Predicates optimization technique, sql engine recognizes leading index prefix components with equi-constant predicates and tries to select index scan if the remaining index components (may include leading components also) are specified in group-by columns. For example:
Create index idx1 on test1(c1,c2, c3)
Select c2,c3 from Test1 where c1=100 GROUP BY c2,c3;
Select c2,c3 from Test1 where c1=100 GROUP BY c3,c2;
In these two queries, index idx1 can be chosen with the index predicate c1=100. Since the rows are in order and the Considering Constant Predicates optimization technique is applied, STREAM aggregation is chosen. If this optimization technique is not applied, the SQL optimizer will pick the index scan but will choose the HASH aggregation.
