Avoiding sorting and grouping in processing queries

• Authors: Xiaoyu Wang;Mitch Cherniack
• Venue: VLDB '03 Proceedings of the 29th international conference on Very large data bases - Volume 29
• Year: 2003

Sorting and grouping are amongst the most costly operations performed during query evaluation. System R [6] used simple inference strategies to determine orderings held of intermediate relations to avoid unnecessary sorting, and to influence join plan selection. Since then, others have proposed using integrity constraint information to infer orderings of intermediate query results. However, these proposals do not consider how to avoid grouping operations by inferring groupings, nor do they consider secondary orderings (where records in the same group satisfy some ordering). In this paper, we introduce a formalism for expressing and reasoning about order properties: ordering and grouping constraints that hold of physical representations of relations. In so doing, we can reason about how the relation is ordered or grouped, both in terms of primary and secondary orders. After formally defining order properties, we introduce a plan refinement algorithm that infers order properties for intermediate and final query results on the basis of those known to hold of query inputs, and then exploits these inferences to avoid unnecessary sorting and grouping. We then show empirical results demonstrating the benefits of plan refinement, and show that the overhead that our algorithm adds to query optimization is low.