Abstract
When planning a database, the problem of index selection is of particular interest. In this paper, we examine a transaction model which includes queries, updates, insertions and deletions, and we define a function that calculates the transactions total cost when an index set is used. Our aim was to minimize the function cost in order to identify the optimal set. The algorithms proposed in previous studies require an exponential time in the number of file attributes in order to solve the problem. We propose a heuristic algorithm based on some properties of the cost function and this algorithm produces an almost optimal set in polinomial time.
This is a preview of subscription content, log in via an institution to check access.
Preview
Unable to display preview. Download preview PDF.
References
H. D. Anderson and P. B. Berra, “Minimum cost selection of secondary indexes for formatted files”, ACM TODS, 2, 68–90, 1977.
E. Barcucci, E. Grazzini and R. Pinzani, “Index selection in a distributed data base”, in Distributed Data Sharing Systems — F. A. Schreiber and W. Litwin (Editors), Elsevier Science Publishers B. V., Amsterdam, 1985, 179–187.
A. F. Cardenas, “Analysis, and performance of inverted data base structures”, Comm. ACM, 18, 253–263, 1975.
D. Comer, “The difficulty of optimum index selection”, ACM TODS, 3, 440–445, 1978.
M. Hammer and A. Chan, “Index selection in a self-adaptive data-base management system”, in Proc. 1976 ACM-SIGMOD Conf., Washington, DC, 1–8.
M. Hatzopoulos and J. G. Kollias, “On the optimal selection of multilist database structures”, IEEE Trans. Software Eng., 10, 681–687, 1984.
M. Y. L. Ip, L. V. Saxton and V. V. Raghavan, “On the selection of an optimal set of indexes”, IEEE Trans. Software Eng., 9, 135–143, 1983.
J. G. Kollias, P. M. Stocker and P. A. Dearnley, “Improving the performance of an intelligent data management system”, Comp. J., 20, 302–307, 1977.
J. G. Kollias, “File organizations and their reorganization”, Inform. Systems, 4, 49–54, 1979.
J. G. Kollias, “A heuristic approach for determining the optimal degree of file inversion,” Inform. Systems, 4, 307–318, 1979.
M. Schkolnick, “The optimal selection of secondary indices for files”, Inform. Systems, 1, 141–146, 1975.
L. D. Shapiro, “Join processing in database systems with large main memories”, ACM TODS, 11, 239–264, 1986.
M. Stonebraker, “The choice of partial inversions and combined indices”, Int. J. Comput. Inform. Sci., 3, 167–188, 1974.
K. Y. Whang, “Index Selection in Relational Databases”, in Foundations of Data Organization — S. Ghosh, Y. Kambayashi and K. Tanaka (Editors), Plenum Publishing Corporation, New York, 1987, 487–500.
K. Y. Whang, G. Wiederhold, D. Sagalowicz, “Separability — An approach to physical database design”, in Proc. Int. Conf. on Very Large Data Bases, Cannes, 1981, 320–332.
S. B. Yao, “Approximating block accesses in database organizations”, Comm. ACM, 20, 260–261, 1977.
Author information
Authors and Affiliations
Editor information
Rights and permissions
Copyright information
© 1989 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Barcucci, E., Chiuderi, A., Pinzani, R., Verri, M.C. (1989). Index selection in relational databases. In: Demetrovics, J., Thalheim, B. (eds) MFDBS 89. MFDBS 1989. Lecture Notes in Computer Science, vol 364. Springer, Berlin, Heidelberg. https://doi.org/10.1007/3-540-51251-9_3
Download citation
DOI: https://doi.org/10.1007/3-540-51251-9_3
Published:
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-540-51251-6
Online ISBN: 978-3-540-46182-1
eBook Packages: Springer Book Archive