Selecting an optimal set of secondary indices

  • Theo Härder
Concepts And Techniques Of Data Base Management
Part of the Lecture Notes in Computer Science book series (LNCS, volume 44)


The problem of finding an optimal set of secondary indices for a given transaction load is considered. In our model the description of queries, updates, insertions and deletions, which are characterized by their type and probability, is provided. For simple transaction types, which refer only to one single attribute, a "decoupled model" is established. Each attribute can be considered separately so that simple analytic solutions are obtained. With respect to inversion a lower boundary depending on detailed storage and processing characteristics exists for the number of attribute values per attribute.

For complex transaction types their exact attribute combinations and their probabilities are taken into account. To find an optimal index set, in the general case the powerset of all attributes must be examined. In special cases one can get simple solutions.

A prediction procedure for the estimation of the access time behavior during the dynamic evaluation of secondary indices is given.


Predicate Tree Access Rate Access Cost Access Path Statistical Query 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.


  1. (As74).
    Astrahan, M.M., Chamberlin, D.D.: Implementation of a Structured English Query Language, IBM Research Report, RJ 1464, San José, Oct. 28, 1974.Google Scholar
  2. (Ca73).
    Cardenas, A.F.: Evaluation and Selection of File Organization — a Model and System, in: CACM, Vol. 16, No. 9, Sept. 1975, pp. 540–548.Google Scholar
  3. (Co70).
    Codd, E.F.: A Relational Model of Data for Large Shared Data Banks, in: CACM, Vol. 13, No. 6, June 1970, pp. 377–387.Google Scholar
  4. (Hä75).
    Härder, T.: Auswahl optimaler Indexmengen, Research Report DV 75-2, FG DVS, TH Darmstadt, Sept. 1975.Google Scholar
  5. (Ka75).
    Karlowsky, I., Leilich, H.-D., Stiege, G.: Ein Suchrechnerkonzept für Datenbankanwendungen, in: Elektronische Rechenanlagen 17 (1975), No. 3, pp. 108–118.Google Scholar
  6. (Ki74).
    King, W.F.: On the Selection of Indices for a File, IBM Research Report, RJ 1341, San José, 1974.Google Scholar
  7. (Lu71).
    Lum, V.A., Ling, H.: An Optimization Problem on the Selection of Secondary Keys, in: Proc. of ACM National Conference, 1971, pp. 349–356.Google Scholar
  8. (Ro74).
    Rothnie, J.B., Lozano, T.: Attribute Based File Organization in a Paged Memory Environment, in: CACM, Vol. 17, No. 2, Feb. 1974, pp. 63–69.Google Scholar
  9. (Sk74).
    Schkolnick, M.: Optimizing Partial Inversions for Files, IBM Research Report, RJ 1477, San José, Nov. 21, 1974.Google Scholar
  10. (St74).
    Stonebraker, M.: The Choice of Partial Inversions and Combined Indices, appears in Journal of Computer and Information Sciences.Google Scholar
  11. (Wa73).
    Wang, C.P.: Parametrization of Information System Application, IBM Research Report, RJ 1199, San José, April 11, 1973.Google Scholar
  12. (We73).
    Wedekind, H.: Systemanalyse, Carl Hanser Verlag, München 1973.Google Scholar
  13. (Wo71).
    Wong, E., Chiang, T.C.: Canonical Structure in Attribute Based File Organization, in: CACM, Vol. 14, No. 9, Sept. 1971, pp. 593–597.Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 1976

Authors and Affiliations

  • Theo Härder
    • 1
  1. 1.Technische Hochschule DarmstadtGermany

Personalised recommendations