Rule-based query optimization, revisited

Abstract

We present the architecture and a performance assessment of an extensible query optimizer written in Venus. Venus is a generalpurpose active-database rule language embedded in C++. Following the developments in extensible database query optimizers, first in rule-based form, followed by optimizers written as object-oriented programs, the Venus-based optimizer avails to the advantages of both. Venus' modular structure allows us to go a step further and provide extensibility in search by defining parameterized search components in a declarative form that has the additional effect of integrating heuristic and cost-based optimization. We compare optimizers developed with Volcano, OPI++ and Venus. Venus' optimizing compiler yields code whose performance is comparable with Volcano and OPT++ on smaller queries. The ability to introduce additional pruning heuristics yields better scalability on larger queries. Evaluation of the system using quantitative software metrics supports a claim that the Venusbased optimizer is more easily maintained and extended than are its predecessors.

Simpli-Squared: A Very Simple Yet Unexpectedly Powerful Join Ordering Algorithm Without Cardinality Estimates

“…Rule-based Query Optimization Unlike a cardinality estimation-based optimizer, a rule-based optimizer relies on a set of predefined rules to prune the optimization search space and decide on an optimal plan for query execution. Most rule-based optimizers [6,23,37,4,3,2,26,33,39,34] develop specialized rule languages and execution environments to avoid unwanted compatibility issues and accelerate the enumeration process and query execution. Held et al [6] introduce the first rule-based optimizer in Ingres, where the original query is decomposed into single-valued subqueries and executed separately in a greedy cardinality-based fashion.…”

The use of Hints in SQL-Nested query optimization

“…Query optimization is the most important stage in query processing where the database optimizer has to choose a query-evaluation plan with minimized cost and maximized performance [4,13,25,27]. The functionality of query optimization in deciding the best query execution plan is a significant task since there are a lot of possible queries in terms of the canonically equivalent algebraic expression for one given SQL query [4,7,8].…”

“…Moreover, a nested query optimization consumes a larger number of resources such as physical disk reads and memory buffers when compared with a non-nested query [25]. Hints could significantly speed up the system's performance and reduce the resources needed for processing the nested queries.…”