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.

[1]  Irving L. Traiger,et al.  The Recovery Manager of the System R Database Manager , 1981, CSUR.

[2]  Michael Stonebraker,et al.  The design and implementation of INGRES , 1976, TODS.

[3]  Donald E. Knuth,et al.  The art of computer programming: sorting and searching (volume 3) , 1973 .

[4]  Michael J. Carey,et al.  A Study of Index Structures for a Main Memory Database Management System , 1986, HPTS.

[5]  David H. D. Warren,et al.  Efficient Processing of Interactive Relational Data Base Queries expressed in Logic , 1981, VLDB.

[6]  Gio Wiederhold,et al.  Separability —An Approach to Physical Database Design , 1984, IEEE Transactions on Computers.

[7]  Ravi Krishnamurthy,et al.  Design of a Memory Resident DBMS , 1985, IEEE Computer Society International Conference.

[8]  Arvola Chan,et al.  Index selection in a self-adaptive data base management system , 1976, SIGMOD '76.

[9]  Masatoshi Yoshikawa,et al.  Query processing utilizing dependencies and horizontal decomposition , 1983, SIGMOD '83.

[10]  W. S. Luk.  On estimating block accesses in database organizations , 1983, CACM.

[11]  Matthias Jarke,et al.  Query Optimization in Database Systems , 1984, CSUR.

[12]  Robert Kooi,et al.  Query Optimization in INGRES. , 1982 .

[13]  Moshé M. Zloof.  QBE/OBE: A Language for Office and Business Automation , 1981, Computer.

[14]  Carolyn Turbyfill,et al.  Performance of complex queries in Main Memory Database Systems , 1987, 1987 IEEE Third International Conference on Data Engineering.

[15]  Michael J. Carey,et al.  Query processing in main memory database management systems , 1986, SIGMOD '86.

[16]  Toshihide Ibaraki,et al.  On the optimal nesting order for computing N-relational joins , 1984, TODS.

[17]  Shamkant B. Navathe,et al.  An Extended Disjunctive Normal Form Approach for Optimizing Recursive Logic Queries in Loosely Coupled Environments , 1987, VLDB.

[18]  Leonard D. Shapiro,et al.  Join processing in database systems with large main memories , 1986, TODS.

[19]  Gio Wiederhold,et al.  Database Design , 1977 .

[20]  Gio Wiederhold,et al.  Estimating block accesses in database organizations: a closed noniterative formula , 1983, CACM.

[21]  Frederick S. Hillier,et al.  Introduction to Operations Research (3rd ed.). , 1982 .

[22]  William F. Clocksin,et al.  Programming in Prolog , 1981, Springer Berlin Heidelberg.

[23]  H. M. Taylor,et al.  ESTIMATING BLOCK ACCESSES WHEN ATTRIBUTES ARE CORRELATED , 1986 .

[24]  Won Kim,et al.  On optimizing an SQL-like nested query , 1982, TODS.

[25]  Dina Bitton,et al.  Estimating Block Accessses when Attributes are Correlated , 1986, VLDB.

[26]  Moshé M. Zloof.  Office-by-Example: A Business Language that Unifies Data and Word Processing and Electronic Mail , 1982, IBM Syst. J..

[27]  Irving L. Traiger,et al.  System R: relational approach to database management , 1976, TODS.

[28]  R. Faure,et al.  Introduction to operations research , 1968 .

[29]  John Miles Smith,et al.  Optimizing the performance of a relational algebra database interface , 1975, CACM.

[30]  S. B. Yao,et al.  Optimization of query evaluation algorithms , 1979, ACM Trans. Database Syst..

[31]  Paolo Tiberio,et al.  Estimating the cost of updates in a relational database , 1985, TODS.

[32]  Moshé M. Zloof.  Query-by-Example: A Data Base Language , 1977, IBM Syst. J..

[33]  Donald D. Chamberlin,et al.  SEQUEL 2: A Unified Approach to Data Definition, Manipulation, and Control , 1976, IBM J. Res. Dev..

[34]  Patricia G. Selinger,et al.  Access path selection in a relational database management system , 1979, SIGMOD '79.

[35]  Carolyn Turbyfill,et al.  Performance Evaluation of Main Memory Database Systems , 1986 .

[36]  Carlo Zaniolo,et al.  Optimization of Nonrecursive Queries , 1986, VLDB.

[37]  Robert M. Pecherer,et al.  Efficient Evaluation of Expressions in a Relational Algebra , 1975, ACM Pacific.

[38]  Ravi Krishnamurthy,et al.  Office-by-example: an integrated office system and database manager , 1987, TOIS.

[39]  Michael Stonebraker,et al.  Implementation techniques for main memory database systems , 1984, SIGMOD '84.

[40]  Stavros Christodoulakis,et al.  Implications of certain assumptions in database performance evauation , 1984, TODS.

[41]  J. D. Uiiman,et al.  Principles of Database Systems , 2004, PODS 2004.

[42]  Eugene Wong,et al.  Decomposition—a strategy for query processing , 1976, TODS.

[43]  Gio Wiederhold,et al.  Separability - an approach to physical data base design , 1981, VLDB 1981.

[44]  Nils J. Nilsson,et al.  Artificial Intelligence , 1974, IFIP Congress.

[45]  William F. Clocksin,et al.  Programming in Prolog , 1987, Springer Berlin Heidelberg.

[46]  Patrick A. V. Hall,et al.  Optimization of a Single Relation Expression in a Relational Data Base System , 1976, IBM J. Res. Dev..

[47]  Moshé M. Zloof.  A Language for Office and Business Automation , 1979, Data Base Design Techniques II.