Eager aggregation transforms the internal representation of queries such as those discussed above, and processes them as if the aggregation is performed incrementally: first locally, over each table, producing intermediate aggregate results over smaller local subgroups, and then globally after the join, thus combining the local aggregation results to produce the final result set.

These queries, which return the same result set over any data
set, are *derived table* SQL-level rewrites of
the vector and scalar aggregation examples above. They illustrate
the eager aggregation transformations that Adaptive Server performs
on the internal representation of the queries.

select r1, sum(sum_s1 * count_r) from (select r1,r2, count_r = count(*) from r group by r1,r2 )gr , (select s2, sum_s1 = sum(s1) from s group by s2 )gs where r2-s2 group by r1

select sum(sum_s1 * count_r) from (select r2, count_r = count(*) from r group by r2 )gr , (select s2,

sum_s1 = sum(s1) from s group by s2 )gs where r2 = s2

Eager aggregation plans are generated and costed by the optimizer, and can be chosen as the best plan. This form of advanced query optimization can result in orders of magnitude of performance gain, relative to the original SQL query.