Indexes selection for blocks of related SQL queries

• Autorzy: Boroński R.
• Języki publikacji: EN

Abstrakty

EN

This paper discusses the problem of minimizing the response time for a given database workload by a proper choice of indexes. The main objective of our contribution is to illustrate the database queries as a group and search for good indexes for the group instead of an individual query. We present queries block relation conditions for applying the concept of grouped queries index selection. In three experimental tests we provide measurements on the quality of the recommended approach.

Słowa kluczowe

EN

indexes selection for blocks of related SQL queries

• Wydawca: Polskie Towarzystwo Promocji Wiedzy ; Lublin University of Technology
• Czasopismo: Applied Computer Science
• Rocznik: 2012
• Tom: Vol. 8, no 2
• Strony: 3--21
• Opis fizyczny: Bibliogr. 20 poz., fig., tab.

Twórcy

autor

Boroński R.

• Koszalin University of Technology, Śniadeckich 2, 75-453 Koszalin, Poland

Bibliografia

• [1] S. Agrawal, S. Chaudhuri, L. Kollar, A. Marathe, V. Narasayya, and M. Syamala. Database Tuning Advisor for Microsoft SQL Server 2005. In Proceedings of the 30th International Conference on Very Large Databases, 2004.
• [2] T. Back, “Evolutionary algorithms in theory and practice: evolution strategies, evolutionary programming, genetic algorithms”, Oxford University Press Oxford, UK, 1996.
• [3] N. Bruno, S. Chaudhuri, “Automatic physical database tuning: a relaxation-based approach”, SIGMOD '05 Proceedings of the 2005 ACM SIGMOD international conference on Management of data, ACM New York, NY, USA, 2005, pp. 227-238.
• [4] S. Chaudhuri, V. Narasayya, “An efficient Cost-Driven Index Selection Tool for MS SQL Server”, Very Large Data Bases Endowment Inc, 1997.
• [5] D. Comers, "The Ubiquitous B-Tree", Computing Surveys 11 (2), doi:10.1145/356770.356776, pp. 123-137.
• [6] B. Dageville, D. Das, K. Dias, K. Yagoub, M. Zait, and M. Ziauddin. “Automatic SQL Tuning in Oracle 10g”. In Proceedings of the 30th International Conference on Very Large Databases, 2004.
• [7] C. Dawes, B. Bryla, J. Johnson, M Weishan, “OCA Oracle 10g Administration I”, Sybex, 2005, pp. 173.
• [8] S. Finkelstein, M. Schkolnick, P. Tiberio, “Physical database design for relational databases”, ACM Trans. Database Syst. 13(1), (1988), pp. 91-128.
• [9] M. Frank, M. Omiecinski, “Adaptive and Automated Index Selection in RDBMS”, Proceedings of EDBT, 1992.
• [10] H. Gupta, V. Harinarayan, A. Rajaraman, and J. D. Ullman, ”Index Selection for OLAP”, In Proceedings of the Internatoinal Conference on Data Engineering, Birmingham, U.K., April 1997, p. 208-219.
• [11] D. Knuth, “The Art of Computer Programming”, vol. 3, Sorting and Searching. Addison- Wesley, Reading, Mass., 1973.
• [12] D. Knuth, “Sorting and Searching, The Art of Computer Programming”, Volume 3 (Second ed.), Addison-Wesley.
• [13] P. Kołaczkowski, H. Rybiński, “Automatic Index Selection in RDBMS by Exploring Query Execution Plan Space”, Studies in Computational Intelligence, vol. 223, Springer, 2009, pp. 3-24
• [14] J. Kratica, I. Ljubic, D. Tosic, “A Genetic Algorithm for the Index Selection Problem”, EvoWorkshops'03 Proceedings of the 2003 international conference on Applications of evolutionary computing, 2003.
• [15] P.L. Lehman, “Efficient locking for concurrent operations on B-trees”, ACM Transactions on Database Systems (TODS), Volume 6 Issue 4, Dec. 1981, pp. 650-670.
• [16] Y. Maggie, L. Ip, L. V. Saxton, and Vijay V. Raghavan, ”On the Selection of an Optimal Set of Indexes”, IEEE Transactions on Software Engineering, 9(2), March 1983, p. 135-143.
• [17] M. Schkolnick, “The Optimal Selection of Indices for Files”, Information Systems, V.1, 1975.
• [18] K. Schnaitter, “On-line Index Selection for Physical Database Tuning”, ProQuest, UMI Dissertation Publishing, 2011.
• [19] S. Tatham, “Counted B-Trees”, http://www.chiark.greenend.org.uk/~sgtatham/algorithms/cbtree.html.
• [20] H. Wedekind, “On the selection of access paths in a data base system. In Data Base Management”, J.W. Klimbie and K.L. Koffeman, Eds. North-Holland, Amsterdam, 1974, pp. 385-397.