Efficient methods of processing unanticipated queries are a crucial prerequisite for the success of generalized database management systems. A wide variety of approaches to improve the performance of query evaluation algorithms have been proposed: logic-based and semantic transformations, fast implementations of basic operations, and combinatorial or heuristic algorithms for generating alternative access plans and choosing among them. These methods are presented in the framework of a general query evaluation procedure using the relational calculus representation of queries. In addition, nonstandard query optimization issues such as higher level query evaluation, query optimization in distributed databases, and use of database machines are addressed. The focus, however, is on query optimization in centralized database systems.

/pdf/query-optimization-in-database-systems-502cijfvvq.pdf

Query Optimization in Database Systems

In this paper we discuss the application of the dynamic clustering feature of hash to a relational data base machine. By partitioning the relation using hash, large load reductions in join and set operations are realized. Several machine architectures based on hash are presented. We propose a data base machineGRACE which adopts a novel relational algebraic processing algorithm based on hash and sort. Whereas conventional logic-per-track machines perform poorly in a join dominant environment,GRACE can execute join efficiently inO(N+M/K) time, whereN andM are the cardinalities of two relations andK the number of memory banks.

Application of hash to data base machine and its architecture

As network infrastructures with 10 Gb/s bandwidth and beyond have become pervasive and as cost advantages of large commodity-machine clusters continue to increase, research and industry strive to exploit the available processing performance for large-scale database processing tasks. In this work we look at the use of high-speed networks for distributed join processing. We propose Data Roundabout as alight weight transport layer that uses Remote Direct Memory Access (RDMA) to gain access to the throughput opportunities in modern networks. The essence of Data Roundabout is a ring shaped network in which each host stores one portion of a large database instance. We leverage the available bandwidth to (continuously) pump data through the high-speed network. Based on Data Roundabout, we demonstrate cyclo-join, which exploits the cycling flow of data to execute distributed joins. The study uses different join algorithms (hash join and sort-merge join) to expose the pitfalls and the advantages of each algorithm in the data cycling arena. The experiments show the potential of a large distributed main-memory cache glued together with RDMA into a novel distributed database architecture.

/pdf/a-spinning-join-that-does-not-get-dizzy-2d30fpl4bx.pdf

A Spinning Join That Does Not Get Dizzy

A graph model is presented to analyze the performance of a relational join. The amount of page reaccesses, the page access sequence, and the amount of buffer needed are represented in terms of graph parameters. By using the graph model formed from the index on the join attributes, we determine the relationships between these parameters. Two types of buffer allocation strategies are studied, and the upper bound on the buffer size with no page reaccess is given. This bound is shown to be the maximum cut value of a graph. Hence, the problem of computing this upper bound is NP-hard. We also give algorithms to determine a page access sequence requiring a near optimal buffer size with no page reaccess. The optimal page access sequence for a fixed buffer size has also been considered.

Use of graph-theoretic models for optimal relational database accesses to perform join

A methodology is formulated for determining if a microprocessor-based specialized system is practical for the solution of a problem in an application. This methodology is followed in the description and justification of of a back-end system for use with a statistical database system. The functions to be performed by the back-end system include those related to attribute partitioning, compression, and data access. It is shown that the system designed (the Microprocessor Assist System) will be costeffective in increasing the performance of a statistical data management system.

/pdf/microprocessor-assisted-tuple-access-decompression-and-5d2e8hfvtk.pdf

Microprocessor Assisted Tuple Access, Decompression and Assembly for Statistical Database Systems

In this paper, we will first prqpase a new organization of processors and memories for hardware realization of the relational join operation. Algorithmic analysis of the time complexity of the join operation shows that it is linear in the cardinalities of the source, target and result relations which is optimal.

Queueing analysis of the join operation is also used to arrive at closed-form equations for various design parameters such as the sizes of the memories associated with the processors. It is seen that in order to perform joins at a rate commensurate with the output rate of relational tuples, the memories associated with the processors in the hardware must satisfy certain constraints of speed and size. The various constraints and their order of importance are clearly indicated.

We also show how to select an optimum chip design for the join operation, where the design is considered optimal if it gives the best performance for a certain fixed cost. This method may be employed by database machine designers in order to arrive at the optimal values for (1) the number of processors on a chip, and (2) the sizes of the memories to be placed on a chip.

Due to the length of the paper, it is not possible to describe the extensions of this method to perform inequality joins and m-way joins. We also exclude a favorable comparison of this method with other schemes proposed for some other database machines. However, the reader may obtain detailed expositions on the extensions and comparative study in the References[9].

Menon K. Hsiao

Papers

Design and analysis of a relational join operation for VLSI