Abstract
A novel approach to solving Index Selection Problem (ISP) is presented. In contrast to other known ISP approaches, our method searches the space of possible query execution plans, instead of searching the space of index configurations. An evolutionary algorithm is used for searching. The solution is obtained indirectly as the set of indexes used by the best query execution plans. The method has important features over other known algorithms: (1) it converges to the optimal solution, unlike greedy heuristics, which for performance reasons tend to reduce the space of candidate solutions, possibly discarding optimal solutions; (2) though the search space is huge and grows exponentially with the size of the input workload, searching the space of the query plans allows to direct more computational power to the most costly plans, thus yielding very fast convergence to “good enough” solutions; and (3) the costly reoptimization of the workload is not needed for calculating the objective function, so several thousands of candidates can be checked in a second. The algorithm was tested for large synthetic and real-world SQL workloads to evaluate the performace and scalability.
The work has been granted by Polish Ministry of Education (grant No 3T11C 002 29).
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Preview
Unable to display preview. Download preview PDF.
References
Back, T., Hoffmeister, F., Schwefel, H.P.: A survey of evolution strategies. In: Proceedings of the Fourth International Conference on Genetic Algorithms, pp. 2–9. Morgan Kaufmann, San Francisco (1991)
Barcucci, E., Pinzani, R., Sprugnoli, R.: Optimal selection of secondary indexes. IEEE Trans. Softw. Eng. 16(1), 32–38 (1990), http://dx.doi.org/10.1109/32.44361
Bruno, N., Chaudhuri, S.: Automatic physical database tuning: a relaxation-based approach. In: SIGMOD 2005: Proceedings of the 2005 ACM SIGMOD international conference on Management of data, pp. 227–238. ACM Press, New York (2005), http://doi.acm.org/10.1145/1066157.1066184
Caprara, A., Fischetti, M., Maio, D.: Exact and approximate algorithms for the index selection problem in physical database design. IEEE Trans. on Knowl. and Data Eng. 7(6), 955–967 (1995), http://dx.doi.org/10.1109/69.476501
Caprara, A., González, J.J.S.: Separating lifted odd-hole inequalities to solve the index selection problem. Discrete Appl. Math. 92(2-3), 111–134 (1999), http://dx.doi.org/10.1016/S0166-218X9900050-5
Caprara, A., Salazar, J.: A branch-and-cut algorithm for a generalization of the uncapacitated facility location problem. TOP 4(1), 135–163 (1996), citeseer.ist.psu.edu/caprara95branchcut.html
Chan, A.Y.: Index selection in a self-adaptive relational data base management system. Tech. rep., Cambridge, MA, USA (1976)
Chaudhuri, S., Narasayya, V.R.: An efficient cost-driven index selection tool for Microsoft SQL Server. In: VLDB 1997: Proceedings of the 23rd International Conference on Very Large Data Bases, pp. 146–155. Morgan Kaufmann Publishers Inc., San Francisco (1997)
Choenni, S., Blanken, H.M., Chang, T.: Index selection in relational databases. In: International Conference on Computing and Information, pp. 491–496 (1993), citeseer.ist.psu.edu/choenni93index.html
Finkelstein, S., Schkolnick, M., Tiberio, P.: Physical database design for relational databases. ACM Trans. Database Syst. 13(1), 91–128 (1988), http://doi.acm.org/10.1145/42201.42205
Fotouhi, F., Galarce, C.E.: Genetic algorithms and the search for optimal database index selection. In: Proceedings of the The First Great Lakes Computer Science Conference on Computing in the 90’s, pp. 249–255. Springer, London (1991)
Ganek, A.G., Corbi, T.A.: The dawning of the autonomic computing era. IBM Syst. J. 42(1), 5–18 (2003)
Glover, F.: Tabu search – part i. ORSA Journal on Computing 1(3), 190–206 (1989)
Ip, M.Y.L., Saxton, L.V., Raghavan, V.V.: On the selection of an optimal set of indexes. IEEE Trans. Softw. Eng. 9(2), 135–143 (1983), http://dx.doi.org/10.1109/TSE.1983.236458
Kirkpatrick, S., Gelatt, C.D., Vecchi, M.P.: Optimization by simulated annealing. Science 220, 671–680 (1983)
Kołaczkowski, P.: Compressing very large database workloads for continuous online index selection. In: Bhowmick, S.S., Küng, J., Wagner, R. (eds.) DEXA 2008. LNCS, vol. 5181, pp. 791–799. Springer, Heidelberg (2008)
Kormilitsin, M., Chirkova, R., Fathi, Y., Stallman, M.: Plan-based view and index selection for query-performance improvement. Tech. Rep. 18, NC State University, Dept. of Computer Science (2008)
Kratica, J., Ljubić, I., Tošić, D.: A genetic algorithm for the index selection problem (2003), citeseer.ist.psu.edu/568873.html
Meyn, S.P., Tweedie, R.: Markov Chains and Stochastic Stability. Springer, Heidelberg (1993)
Papadomanolakis, S., Ailamaki, A.: An integer linear programming approach to database design. In: Workshop on Self-Managing Database Systems (2007)
Papadomanolakis, S., Dash, D., Ailamaki, A.: Efficient use of the query optimizer for automated physical design. In: VLDB 2007: Proceedings of the 33rd international conference on Very large data bases, pp. 1093–1104. VLDB Endowment (2007)
Parr, T.: ANTLRv3: Another tool for language recognition (2003–2008), http://www.antlr.org/
Russell, S.J., Norvig, P.: Artificial Intelligence: A Modern Approach. Pearson Education, London (2003), http://portal.acm.org/citation.cfm?id=773294
Sattler, K.U., Schallehn, E., Geist, I.: Autonomous query-driven index tuning. In: IDEAS 2004: Proceedings of the International Database Engineering and Applications Symposium (IDEAS 2004), pp. 439–448. IEEE Computer Society Press, Los Alamitos (2004), http://dx.doi.org/10.1109/IDEAS.2004.15
Schnaitter, K., Abiteboul, S., Milo, T., Polyzotis, N.: Colt: continuous on-line tuning. In: SIGMOD 2006: Proceedings of the 2006 ACM SIGMOD international conference on Management of data, pp. 793–795. ACM Press, New York (2006), http://doi.acm.org/10.1145/1142473.1142592
Skelley, A.: DB2 advisor: An optimizer smart enough to recommend its own indexes. In: ICDE 2000: Proceedings of the 16th International Conference on Data Engineering, p. 101. IEEE Computer Society, Washington (2000)
Talebi, Z.A., Chirkova, R., Fathi, Y., Stallmann, M.: Exact and inexact methods for selecting views and indexes for olap performance improvement. In: EDBT 2008: Proceedings of the 11th international conference on Extending database technology, pp. 311–322. ACM, New York (2008), http://doi.acm.org/10.1145/1353343.1353383
Transaction Performance Council: The TPC-H decision support benchmark (2001–2008), http://www.tpc.org/tpch/
Whang, K.-Y.: Index selection in relational databases. In: FODO, pp. 487–500 (1985)
Author information
Authors and Affiliations
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2009 Springer-Verlag Berlin Heidelberg
About this chapter
Cite this chapter
Kołaczkowski, P., Rybiński, H. (2009). Automatic Index Selection in RDBMS by Exploring Query Execution Plan Space. In: Ras, Z.W., Dardzinska, A. (eds) Advances in Data Management. Studies in Computational Intelligence, vol 223. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-642-02190-9_1
Download citation
DOI: https://doi.org/10.1007/978-3-642-02190-9_1
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-642-02189-3
Online ISBN: 978-3-642-02190-9
eBook Packages: EngineeringEngineering (R0)