Orthogonal optimization of subqueries and aggregation César Galindo-Legaria and Milind Joshi   View Paper (PDF)   Return to Industrial Sessions Abstract There is considerable overlap between strategies proposed for subquery evaluation, and those for grouping and aggregation. In this paper we show how a number of small, independent primitives generate a rich set of e.cient execution strategies-covering standard proposals for subquery evaluation suggested in earlier literature. These small primitives fall into two main, orthogonal areas: Correlation removal, and e.cient processing of outerjoins and GroupBy. An optimization approach based on these pieces provides syntax-independence of query processing with respect to subqueries, i. e. equivalent queries written with or without subquery produce the same e.cient plan. We describe techniques implemented in Microsoft SQL Server (releases 7.0 and 8.0) for queries containing sub queries and/or aggregations, based on a number of orthogonal optimizations. We concentrate separately on removing correlated subqueries, also called "query flattening," and on e.cient execution of queries with aggregations. The end result is a modular, flexible implementation, which produces very e.cient execution plans. To demonstrate the validity of our approach, we present results for some queries from the TPC-H benchmark. From all published TPC-H results in the 300GB scale, at the time of writing (November 2000), SQL Server has the fastest results on those queries, even on a fraction of the processors used by other systems. DiSC'02 © 2003 Association for Computing Machinery