Eager aggregation

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.

Vector aggregation

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

Scalar aggregation

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.