Materialized View Effect on Query Performance

Authors: Yusuf Ziya Ayık, Ferhat Kahveci

Abstract:

Currently, database management systems have various tools such as backup and maintenance, and also provide statistical information such as resource usage and security. In terms of query performance, this paper covers query optimization, views, indexed tables, pre-computation materialized view, query performance analysis in which query plan alternatives can be created and the least costly one selected to optimize a query. Indexes and views can be created for related table columns. The literature review of this study showed that, in the course of time, despite the growing capabilities of the database management system, only database administrators are aware of the need for dealing with archival and transactional data types differently. These data may be constantly changing data used in everyday life, and also may be from the completed questionnaire whose data input was completed. For both types of data, the database uses its capabilities; but as shown in the findings section, instead of repeating similar heavy calculations which are carrying out same results with the same query over a survey results, using materialized view results can be in a more simple way. In this study, this performance difference was observed quantitatively considering the cost of the query.

Keywords: Materialized view, pre-computation, query cost, query performance.

Digital Object Identifier (DOI): doi.org/10.5281/zenodo.1132307

References:

[1] MEB, Veritabanında Sorgular, Ankara, Turquia: Ministry of Education, 2012.
[2] IEEE, SWEBOK version 3.0, Piscataway, New Jersey, USA: IEEE Computer Society Products and Services, 2014.
[3] N. Kumari, "SQL server query optimization techniques," International Journal of Scientific and Research Publications, vol. 2, no. 6, pp. 1-4, 2012.
[4] A. Ö. Uysal, "Veritabanı sistemlerinde sorgu optimizasyonlarının veri analiz teknikleriyle geliştirilmesi," Yıldız Technical University, Graduate School of Natural and Applied Sciences, 2011.
[5] MEB, Veritabanı Yönetimsel Fonksiyonları, Ankara, Turquia: Ministry of Education, 2013.
[6] Y. Özkan, Veri madenciliği yöntemleri, Istanbul, Turquia: Papatya Yayıncılık Eğitim, 2013.
[7] J. Han, M. Kamber and J. Pei, Data Mining Concepts and Techniques 3rd Edition, Waltham, Massachusetts, USA: Elsevier Inc., 2012.
[8] C. J. Date, The Relational Database Dictionary: A Comprehensive Glossary of Relational Terms and Concepts, with Illustrative Examples, Sebastopol, California, USA: O'Reilly Media, 2006.
[9] L. Ergüder, "Materialized View," 25 06 2013. (Online). Available: http://www.injavawetrust.com/oracle-ders-40-views-05-materialized-view/. (Accessed 11 10 2016).
[10] DB-Engines, "DB-Engines Ranking of Relational DBMS," 2016. (Online). Available: http://db-engines.com/en/ranking/relational+dbms. (Accessed 14 10 2016).
[11] Oracle, "Optimizer Statistics Concepts," 2016. (Online). Available: https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL351. (Accessed 14 10 2016).
[12] M. Pilecki, "Optimizing SQL Server Query Performance," TechNet Magazine, 2007.
[13] MySQL, "Understanding the Query Execution Plan," 2016. (Online). Available: http://dev.mysql.com/doc/refman/5.7/en/execution-plan-information.html. (Accessed 14 10 2016).
[14] Oracle, "Query Optimizer Concepts," 2016. (Online). Available: https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm#TGSQL192. (Accessed 14 10 2016).
[15] K. Yagoub and P. Gongloor, "SQL Performance Analyzer," Oracle Corporation, Redwood Shores, California, USA, 2007.