Evan P. Harris

Join algorithm costs revisited

Abstract. A method of analysing join algorithms based upon the time required to access, transfer and perform the relevant CPU-based operations on a disk page is proposed. The costs of variations of several of the standard join algorithms, including nested block, sort-merge, GRACE hash and hybrid hash, are presented. For a given total buffer size, the cost of these join algorithms depends on the parts of the buffer allocated for each purpose. For example, when joining two relations using the nested block join algorithm, the amount of buffer space allocated for the outer and inner relations can significantly affect the cost of the join. Analysis of expected and experimental results of various join algorithms show that a combination of the optimal nested block and optimal GRACE hash join algorithms usually provide the greatest cost benefit, unless the relation size is a small multiple of the memory size. Algorithms to quickly determine a buffer allocation producing the minimal cost for each of these algorithms are presented. When the relation size is a small multiple of the amount of main memory available (typically up to three to six times), the hybrid hash join algorithm is preferable.

Optimal dynamic multi-attribute hashing for range queries

This paper examines a partial match retrieval scheme which supports range queries for highly dynamic databases. The scheme relies on order preserving multi-attribute hashing. In general, designing optimal indexes is NP-hard. Greedy algorithms used to determine the optimal indexes for simple partial match queries are not directly applicable because there are a larger number of queries to consider in determining the optimal indexes. In this paper we present heuristic algorithms which provide near-optimal solutions. The optimisation scheme we propose can be used to design other dynamic file structures such as the grid file, BANG file and multilevel grid file to further enhance their retrieval performance taking into consideration the query distribution.

Optimal Clustering of Relations to Improve Sorting and Partitioning for Joins

The sorting or partitioning of relations is very common in relational database systems. Implementations of the join operation include the sort-merge join algorithm, which sorts both relations, and the hash join algorithm, which usually partitions both relations. We describe how clustering records using an optimal multi-attribute hash (MAH) file, taking the query pattern and distribution into account, reduces the average cost of sorting or partitioning. We demonstrate that maintaining multiple copies of a data file, each with a different clustering organization, further reduces the average cost of sorting or partitioning. We describe an inexpensive method for determining a good partitioning index (MAH file organization). Our analysis and experiments show that the partitioning indexes we find are usually optimal and can often partition a relation more than ten times faster than by not using any clustering. We also show that a significant change in the query pattern or distribution is required before a reorganization of a data file is necessary, and that such a reorganization is, in general, an inexpensive operation.

An environment for building graphical user interfaces for nested relational databases

Abstract This paper describes the architecture of an environment for building portable forms-based graphical user interfaces. The environment provides a particularly good platform for creating forms-based user interfaces to nested relational databases on a range of platforms, from workstations with bitmap screens to character based terminals. The major problems involved in creating these interfaces are the displaying of nested tables which would normally be larger than the available screen area, and the laying out of the elements of the forms-based interface inside the window. These problems are discussed and solutions to them are provided.

Efficient range query retrieval for non-uniform data distributions

Answering range queries is a common database operation. Methods based on hashing techniques to minimise the cost of answering range queries by taking the query distribution into account have previously been proposed. These methods have all assumed a uniform distribution of data to disk pages to achieve good performance. This assumption makes them less useful in practice because most real data distributions are non-uniform. In this paper, we discuss a method to eliminate this restriction. Extensive experimentation using a multi-dimensional file structure, the BANG file, indicates that our method results in good performance for all data distributions. In one case an improvement of over 36 times was achieved without compromising the storage utilisation. Our method also results in a stable and efficient file organisation. If the query distribution does not change substantially, an optimised file organisation rarely requires reorganisation.

Database Transactions in a Purely Declarative Logic Programming Language

We demonstrate how a purely declarative language, with the help of strict typing, precise moding, and determinism declarations, can be used to concisely and declaratively express database transactions, including updates. We have begun incorporating transactions into the Aditi deductive database system using an extended form of Mercury as the database programming and query language.