This Article 
 Bibliographic References 
 Add to: 
Optimal Selection of Secondary Indexes
January 1990 (vol. 16 no. 1)
pp. 32-38

When planning a database, the problem of index selection is of particular interest. The authors examine a transaction model that includes queries, updates, insertions, and deletions, and they define a function that calculates the transaction's total cost when an index set is used. Their aim is to minimize the function cost in order to identify the optimal set. The algorithms proposed in other studies require an exponential time in the number of attributes in order to solve the problem. The authors propose a heuristic algorithm based on some properties of the cost function that produces an almost optimal set in polynomial time. In many cases, the cost function properties make it possible to prove that the solution obtained is the optimal one.

[1] H. D. Anderson and P. B. Berra, "Minimum cost selection of secondary indexes for formatted files,"ACM Trans. Database Syst., vol. 2, no. 1, pp. 68-90, Mar. 1977.
[2] D. Comer, "The difficulty of optimum index selection,"ACM Trans. Database Syst., vol. 3, no. 4, pp. 440-445, 1978.
[3] M. Stonebraker, "The choice of partial inversions and combined indices."Int. J. Comput. Inform. Sci., vol. 3, pp. 167-188, 1974.
[4] M. Schkolnick. "The optimal selection of secondary indices for files,"Inform. Syst., vol. 1, pp. 141-146, 1975.
[5] M. Hammer and A. Chan, "Index selection in a self-adaptive database management system," inProc. 1976 ACM-SIGMOD Conf.(Washington, DC).
[6] J. G. Kollias, P. M. Stocker, and P. A. Dearnley, "Improving the performance of an intelligent data management system,"Comput. J., vol. 20, pp. 302-307, 1977.
[7] J. G. Kollias, "File organizations and their reorganization,"Inform. syst., vol. 4, pp. 49-54. 1979.
[8] J. G. Kollias, "A heuristic approach for determining the optimal degree of tile inversion,"Inform. Syst., vol. 4, pp. 307-318, 1979.
[9] M. Hatzopoulos and J. G. Kollias, "On the optimal selection of multilist database structures,"IEEE Trans. Software Eng., vol. SE-10, pp. 681-687. 1984.
[10] M. Y. L. Ip, L. V. Saxton, and V. V. Raghavan, "On the selection of an optimal set of indexes,"IEEE Trans. Software Eng., vol. SE- 9, pp. 135-143, 1983.
[11] K. Y. Whang, "Index selection in relational databases," inFoundations of Data Organization. S. Ghosh. Y. Kambayashi, and K. Tanaka, Eds. New York: Plenum, 1987. pp. 487-500.
[12] A. Cardenas, "Analysis and performance of inverted data-base structures,"Commun. ACM, vol. 18, no. 5, pp. 253-263, 1975.
[13] S. B. Yao, "Approximating block accesses in database organizations,"Commun. ACM, vol. 20, pp. 260-261, Apr. 1977.
[14] E. Barcucci. E. Grazzini. and R. Pinzani. "Index selection in a distributed data base," inDistributed Data Sharing Systems, F. A. Schreiber and W. Litwin. Eds. Amsterdam, The Netherlands: Elsevier Science, 1985, pp. 179-187.
[15] E. Barcucci, A. Chiuderi, R. Pinzani, and M. C. Verri, "Index selection in relational databases," inProc. MFDBS 89 (LNCS 364). 1989. pp. 24-36.

Index Terms:
optimal selection; secondary indexes; database; transaction model; queries; updates; insertions; deletions; heuristic algorithm; polynomial time; cost function properties; database management systems; heuristic programming; indexing; transaction processing.
E. Barcucci, O. Pinzani, "Optimal Selection of Secondary Indexes," IEEE Transactions on Software Engineering, vol. 16, no. 1, pp. 32-38, Jan. 1990, doi:10.1109/32.44361
Usage of this product signifies your acceptance of the Terms of Use.