Results 1  10
of
21
Optimizing Queries with Materialized Views
, 1995
"... While much work has addressed the problem of maintaining materialized views, the important question of optimizing queries in the presence of materialized views has not been resolved. In this paper, we analyze the optimization question and provide a comprehensive and efficient solution. Our solution ..."
Abstract

Cited by 258 (5 self)
 Add to MetaCart
While much work has addressed the problem of maintaining materialized views, the important question of optimizing queries in the presence of materialized views has not been resolved. In this paper, we analyze the optimization question and provide a comprehensive and efficient solution. Our solution has the desirable property that it is a simple generalization of the traditional query optimization algorithm. 1 Introduction The idea of using materialized views for the benefit of improved query processing has been proposed in the literature more than a decade ago. In this context, problems such as definition of views, composition of views, maintenance of views [BC79, KP81, SI84, BLT86, CW91, Rou91, GMS93] have been researched but one topic has been conspicuous by its absence. This concerns the problem of the judicious use of materialized views in answering a query. It may seem that materialized views should be used to evaluate a query whenever they are applicable. In fact, blind applicat...
Query Optimization
, 1996
"... Imagine yourself standing in front of an exquisite buffet filled with numerous delicacies. Your goal is to try them all out, but you need to decide in what order. What exchange of tastes will maximize the overall pleasure of your palate? Although much less pleasurable and subjective, that is the typ ..."
Abstract

Cited by 146 (4 self)
 Add to MetaCart
Imagine yourself standing in front of an exquisite buffet filled with numerous delicacies. Your goal is to try them all out, but you need to decide in what order. What exchange of tastes will maximize the overall pleasure of your palate? Although much less pleasurable and subjective, that is the type of problem that query optimizers are called to solve. Given a query, there are many plans that a database management system (DBMS) can follow to process it and produce its answer. All plans are equivalent in terms of their final output but vary in their cost, i.e., the amount of time that they need to run. What is the plan that needs the least amount of time? Such query optimization is absolutely necessary in a DBMS. The cost difference between two alternatives can be enormous. For example, consider the following database schema, which will be...
Including GroupBy in Query Optimization
, 1994
"... In existing relational database systems, processing of groupby and computation of aggregate functions are always postponed until all joins are performed. In this paper, we present transformations that make it possible to push groupby operation past one or more joins and can potentially reduce the ..."
Abstract

Cited by 121 (7 self)
 Add to MetaCart
In existing relational database systems, processing of groupby and computation of aggregate functions are always postponed until all joins are performed. In this paper, we present transformations that make it possible to push groupby operation past one or more joins and can potentially reduce the cost of processing a query significantly. Therefore, the placement of groupby should be decided based on cost estimation. We explain how the traditional SystemR style optimizers can be modified by incorporating the greedy conservative heuristic that we developed. We prove that applications of greedy conservative heuristic produce plans that are better (or no worse) than the plans generated by a traditional optimizer. Our experimental study shows that the extent of improvement in the quality of plans is significant with only a modest increase in optimization cost. Our technique also applies to optimization of Select Distinct queries by pushing down duplicate elimination in a costbased fas...
Leftdeep vs. bushy trees: An analysis of strategy spaces and its implications for query optimization
 In Proceedings of the ACM SIGMOD International Conference on Management of Data
, 1991
"... We present a combination of analytical and experimental results that shed some light into the shape of the cost function of the strategy spaces that query optimizers must deal with. These are the space that includes only leftdeep trees and the space that includes koth deep and bushy trees. We concl ..."
Abstract

Cited by 75 (2 self)
 Add to MetaCart
We present a combination of analytical and experimental results that shed some light into the shape of the cost function of the strategy spaces that query optimizers must deal with. These are the space that includes only leftdeep trees and the space that includes koth deep and bushy trees. We conclude that the cost functions of both spaces essentially form a “well ” but of a distinctly different quality. Based on this result, we discuss how Iterative Improvement, Simulated Annealing, and Two Phase Optimization perform on these spaces. We conclude that the space of both deep and bushy trees is easier to optimize than the space of leftdeep trees alone. 1.
The Complexity of TransformationBased Join Enumeration
, 1997
"... Query optimizers that explore a search space exhaustively using transformation rules usually apply all possible rules on each alternative, and stop when no new information is produced. A memoizing structure was proposed in [McK93] to improve the reuse of common subexpression, thus improving the eff ..."
Abstract

Cited by 32 (4 self)
 Add to MetaCart
Query optimizers that explore a search space exhaustively using transformation rules usually apply all possible rules on each alternative, and stop when no new information is produced. A memoizing structure was proposed in [McK93] to improve the reuse of common subexpression, thus improving the efficiency of the search considerably. However, a question that remained open is, what is the complexity of the transformationbased enumeration process ? In particular, with n the number of relations, does it achieve the O(3 n ) lower bound established by [OL90]? In this paper we examine the problem of duplicates, in transformationbased enumeration. In general, different sequences of transformation rules may end up deriving the same element, and the optimizer must detect and discard these duplicate elements generated by multiple paths. We show that the usual commutativity/associativity rules for joins generate O(4 n ) duplicate opera Permission to copy without fee all or part of this ma...
OPT++: An ObjectOriented Implementation for Extensible Database Query Optimization
 VLDB Journal
, 1999
"... Paper Number 323 In this paper we describe the design and implementation of OPT++, a tool for Extensible Database Query Optimization that uses an objectoriented design to simplify the task of implementing, extending, and modifying an optimizer. Building an optimizer using OPT++ makes it easy to ext ..."
Abstract

Cited by 29 (1 self)
 Add to MetaCart
(Show Context)
Paper Number 323 In this paper we describe the design and implementation of OPT++, a tool for Extensible Database Query Optimization that uses an objectoriented design to simplify the task of implementing, extending, and modifying an optimizer. Building an optimizer using OPT++ makes it easy to extend the query algebra (to add new query algebra operators and physical implementation algorithms to the system), easy to change the search space explored, and also easy to change the search strategy used. Furthermore, OPT++ comes equipped with a number of search strategies that are available for use by an Optimizer{ Implementor. OPT++ considerably simpli es both, the task of implementing an optimizer for a new database system, and the task of experimenting with various optimization techniques and strategies to decide what techniques are best suited for that database system. We present the results of performance studies which validate our design and show that, in spite of its exibility, OPT++ can be used to build e cient optimizers. 1
On the Estimation of Join Result Sizes
, 2006
"... Good estimates of join result sizes are critical for query optimization in relational database management systems. We address the problem of incrementally obtaining accurate and consistent estimates of join result sizes. We have invented a new rule for choosing join selectivities for estimating j ..."
Abstract

Cited by 21 (0 self)
 Add to MetaCart
Good estimates of join result sizes are critical for query optimization in relational database management systems. We address the problem of incrementally obtaining accurate and consistent estimates of join result sizes. We have invented a new rule for choosing join selectivities for estimating join result sizes. The rule is part of a new unified algorithm called Algorithm ELS (Equivalence and Largest Selectivity). Prior to computing any result sizes, equivalence classes are determined for the join columns. The algorithm also takes into account the effect of local predicates on table and column cardinalities. These computations allow the correct selectivity values for each eligible join predicate to be computed. We show that the algorithm is correct and gives better estimates than current estimation algorithms.
UniformlyDistributed Random Generation of Join Orders
, 1995
"... In this paper we study the space of operator trees that can be used to answer a join query, with the goal of generating elements form this space at random. We solve the problem for queries with acyclic query graphs. First, we count the exact number of trees that can be used to evaluate a given query ..."
Abstract

Cited by 14 (11 self)
 Add to MetaCart
In this paper we study the space of operator trees that can be used to answer a join query, with the goal of generating elements form this space at random. We solve the problem for queries with acyclic query graphs. First, we count the exact number of trees that can be used to evaluate a given query. Then, we establish a mapping between the n operator trees for a query and the integers 1 through n i. e. a ranking and describe efficient ranking and unranking procedures. The generation of random, uniformly distributed operator trees follows from the unraking.
Fast, Randomized JoinOrder Selection  Why Use Transformations?
 IN PROC. OF THE CONF. ON VERY LARGE DATA BASES (VLDB
, 1994
"... We study the effectiveness of probabilistic selection of joinquery evaluation plans without reliance on tree transformation rules. Instead, each candidate plan is chosen uniformly at random from the space of valid evaluation orders. This leads to a transformationfree strategy where a sequence o ..."
Abstract

Cited by 6 (0 self)
 Add to MetaCart
We study the effectiveness of probabilistic selection of joinquery evaluation plans without reliance on tree transformation rules. Instead, each candidate plan is chosen uniformly at random from the space of valid evaluation orders. This leads to a transformationfree strategy where a sequence of random plans is generated and the plans are compared on their estimated costs. The success of this strategy depends on the ratio of "good" evaluation plans in the space of alternatives, the efficient generation of random candidates, and an accurate estimation of their cost. To avoid a biased exploration of the space, we solved the open problem of efficiently generating random, uniformlydistributed evaluation orders, for queries with acyclic graphs. This benefits any optimization or sampling scheme in which a random choice of (initial) query plans is required. A direct comparison with iterative improvement and simulated annealing, using a proven costevaluator, shows that our transformationfree strategy converges faster and yields solutions of comparable cost.
Query Rewriting and Search in CROQUE
, 1998
"... In query optimization, a given query will be transformed by rewrite rules into an equivalent execution plan that is cheaper than the straightforwardly assigned plan according to some cost model. Finding the cheapest of all equivalent execution plans is a challenge since the rewriting of complex que ..."
Abstract

Cited by 4 (0 self)
 Add to MetaCart
In query optimization, a given query will be transformed by rewrite rules into an equivalent execution plan that is cheaper than the straightforwardly assigned plan according to some cost model. Finding the cheapest of all equivalent execution plans is a challenge since the rewriting of complex queries on the basis of a large set of rewriting rules may potentially span a very large space of equivalent plans. Consequently, one has to either use search strategies to explore (parts of) the search space or some heuristics to prune this space thus making it efficiently traversable. This paper presents the use of search strategies in the CROQUE project. The adaptation of some common strategies led to the development of a simple but powerful heuristics which is demonstrated by examples executed in the CROQUE prototype. The proposed heuristics can support any randombased search strategy or can be used standalone. It may be integrated seamlessly into most of the present query optimizers w...