Selecting an optimal set of secondary indices
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.
KeywordsPredicate Tree Access Rate Access Cost Access Path Statistical Query
- (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
- (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
- (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
- (Hä75).Härder, T.: Auswahl optimaler Indexmengen, Research Report DV 75-2, FG DVS, TH Darmstadt, Sept. 1975.Google Scholar
- (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
- (Ki74).King, W.F.: On the Selection of Indices for a File, IBM Research Report, RJ 1341, San José, 1974.Google Scholar
- (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
- (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
- (Sk74).Schkolnick, M.: Optimizing Partial Inversions for Files, IBM Research Report, RJ 1477, San José, Nov. 21, 1974.Google Scholar
- (St74).Stonebraker, M.: The Choice of Partial Inversions and Combined Indices, appears in Journal of Computer and Information Sciences.Google Scholar
- (Wa73).Wang, C.P.: Parametrization of Information System Application, IBM Research Report, RJ 1199, San José, April 11, 1973.Google Scholar
- (We73).Wedekind, H.: Systemanalyse, Carl Hanser Verlag, München 1973.Google Scholar
- (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