Physical structure design for relational databases

Abstract

This paper contains a description of the optimisation method for large databases with heterogeneous applications. This method makes it possible to automatically define the physical structure of a relational database. The input to the optimisation algorithm are the SQL queries stored in text files which are executed on the database server in a real life applications. Our tool recognises the classes of SQL queries and their frequency, analyses semantics and as a result prepares suggestions for the following parameters of physical definition of the database: the key and physical structure of a table (from amongst Btree, Isam, Hash and Heap), set of optimal indexes (this means their key as well as physical structure). The principal information which the system uses within the database optimisation process are: frequency and classes of SQL queries in an application and the data value distributions. The selection of the optimal configuration of the database relies a Electre Method which is a multicriteria optimal choice method. The use of these techniques increases the efficiency of the system first of all because it reduces the number of pages which are read and written to disks as a result of the optimal choice of the physical structures of tables and indexes. The system supports Oracle, Informix, Sybase and CA-Ingres.