Query Processing Strategies Research Articles

Query optimization in a memory-resident domain relational calculus database system

We present techniques for optimizing queries in memory-resident database systems. Optimization techniques in memory-resident database systems differ significantly from those in conventional disk-resident database systems. In this paper we address the following aspects of query optimization in such systems and present specific solutions for them: (1) a new approach to developing a CPU-intensive cost model; (2) new optimization strategies for main-memory query processing; (3) new insight into join algorithms and access structures that take advantage of memory residency of data; and (4) the effect of the operating system's scheduling algorithm on the memory-residency assumption. We present an interesting result that a major cost of processing queries in memory-resident database systems is incurred by evaluation of predicates. We discuss optimization techniques using the Office-by-Example (OBE) that has been under development at IBM Research. We also present the results of performance measurements, which prove to be excellent in the current state of the art. Despite recent work on memory-resident database systems, query optimization aspects in these systems have not been well studied. We believe this paper opens the issues of query optimization in memory-resident database systems and presents practical solutions to them.

Query processing techniques in the summary-table-by-example database query language

Summary-Table-by-Example (STBE) is a graphical language suitable for statistical database applications. STBE queries have a hierarchical subquery structure and manipulate summary tables and relations with set-valued attributes. The hierarchical arrangement of STBE queries naturally implies a tuple-by-tuple subquery evaluation strategy (similar to the nested loops join implementation technique) which may not be the best query processing strategy. In this paper we discuss the query processing techniques used in STBE. We first convert an STBE query into an “extended” relational algebra (ERA) expression. Two transformations are introduced to remove the hierarchical arrangement of subqueries so that query optimization is possible. To solve the “empty partition” problem of aggregate function evaluation, directional join (one-sided outer-join) is utilized. We give the algebraic properties of the ERA operators to obtain an “improved” ERA expression. Finally we briefly discuss the generation of alternative implementations of a given ERA expression. STBE is implemented in a prototype statistical database management system. We discuss the STBE-related features of the implemented system.

A knowledge-based approach to multiple query processing

The collective processing of multiple queries in a database system has recently received renewed attention due to its capability of improving the overall performance of a database system and its applicability to the design of knowledge-based expert systems and extensible database systems. A new multiple query processing strategy is presented which utilizes semantic knowledge on data integrity and information on predicate conditions of the access paths (plans) of queries. The processing of multiple queries is accomplished by the utilization of subset relationships between intermediate results of query executions, which are inferred employing both semantic and logical information. Given a set of fixed order access plans, the A ∗ algorithm is used to find the set of reformulated access plans which is optimal for a given collection of semantic knowledge.

An evaluation of relational join algorithms in a pipelined query processing environment

A query processing strategy which is based on pipelining and data-flow techniques is presented. Timing equations are developed for calculating the performance of four join algorithms: nested block, hash, sort-merge, and pipelined sort-merge. They are used to execute the join operation in a query in distributed fashion and in pipelined fashion. Based on these equations and similar sets of equations developed for other relational algebraic operations, the performance of query execution was evaluated using the different join algorithms. The effects of varying the values of processing time, I/O time, communication time, buffer size, and join selectively on the performance of the pipelined join algorithms are investigated. The results are compared to the results obtained by employing the same algorithms for executing queries using the distributed processing approach which does not exploit the vertical concurrency of the pipelining approach. These results establish the benefits of pipelining.< <ETX xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">&gt;</ETX>

Document query processing strategies: cost evaluation and heuristics

This paper describes query processing strategies used in a system where queries are specified on formatted data and text components of documents. The system provides different access methods: indexes for formatted data and signature files for the text. Four basic document queries, representative of a wide range of queries, are examined in detail. Strategies for query processing are presented together with an evaluation of the costs of the various strategies. Finally, general heuristics are derived.

The design of POSTGRES

This paper presents the preliminary design of a new database management system, called POSTGRES, that is the successor to the INGRES relational database system. The main design goals of the new system are to provide better support for complex objects, provide user extendibility for data types, operators and access methods, provide facilities for active databases (i.e., alerters and triggers) and inferencing including forward- and backward-chaining, simplify the DBMS code for crash recovery, produce a design that can take advantage of optical disks, workstations composed of multiple tightly-coupled processors, and custom designed VLSI chips, and make as few changes as possible (preferably none) to the relational model. The paper describes the query language, programming language interface, system architecture, query processing strategy, and storage system for the new system.

A parallel pipelined relational query processor

This paper presents the design of a relational query processor. The query processor consists of only four processing PIPEs and a number of random-access memory modules. Each PIPE processes tuples of relations in a bit-serial, tuple-parallel manner for each of the primitive database operations which comprise a complex relational query. The design of the query processor meets three major objectives: the query processor must be manufacturable using existing and near-term LSI (VLSI) technology; it must support in a uniform manner both the numeric and nonnumeric processing requirements a high-level user interface like SQL presents; and it must support the query-processing strategy derived in the query optimizer to satisfy certain system-wide performance optimality criteria.

Implementation techniques for main memory database systems

With the availability of very large, relatively inexpensive main memories, it is becoming possible keep large databases resident in main memory In this paper we consider the changes necessary to permit a relational database system to take advantage of large amounts of main memory We evaluate AVL vs B+-tree access methods for main memory databases, hash-based query processing strategies vs sort-merge, and study recovery issues when most or all of the database fits in main memory As expected, B+-trees are the preferred storage mechanism unless more than 80--90% of the database fits in main memory A somewhat surprising result is that hash based query processing strategies are advantageous for large memory situations

On the design of a query processing strategy in a distributed database environment

An algorithm is given to process a given query in a fragmented distributed data base environment. Unlike previous algorithms, it has the following desired features.(1) It makes use of redundant relations to reduce communication cost;(2) a copy of each relation referenced by the query is selected so that the set of relations are contained in the minimum number of sites;(3) an efficient algorithm to process fragments is provided;(4) all relations that need not be sent to the assembly site to produce the answer are identified. Thus, unnecessary sending of these relations across sites and processing on these relations, which are common in earlier algorithms, are avoided;(5) useless semi-joins are discarded and "worse" semi-joins are replaced by better ones;(6) a process to estimate the cost and the benefit of a semi-join, based on dynamic execution of semi-joins is introduced. It is expected that the new process is more accurate than earlier estimation process.The algorithm is easy to implement and is operational.

Optimization Algorithms for Distributed Queries

The efficiency of processing strategies for queries in a distributed database is critical for system performance. Methods are studied to minimize the response time and the total time for distributed queries. A new algorithm (Algorithm GENERAL) is presented to derive processing strategies for arbitrarily complex queries. Three versions of the algorithm are given: one for minimizing response time and two for minimizing total time. The algorithm is shown to provide optimal solutions under certain conditions.

Processor allocation strategies for multiprocessor database machines

In this paper four alternative strategies for assigning processors to queries in multiprocessor database machines are described and evaluated. The results demonstrate that SIMD database machines are indeed a poor design when their performance is compared with that of the three MIMD strategies presented. Also introduced is the application of data-flow machine techniques to the processing of relational algebra queries. A strategy that employs data-flow techniques is shown to be superior to the other strategies described by several experiments. Furthermore, if the data-flow query processing strategy is employed, the results indicate that a two-level storage hierarchy (in which relations are paged between a shared data cache and mass storage) does not have a significant impact on performance.

Oxidized porous silicon isolates better than sapphire : Charles Cohen. Electronics77 (27 January 1981)

In the past two decades, a considerable amount of research exists which uses hardware, firmware, and novel architectures to achieve the needed efficiency in implementing database management functions. However, most of the past efforts have been directed toward developing database computers for supporting a relatively primitive data model, namely, the relational model. This paper presents the design and evaluation of an Object Flow Computer (OFC). OFC is designed to efficiently support the processing of object-oriented databases. OFC employs a vertically fragmented data storage structure and a two-phase parallel query processing strategy. A set of primitive operators is defined for OFC. Depending on the performance requirement, these operators can be implemented in software running on general-purpose processors or as functions in special-purpose coprocessors. A high-level database request can be decomposed into these primitive operators and executed in parallel. OFC combines a number of known database processing techniques such as query decomposition, pipelining mode of data processing, and data flow control strategy. The performance evaluation of the proposed two-phase query processing strategy and a comparison with the conventional query processing strategy are also presented.

Over a million devices make up 32-bit CPU and support chips : Bruce Leboss. Electronics39 (10 February 1981)

In the past two decades, a considerable amount of research exists which uses hardware, firmware, and novel architectures to achieve the needed efficiency in implementing database management functions. However, most of the past efforts have been directed toward developing database computers for supporting a relatively primitive data model, namely, the relational model. This paper presents the design and evaluation of an Object Flow Computer (OFC). OFC is designed to efficiently support the processing of object-oriented databases. OFC employs a vertically fragmented data storage structure and a two-phase parallel query processing strategy. A set of primitive operators is defined for OFC. Depending on the performance requirement, these operators can be implemented in software running on general-purpose processors or as functions in special-purpose coprocessors. A high-level database request can be decomposed into these primitive operators and executed in parallel. OFC combines a number of known database processing techniques such as query decomposition, pipelining mode of data processing, and data flow control strategy. The performance evaluation of the proposed two-phase query processing strategy and a comparison with the conventional query processing strategy are also presented.