Tables indexing for various densities of SQL queries groups (example of Oracle 11g)

Radosław Boroński, Grzegorz Bocewicz

Abstract


Commonly used commercial tools are based on a methodology that enables tables indexing for individual SQL queries. The article presents an original method, based on a genetic algorithm, for indexing tables for groups of queries in a relational database. Conducted experiments have shown that the use of indices for a group of queries can reduce the group execution time by 15% as well as can reduce the memory needs by 68-90%.

Keywords


index; indexing; database; query; optimization; SQL; RDBMS

Full Text:

PDF (Polski)

References


Barcucci E., Pinzani R., Sprugnoli R.: Optimal selection of secondary indexes. IEEE Transactions on Software Engineering, Vol. 16(1), 1990, s. 32÷38

Boroński R., Bocewicz G., Wójcik R.: Grouped queries indexing for relational database. eKNOW 2013: The Fifth International Conference on Information, Process, and Knowledge Management, Iaria Journals, 2013, s. 123÷129.

Boroński R., Bocewicz G.: Indexes driven mechanism for grouped SQL queries. Pomiary, Automatyka, Robotyka, nr 2, 2013, s. 135÷142.

Boroński R.: Problem doboru indeksów dla powiązanych zapytań SQL. [w:] Matuszek J., Gregor M., Micieta B. (red.): Metody i techniki zarządzania w inżynierii produkcji, rocznik VI , Wydawnictwo Naukowe Akademii Techniczno-Humanistycznej w BielskuBiałej, Bielsko-Biała 2013, s. 27÷42.

Boroński R., Bocewicz G.: Multi-criteria index selection for grouped SQL queries. Communications in Computer and Information Science, Vol. 370, Springer, 2013, s. 573÷581.

Boroński R., Bocewicz G.: Relational database index selection algorithm (example of Oracle 11g). Communications in Computer and Information Science, Springer, 2014 (in print).

Bruno N., Chaudhuri S.: An online approach to physical design tuning. International Conference on Data Engineering, 2007, s. 826÷835.

Bruno N., Chaudhuri S.: Automatic physical database tuning: a relaxation-based approach. ACM SIGMOD International Conference on Management of Data, 2005, s. 227÷238.

Caprara A., Fischetti M., Maio D.: Exact and approximate algorithms for the index selection problem in physical database design. IEEE Transactions on Knowledge and Data Engineering, Vol. 7(6), 1995, s. 955÷967.

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

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.

Sattler K.-U., Schallehn E., Geist I.: Autonomous query-driven index tuning. International Database Engineering and Applications Symposium, 2004, s. 439÷448.

Schnaitter K., Abiteboul S., Milo T., Polyzotis N.: On-line index selection for shifting workloads. International Workshop on Self-Managing Database Systems, 2007, s. 459÷468.




DOI: http://dx.doi.org/10.21936/si2014_v35.n2.675