Query Optimization Algorithm Research Articles

Query optimization in the presence of limited access patterns

We consider the problem of query optimization in the presence of limitations on access patterns to the data (i.e., when one must provide values for one of the attributes of a relation in order to obtain tuples). We show that in the presence of limited access patterns we must search a space of annotated query plans , where the annotations describe the inputs that must be given to the plan. We describe a theoretical and experimental analysis of the resulting search space and a novel query optimization algorithm that is designed to perform well under the different conditions that may arise. The algorithm searches the set of annotated query plans, pruning invalid and non-viable plans as early as possible in the search space, and it also uses a best-first search strategy in order to produce a first complete plan early in the search. We describe experiments to illustrate the performance of our algorithm.

ADAPTIVE QUERY OPTIMIZATION IN DYNAMIC DATABASES

This paper addresses the problem of query optimization for databases in which objects frequently change their values. A greedy, adaptive query optimization algorithm is proposed to evaluate relational queries and queries containing complex objects. Rather than contructing a full plan for an access path and executing it, the algorithm constructs a partial plan, executes it, updates the statistics, and constructs a new partial plan. Since a partial plan is constructed based on the latest statistics, the algorithm is adaptive to data modifications and errors from the statistics. It is proved that the algorithm can produce an optimal solution for a class of queries. Furthermore, experiments show that the overhead associated with the algorithm is negligible and the algorithm is efficient for other cases.

Optimizing distributed join queries: A genetic algorithm approach

ptimizing join queries is a major problem in distributed database systems, particularly when files are replicated and copies stored at different nodes in the network. A distributed query optimization algorithm must select file copies and determine how and where those files will be processed. Process decisions include which files to reduce via semijoins, if any, the sites at which to perform join operations, and the order in which to perform those join operations. We extend the scope of distributed query optimization research by develop-ing a model that, for the first time, includes all of these design decisions and considers both communication and local processing costs. We develop a genetic algorithm-based solution procedure for this model which quickly determines efficient query processing plans. We demonstrate that ignoring local processing costs or restricting join processing to the result site, as commonly done in prior research, can result in inefficient query execution plans.

Adaptive Join Algorithms in Dynamic Distributed Databases

This paper addresses the problem of query optimization for dynamic databases in distributed environments where data frequently change their values. An adaptive query optimization algorithm is proposed to evaluate queries. Rather than constructing a full plan for an access path and executing it, the algorithm constructs a partial plan, executes it, updates the statistics, and constructs a new partial plan. Since a partial plan is constructed based on the latest statistics, the algorithm is adaptive to data modifications and errors from the statistics. The algorithm extends the SDD-1 algorithm by considering local processing cost as well as communication cost. Whereas the SDD-1 algorithm only uses semi-joins to reduce communication cost, the algorithm reduces it with joins as well. It is proved that the adaptive algorithm is more efficient than the SDD-1 algorithm.

Workload scheduling for multiple query processing

The design of query processing and optimization algorithms that utilize the database management system resources effectively is crucial for maximizing the system performance. We describe how to schedule a group of queries for processing. The novel feature of our approach is that we exploit sharings of relations or common intermediate results for pipelined plans

Evaluation and optimization of query programs in an object-oriented and symbolic information system

OASIS, a database programming environment that extends UNIX with the concept of persistent objects, is discussed. The OASIS query languages extend conventional database query languages with procedural methods and general control statements. As the complexity of the languages makes it difficult to device a query optimizer based on a universally applicable algorithm, each query in OASIS is optimized based on a collection of basic patterns for which each pattern is associated with a separation query optimization algorithm. The optimization techniques for a set of basic patterns consisting of iterative statements and a set of nested statements is described. The optimization techniques discussed include an extended decomposition algorithm, evaluation of multiple conditions, data dependence analysis, and optimization of queries with arbitrary nesting. >

Domain-specific semijoin: A new operation for distributed query processing

Many query optimization algorithms proposed for fragmented databases apply semijoins to reduce the size of the fragments of joining relations before they are sent to a final processing site. When semijoins are employed in such a system, they have to be performed in a relation-to-relation or a relation-to-fragment manner to avoid eliminating contributive tuples. To improve the semijoin operation, we introduce a new operation called domain-specific semijoin. This operation exploits the semantic information associated with the joining fragmented relations to reduce the size of fragments by eliminating noncontributive tuples. Domain-specific semijoins may be performed in a fragment-to-fragment manner and provide more flexibility in distributed query processing. It can be shown that for a given query, there is always a strategy, using both domain-specific semijoins and semijoins, which is at least as good as the best strategy using only semijoin reductions.

On the reusability of query optimization algorithms

We tackle the problem of software reusability as it pertains to an important class of nonrecursive query optimization algorithms. We demonstrate reusability can be achieved by (1) imposing standardized interfaces and (2) expressing algorithms in a DBMS-implementation-independent manner. The former is accomplished by generalizing the notion of query graphs, and the latter is accomplished by standardizing algorithm definitions in terms of query-graph rewrite rules. Demonstrating algorithm reusability is an essential step toward a building-block technology for extensible database systems.

Performance issues in the binary relationship model

In this paper we propose to use the Binary Relationship Model not only in the logical, but also in the physical level. At the logical level we propose and analyze a query optimization algorithm. The major optimization issue is to find an optimized join sequence for chain queries. A branch-and-bound search algorithm with dynamic programming is used in the search for such a sequence. At the physical level we propose to store the binary relations using multiattribute hashing. We develop a join algorithm which takes advantage of the clustering that multiattribute hashing achieves, and derive analytical formulas to predict its performance.

Distributed query processing a multiple database system

Mermaid is a testbed system which provides integrated access to multiple databases. Two query optimization algorithms have been developed for Mermaid. The semijoin algorithm tends to reduce the data transmission cost, while the replicate algorithm reduces the processing cost. An algorithm that integrates the features of these two algorithms to optimize the processing cost as well as the transmission cost is presented. A dynamic network environment is considered where processing speeds at each site and transmission speeds at each link can be variable. Moreover, distributed processing of aggregates is considered based on the functional dependency among the fragment attribute, the aggregate attribute, and the group-by attribute. Semantic information is utilized to obtain efficient query processing.< <ETX xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">&gt;</ETX>

Other comments on 'Optimization algorithms for distributed queries' by P.M.G. Apears

An erroneous fact concerning the assumption of irreducibility of nonjoining attributes of the distributed query optimization algorithm called GENERAL presented in the above paper (see ibid., vol.SE-9, no.1, p.57-68, Jan. 1983) is pointed out. It is shown that it is possible to generate an efficient semijoin program with better response time than the one produced by the GENERAL algorithm. A counterexample that proves this possibility is provided. >

Optimizing join queries in distributed databases

A reduced cover set of the set of full reducer semijoin programs for an acyclic query graph for a distributed database system is given. An algorithm is presented that determines the minimum cost full reducer program. The computational complexity of finding the optimal full reducer for a single relation is of the same order as that of finding the optimal full reducer for all relations. The optimization algorithm is able to handle query graphs where more than one attribute is common between the relations. A method for determining the optimum profitable semijoin program is presented. A low-cost algorithm which determines a near-optimal profitable semijoin program is outlined. This is done by converting a semijoin program into a partial order graph. This graph also allows one to maximize the concurrent processing of the semijoins. It is shown that the minimum response time is given by the largest cost path of the partial order graph. This reducibility is used as a post optimizer for the SSD-1 query optimization algorithm. It is shown that the least upper bound on the length of any profitable semijoin program is N(N-1) for a query graph of N nodes. >

Query optimization by simulated annealing

Query optimizers of future database management systems are likely to face large access plan spaces in their task. Exhaustively searching such access plan spaces is unacceptable. We propose a query optimization algorithm based on simulated annealing , which is a probabilistic hill climbing algorithm. We show the specific formulation of the algorithm for the case of optimizing complex non-recursive queries that arise in the study of linear recursion. The query answer is explicitly represented and manipulated within the closed semiring of linear relational operators. The optimization algorithm is applied to a state space that is constructed from the equivalent algebraic forms of the query answer. A prototype of the simulated annealing algorithm has been built and few experiments have been performed for a limited class of relational operators. Our initial experience is that, in general, the algorithm converges to processing strategies that are very close to the optimal. Moreover, the traditional processing strategies (e.g., the semi-naive evaluation ) have been found to be, in general, suboptimal.

Query optimization in distributed databases through load balancing

Abstract : As technology advances, computing environments composed of large numbers of workstations and mainframes connected by a high bandwidth local area network become attractive. These systems typically support a large number of application classes, and the heterogeneity of their workload, coupled with the decentralization of the systems, can lead to load imbalances across the network. This work attempts to study the benefits of load balancing strategies in the context of a particular application, distributed database systems. It was felt that, by focusing on a specific area, the problem would become more tractable. The choice of database management systems can be justified not only by their intrinsic importance, but also by the adaptability of load balancing strategies to query optimization algorithms. In order to determine whether load balancing strategies can indeed be adapted to current optimizers with a moderate amount of effort and to see whether the resulting performance benefits are sizable, both benchmarking and simulation experiments were carried out.

Some experiences with a distributed database testbed system

DDTS (Distributed Database Testbed System) was developed for the purpose of evaluating alternative approaches and algorithms for managing data stored in several databases on different machines. An overview of the system and information architectures of DDTS, as well as an example of the execution of a distributed request, are presented. The results of experiments with three query optimization algorithms are summerized. Our approach for dealing with semantic integrity constraints in a distributed system is reviewed. By sacrificing the enforcement of semantic integrity constraints that span sites, DDTS can be used as a federated distributed database management system.

Querying distributed databases on local area networks

Distributed databases on local area networks present additional considerations for query optimization over databases on geographically distributed, point-to-point networks. This paper surveys and evaluates the state of current research on distributed query optimization for local area networks. A classification taxonomy is presented and used to analyze the proposed query-optimization algorithms. The unique features of each algorithm are highlighted and a qualitative comparison of the algorithms is given. Future research directions are discussed.

Panel: Extensible database systems

New implementation techniques and new capabilities for database systems are being developed and proposed at a rapid rate. Novel file structures and improved algorithms for query optimization, buffer and recovery management, and transaction management have the potential of realizing significant gains in DBMS performance. The proposed integration of design objects, voice, text, rules, vector graphics, and images into databases promises exciting new capabilities for DBMSs. To accommodate advances in database technology and to support new classes of database applications, DBMSs must be extensible (i.e., customizable). To achieve extensibility forces a fundamental rethinking about how DBMSs are built, and how special-purpose features can be integrated into a DBMS with little effort and expense. Customizing DBMSs implies the availability of extensible data models, to allow for the introduction of new object types and operations, and extensible storage structures, to take advantage of special properties of stored data or operations to enhance performance. Although research on extensible DBMSs is still in its infancy, a fundamental concept underlying their construction is now evident. This is the standardization of interfaces and the plug-compatibility of modules. An extensible DBMS will be a 'software bus' whereby new modules (and hence new DBMS capabilities) can be added, exchanged, or removed by plugging or unplugging modules. Extensible DBMSs will thus rely on extensive software libraries, where new modules can be added as needed. Furthermore, changes to DBMSs can be made in months rather than years, and the reinvention of established technology is kept to a minimum because of the reusability of modules. The perception of DBMSs as monolithic entities that are difficult to modify will change as extensible DBMS technology becomes better understood. The use of database systems will not change, the ANSI/SPARC roles of database users, who write and execute transactions, and the database administrator (DBA), who designs and writes database schemas, will remain. Extensible DBMSs will require the introduction of an additional party, the database architecture administrator (DDA) , who is responsible for the construction and customization of a DBMS. A growing number of researchers are developing extensible DBMSs. The purpose of this panel is to explain and discuss some of the approaches that are now being taken (and those that can be taken), and to survey the problems that confront extensible database technology. Descriptions of the systems and research represented at this panel are given in the following sections.

An optimization of queries in distributed database systems

This paper addresses the processing of a query in distributed database systems using a sequence of semijoins. The objective is to minimize the intersite data traffic incurred by a distributed query. A method is developed which accurately and efficiently estimates the size of an intermediate result of a query. This method provides the basis of the query optimization algorithm. Since the distributed query optimization problem is known to be intractable, a heuristic algorithm is developed to determine a low-cost sequence of semijoins. The cost comparison with an existing algorithm is provided. The complexity of the main features of the algorithm is analytically derived. The scheduling time for sequences of semijoins is measured for example queries using the PASCAL program which implements the algorithm. All rights of reproduction in any form reserved.

Query optimization on local area networks

Local area networks are becoming widely used as the database communication framework for sophisticated information systems. Databases can be distributed among stations on a network to achieve the advantages of performance, reliability, availability, and modularity. Efficient distributed query optimization algorithms are presented here for two types of local area networks: address ring networks and broadcast networks . Optimal algorithms are designed for simple queries . Optimization principles from these algorithms guide the development of effective heuristic algorithms for general queries on both types of networks. Several examples illustrate distributed query processing on local area networks.