Indexing for tables of grouped SQL queries with size criterion

Radosław Boroński

Abstract


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, including the size criterion. Examples illustrate that the use of a group approach can reduce queries block execution time of 30% compared to classical methods.

Keywords


index; indexing; optimization; SQL; RDBMS

Full Text:

PDF (Polski)

References


Agrawal S., Chaudhuri S., Kollar L., Marathe A., Narasayya V., Syamala M.: Database Tuning Advisor for Microsoft SQL Server 2005. Proceedings of the 30th International Conference on Very Large Databases, 2004.

Back T.: Evolutionary algorithms in theory and practice: evolution strategies, evolutionary programming, genetic algorithms. Oxford University Press Oxford, UK 1996.

Boroński R.: Automatyzacja i optymalizacja procesu doboru indeksów dla dowolnego wycinka czasowego w relacyjnej bazie danych (na przykładzie Oracle 11g). Studia Informatica, Vol. 33, No. 2A (105), Gliwice 2012, s. 229.

Boroński R.: Wpływ ustawień parametru wieloblokowego sekwencyjnego czytania danych na czas wykonania zapytania SQL w bazie danych Oracle. Materiały VI Krajowej Konferencji Naukowej Infobazy 2011, Gdańsk 2011, s. 135.

Chaudhuri S., Narasayya V.: An efficient Cost-Driven Index Selection Tool for MS SQL Server. Very Large Data Bases Endowment Inc, 1997.

Dageville B., Das D., Dias K., Yagoub K., Zait M. Ziauddin M.: Automatic SQL Tuning in Oracle 10g. Proceedings of the 30th International Conference on Very Large Databases, 2004.

Finkelstein S., Schkolnick M., Tiberio P.: Physical database design for relational databases. ACM Trans. Database Syst, 13(1), 1988, s. 91÷128.

Frank M, Omiecinski M.: Adaptive and Automated Index Selection in RDBMS. Proceedings of EDBT, 1992.

Gupta H., Harinarayan Y., Rajaraman A., Ullman J.D.: Index Selection for OLAP. Proceedings of the Internatoinal Conference on Data Engineering, Birmingham 1997, s. 208÷219.

Kołaczkowski P., Rybiński H.: Automatic Index Selection in RDBMS by Exploring Query Execution Plan Space. Studies in Computational Intelligence, Vol. 223, Springer, 2009, s. 3÷24

Kratica J., Ljubic I., Tosic D.: A Genetic Algorithm for the Index Selection Problem. EvoWorkshops’03, Proceedings of the 2003 international conference on Applications of evolutionary computing, 2003.

Maggie Y., Ip L., Saxton L. V., Raghavan V.: On the Selection of an Optimal Set of Indexes. IEEE Transactions on Software Engineering, 9(2), 1983, s. 135÷143.

Schkolnick M.: The Optimal Selection of Indices for Files. Information Systems, Vol. 1, 1975.

Schnaitter K.: On-line Index Selection for Physical Database Tuning. ProQuest, UMI Dissertation Publishing, 2011.

Valentin G., Zuliani M., Zilio D., Lohman G.: DB2 Advisor: An Optimizer Smart Enough to Recommend Its Own Indexes. Proceeding ICDE ‘00, Proceedings of the 16th International Conference on Data Engineering, s. 101.

Wedekind H.: On the selection of access paths in a data base system, [in:] Klimbie J. W., Koffeman K. L. (eds.): Data Base Management. North-Holland, Amsterdam 1974, s. 385÷397.




DOI: http://dx.doi.org/10.21936/si2013_v34.n2A.22