Physical structure design for relational databases

  • Janusz Charczuk
Regular Papers Industrial Track
Part of the Lecture Notes in Computer Science book series (LNCS, volume 1475)


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.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    A.Albano, V. De Antonellis, A. Di Leva, Computer-Aided Database Design, North-Holland, 1985Google Scholar
  2. 2.
    B.Roy, Wielokryterialne wspomaganie decyzji, WNT Warszawa, 1990Google Scholar
  3. 3.
    B.Roy, Revue FranÇaise d’Informatique et de Recherche Opérationnalle 8, 1968, 57–75 Classesment et chiox en présence de points de vue multiple (La méthode ELECTRE)Google Scholar
  4. 4.
    S. Choenni, H. Blanken, T.Chang, On the Automation of Physical Database Design, Proc. of the ACM-SAC, 1993Google Scholar
  5. 5.
    S. Choenni, H. Blanken, T.Chang, Index Selection in Relational Databases, Proc. of the 5th IEEE ICCI, 1993Google Scholar
  6. 6.
    S.Chaudhuri, V.Narasayya, An Efficient, Cost-Driven Index Selection Tool for Microsoft SQL Server, Proc. of the 23th VLDB, 1997Google Scholar
  7. 7.
    Ch.Kilger, G.Moerkotte, Indexing Multiple Sets, Proc. of the 20th VLDB, 1994Google Scholar
  8. 8.
    W.Ogryczak, Wielokryterialna optymalizacja liniowa i dyskretna: modele preferencji i zastosowania do wspomagania decyzji, Wydawnictwa UW, Warszawa, 1997Google Scholar
  9. 9.
    H.Argenton, P.Becker, Efficient Retrieval of Labelled Binary Trees, Proc. of the ADTI, 1994Google Scholar
  10. 10.
    S.Ganguly, A.Goel, A.Silberschatz, Efficient and Accurate Cost Models for Parallel Query Optymalization, Proc. of the 15th PODS, 1996Google Scholar
  11. 11.
    INGRES Database Administrator’s Guide for the UNIX Operating System, Ingres Corporation, 1991Google Scholar
  12. 12.
    M.Syslo, N.Deo, J.Kowalik, Algorytmy optymalizacji dyskretnej, Wydawnictwo Naukowe PWN, Warszawa 1993Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 1998

Authors and Affiliations

  • Janusz Charczuk
    • 1
  1. 1.Rodan-SystemWarszawaPoland

Personalised recommendations