A Combined Framework for Grouping and Order Optimization

Abstract

Since the introduction of cost-based query optimization by Selinger et al. in their seminal paper, the performance-critical role of interesting orders has been recognized. Some algebraic operators change interesting orders (e.g. sort and select), while others exploit them (e.g. merge join). Likewise, Wang and Cherniack (VLDB 2003) showed that existing groupings should be exploited to avoid redundant grouping operations. Ideally, the reasoning about interesting orderings and groupings should be integrated into one framework.So far, no complete, correct, and efficient algorithm for ordering and grouping inference has been proposed. We fill this gap by proposing a general two-phase approach that efficiently integrates the reasoning about orderings and groupings. Our experimental results show that with a modest increase of the time and space requirements of the preprocessing phase both orderings and groupings can be handled at the same time. More importantly, there is no additional cost for the second phase during which the plan generator changes and exploits orderings and groupings by adding operators to subplans.

Sort-sharing-aware query processing

“…As such, some sort operations within the query execution plan become redundant and thus can be removed. Their work was followed and extended by [18,29,19], which are all independent and complementary to our work.…”

Incorporating partitioning and parallel plans into the SCOPE optimizer

“…Earlier research has shown how to derive ordering and grouping properties for standard relational operators executed on non-partitioned inputs [15], [16], [17], [13], [12]. Ordering and grouping are local properties, that is, properties of each partition, so previous work still applies when the operators are running in partitioned mode.…”

“…Normalization and matching of local properties (sorting and grouping) have been studied extensively in [13], [12]. Matching of global properties (partitioning) is based on inference rules (2), (3), and (5) in section V. Recall that required properties are not always unique but may encode multiple alternatives, which results in additional matching opportunities.…”

“…The data is repartitioned on usergroup and each destination machine sort-merges its inputs to retain the sort order on usergroup. • (12). All rows from the usergroup now reside on the same machine and the global aggregate on usergroup is calculated.…”

“…As this issue is well covered elsewhere [4], [16], [13], [12], we omit the details but assume that functional dependencies and equivalence classes have been computed.…”