Access Path Selection in a Relational Database Management System

Selinger, Patricia G.; Astrahan, M. M.; Chamberlin, Donald D.; Lorie, Raymond A.; Price, Thomas G.

doi:10.1016/b978-0-934613-53-8.50038-8

Cited by 52 publications

(65 citation statements)

References 9 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…Second, note that given two groupings g and g ⊂ g and a tuple stream R satisfying the grouping g, R need not satisfy the grouping g . For example the tuple stream ((1, 2), (2,3), (1,4)) with the schema (a, b) is grouped by {a, b}, but not by {a}. This is different from orderings, where a tuple stream satisfying a ordering o also satisfies all orderings that are a prefix of o.…”

Section: Groupingmentioning

confidence: 99%

“…The importance of exploiting available orderings has been recognized in the seminal work of Selinger et al [4]. They presented the concept of interesting orderings and showed how redundant sort operations could be avoided by reusing available orderings, rendering sort-based operators like sort-merge join much more interesting.…”

Section: Introductionmentioning

confidence: 99%

“…To avoid redundant sorting it is required to keep track of the orderings a certain plan satisfies. The orderings that are relevant for query optimization are called interesting orders [4]. The set of interesting orders for a given query consists of 1. all orderings required by an operator of the physical algebra that may be used in a query execution plan for the given query, and 2. all orderings produced by an operator of the physical algebra that may be used in a query execution plan for the given query.…”

Section: Orderingmentioning

confidence: 99%

See 2 more Smart Citations

A Combined Framework for Grouping and Order Optimization

NEUMANN¹,

MOERKOTTE²

2004

Proceedings 2004 VLDB Conference

View full text Add to dashboard Cite

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.

show abstract

Section: Groupingmentioning

confidence: 99%

Section: Introductionmentioning

confidence: 99%

Section: Orderingmentioning

confidence: 99%

See 1 more Smart Citation

A Combined Framework for Grouping and Order Optimization

NEUMANN¹,

MOERKOTTE²

2004

Proceedings 2004 VLDB Conference

View full text Add to dashboard Cite

show abstract

“…Recently, many previous works are focused on algorithms of obtaining an optimal access plan of a complex query. These algorithms are Divided into four different categories: (a) Deterministic Algorithms, Every algorithm in this category creates a solution, systematically, in a deterministic way, either by applying a heuristic search technique [24], (b) Randomized Algorithms, which aim to find the state which solution gives the globally minimum cost. [25] (such as Iterative Improvement Algorithm [27], Simulated Annealing Algorithm (SA) [28], Two-Phase Optimization (2PO) [26], Toured Simulated Annealing [29], and Random Sampling [30]), (c) Genetic Algorithms, which inspire the biological evolution by using a randomized search technique, while looking for good problem solutions [31].…”

Section: The Query Access Plan Problemmentioning

confidence: 99%

“…The members that survive the subsequent selection are considered the "fittest" ones [32], and the next generation relies on them. And the algorithm reaches its end when there is no further improvement and the solution is represented by the fittest member of the last population [24], and (d) Hybrid algorithms, which combine the strategies of pure deterministic and pure randomized algorithms: solutions obtained by deterministic algorithms are used as starting points for randomized algorithms or as initial population members for genetic algorithms [16], [20].…”

Section: The Query Access Plan Problemmentioning

confidence: 99%

An Enhanced Ant Colony-based Approach for Query Optimization

Hanafy¹,

Gadallah²,

Hefny³

2017

IJCA

View full text Add to dashboard Cite

One of the mandatory processes for all those types of applications is the inquiry process of the stored huge amounts of data. Such process is either a predefined or an ad-hoc query. From the logical point of view, the query process depends mainly on many algebraic operations, including selection, projection and joining operations. The most important one of them is the join operation, which represents the key factor of the inquiry process to retrieve the related information from different data tables. Many approaches have been proposed aiming to reduce the cost of join operations. Yet, there is still a need for more query optimizing processes in order to reduce the query response time. This paper proposes an enhanced optimal query processing approach for inner and outer join, where the proposed model exploits an adopted Ant Colony Optimization.

show abstract

Query Optimization

and

Haug

2010

Distributed Database Management Systems

View full text Add to dashboard Cite

In this chapter, we provide an overview of query processing with the emphasis on optimizing queries in centralized and distributed database environments. It is a welldocumented fact that for a given query there are many evaluation alternatives. The reason for the existence of a large number of alternatives (solution space) is the vast number of factors that affect query evaluation. These factors include the number of relations in the query, the number of operations to be performed, the number of predicates applied, the size of each relation in the query, the order of operations to be performed, the existence of indexes, and the number of alternatives for performing each individual operation-just to name a few. In a distributed system, there are other factors, such as the fragmentation details for the relations, the location of these fragments/tables in the system, and the speed of communication links connecting the sites in the system. The overhead associated with sending messages and the overhead associated with the local processing speed increase exponentially as the number of available alternatives increases. It is therefore generally acceptable to merely try to find a "good" alternative execution plan for a given query, rather than trying to find the "best" alternative.A query running against a distributed database environment (DDBE) will have to go through two types of optimization. The first type of optimization is done at the global level, where communication cost is a prominent factor. The second type of optimization is done at the local level. This is what each local DBE performs on the fragments that are stored at the local site, where the local CPU and, more importantly, the disk input/output (I/O) time are the main drivers. Almost all global optimization alternatives ignore the local processing time. When these alternatives were being developed, it was believed that the communication cost was a more dominant factor than the local processing cost. Now, it is believed that both the local query cost and the global communication cost are important to query optimization. 111 112 QUERY OPTIMIZATIONSuppose we have two copies of a relation at two different servers, where the first server is a lot faster than the second server, but at the same time, the connection to the first server is a lot slower than the connection to the second server (perhaps we are closer to the second server). An optimization strategy that only considered communication cost would choose the second server to run the local query. This will not necessarily be the best strategy, due to the speed of the chosen (second) server. The overall time to run a query in a distributed system consists of the time it takes to communicate local queries to local DBEs; the time it takes to run local query fragments; the time it takes to assemble the data and generate the final results; and the time it takes to display the results to the user. Therefore, to study distributed query optimization, we need to understand how a query is optimized both locally an...

show abstract

Access Path Selection in a Relational Database Management System

Cited by 52 publications

References 9 publications

A Combined Framework for Grouping and Order Optimization

A Combined Framework for Grouping and Order Optimization

An Enhanced Ant Colony-based Approach for Query Optimization

Query Optimization

Contact Info

Product

Resources

About