Exploiting upper and lower bounds in top-down query optimization

Shapiro, Lewis P.; Maier, David; Benninghoff, P.; Billings, K.; Fan, Yang-Wen; Hatwal, K.; Wang, Qiang; Zhang, Y.; Wu, Hua-Hsuan; Vance, Bennet

doi:10.1109/ideas.2001.938068

Cited by 13 publications

(21 citation statements)

References 15 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…[21]). This can lead to strengths and weaknesses that specifically result from the transformational nature of these particular systems being incorrectly attributed to all top-down search methods.…”

Section: Transformational Searchmentioning

confidence: 93%

“…This point deserves emphasis, because there have been several previous comparisons drawn between bottom-up dynamic programming and top-down transformational search [11,10,21] that can lead to the misconception that bottom-up search strategies are inherently more efficient-especially for CPfree search spaces-and that top-down search algorithms need to rely on branch-and-bound or demand-driven interesting orders to be competitive. In contrast, our results show that there is no inherent performance penalty in changing from bottom-up to top-down search.…”

Section: Optimal Top-down Partitioningmentioning

confidence: 98%

“…This approach is used explicitly in the prefix search of Sybase SQL Anywhere [3]. An equivalent variant preferable for divideand-conquer approaches is implicit in the search of Volcano, Cascades, and Columbia [9,7,21]. Rather than passing down both U and L, those systems pass down a "budget" (i.e.…”

Section: Accumulated-cost Boundingmentioning

confidence: 98%

“…Transformational search is used in the Volcano [9], Cascades [7], OPT++ [10], and Columbia [21] systems, as well as in Microsoft SQL Server [8]. EROC [11] is a hybrid system that uses (top-down) transformational search for join method selection and costing but (bottom-up) compositional dynamic programming for the enumeration of join orders.…”

Section: Transformational Searchmentioning

confidence: 99%

See 3 more Smart Citations

Optimal top-down join enumeration

DeHaan

Tompa

2007

Proceedings of the 2007 ACM SIGMOD International Conference on Management of Data

View full text Add to dashboard Cite

Most contemporary database systems perform cost-based join enumeration using some variant of System-R's bottomup dynamic programming method. The notable exceptions are systems based on the top-down transformational search of Volcano/Cascades. As recent work has demonstrated, bottom-up dynamic programming can attain optimality with respect to the shape of the join graph; no comparable results have been published for transformational search. However, transformational systems leverage benefits of top-down search not available to bottom-up methods.In this paper we describe a top-down join enumeration algorithm that is optimal with respect to the join graph. We present performance results demonstrating that a combination of optimal enumeration with search strategies such as branch-and-bound yields an algorithm significantly faster than those previously described in the literature. Although our algorithm enumerates the search space top-down, it does not rely on transformations and thus retains much of the architecture of traditional dynamic programming. As such, this work provides a migration path for existing bottom-up optimizers to exploit top-down search without drastically changing to the transformational paradigm.

show abstract

Section: Transformational Searchmentioning

confidence: 93%

Section: Optimal Top-down Partitioningmentioning

confidence: 98%

Section: Accumulated-cost Boundingmentioning

confidence: 98%

Section: Transformational Searchmentioning

confidence: 99%

See 2 more Smart Citations

Optimal top-down join enumeration

DeHaan

Tompa

2007

Proceedings of the 2007 ACM SIGMOD International Conference on Management of Data

View full text Add to dashboard Cite

show abstract

“…Given incompleteness due to pruning of cross products, completeness of RS-B2 in the presence of cost based pruning (as proposed, for example, in the Columbia optimizer, [18]) also needs to be examined, as part of future work.…”

Section: Discussionmentioning

confidence: 99%

Optimizing join enumeration in transformation-based query optimizers

Shanbhag

Sudarshan

2014

Proc. VLDB Endow.

View full text Add to dashboard Cite

Query optimizers built on the Volcano/Cascades framework, which is based on transformation rules, are used in many commercial databases. Transformation rulesets proposed earlier for join order enumeration in such a framework either allow enumeration of joins with cross-products (which can significantly increase the cost of optimization), or generate a large number of duplicate derivations. In this paper we propose two new rulesets for generating cross-product free trees. One of the rulesets is a minor extension of a simple but inefficient ruleset, which we prove is complete (we also show that a naive extension of an efficient ruleset leads to incompleteness). We then propose an efficient new ruleset, which is based on techniques proposed recently for top-down join order enumeration, but unlike earlier work it is cleanly integrated into the Volcano/Cascades framework, and can be used in conjunction with other transformation rules. We show that our ruleset is complete (i.e., it generates the entire search space without cross products) while avoiding inefficiency due to duplicate derivations. We have implemented this ruleset in the PyroJ Optimizer (an implementation of the Volcano optimizer framework) and show that it significantly outperforms the alternatives, in some cases by up to two orders of magnitude, in terms of time taken.

show abstract