Query optimization for group-by extensions and distinct aggregate functions

摘要

Techniques for query optimization for group-by extensions and distinct aggregate functions are provided. A query has an extended group-by clause with an extended group-by operator and a first set of group-by columns. The query has one or more distinct aggregate functions and one or more non-distinct aggregate functions. An initial subquery is constructed that generates a partially aggregated initial temporary (PAIT) table when executed. The initial subquery includes a GROUP BY clause with a second set of group-by columns that includes the first set of group-by columns of the extended group-by clause of the query and one or more columns specified by the one or more distinct aggregate functions. One or more subqueries are computed that compute groupings indicated by the extended group-by operator based on the PAIT table generated by the initial subquery.