Development of technology for automated creation of materialized views

Authors

DOI:

https://doi.org/10.15587/1729-4061.2015.50892

Keywords:

materialized view, query evaluation, query grouping, central query, clustering

Abstract

One of the urgent tasks in the DBMS optimization by the materialized views (MV) - the saved query evaluation results - is selecting queries, the result of which can be materialized. The problem is characterized by high computational complexity since it requires an analysis of the DBMS transaction log over a long period of time. The choice of the MV should be based on the cost of their future support, as well as the execution frequency and cost of queries, optimized by the MV data. It is effective to create the MP not for individual queries, but to form groups of similar queries and create views for them. When searching for the same type of queries, the SQL syntax should be taken into account. The paper considers the technology for automated creation of MV. Query evaluation was proposed, which takes into account the statistical and time indicators of query execution, as well as update frequency of the base tables. The query tokenization algorithm, which takes into account various syntactic structures of SQL was developed. The model of presenting queries in the form of numeric vectors was described. The pre-clustering stage was introduced to reduce the volume of the data processed. The improved clustering algorithm has allowed to form groups around the most resource-intensive and often executed queries. An algorithm for grouping and forming the central query, specifying the results of the preliminary clustering was developed. The described technology allows to automate the full creation cycle of MV. Experimental data showed that technology improvement has allowed to increase performance and reduce the resource intensity of the MV generation process, as well as enhance the quality of the developed MV compared to previous solutions.

Author Biographies

Екатерина Андреевна Новохатская, Odessa National Polytechnic University 1 Shevchenko ave., Odessa, Ukraine, 65044

Postgraduate student

Department of System Software

Алексей Борисович Кунгурцев, Odessa National Polytechnic University 1 Shevchenko ave., Odessa, Ukraine, 65044

PhD, professor

Department of System Software

References

  1. Aouiche, K., Jouve, P., Darmont, J. (2006). Clustering-Based Materialized View Selection in Data Warehouses, 81–95. doi: 10.1007/11827252_9
  2. Derakhshan, R., Stantic, B., Korn, O., Dehne, F. (2008). Parallel Simulated Annealing for Materialized View Selection in Data Warehousing Environments., 121–132. doi: 10.1007/978-3-540-69501-1_14
  3. Zhou, L., Geng, H., Xu, M. (2011). An Improved Algorithm for Materialized View Selection. Journal of Computers, 6 (1), 130–138. doi: 10.4304/jcp.6.1.130-138
  4. Ashdown, L., Colgan, M., Kyte, T. (2014). Optimizing Access Paths with SQL Access Advisor. Redwood City. Available at: https://docs.oracle.com/database/ 121/TGSQL/tgsql_sqlaccess.htm#TGSQL592
  5. Rao, J., Zhang, C., Megiddo, N., Lohman, G. (2002). Automating physical database design in a parallel database. Proceedings of the 2002 ACM SIGMOD international conference on Management of data - SIGMOD '02, 558–569. doi: 10.1145/564691.564757
  6. Gupta, H., Mumick, I. S. (2006). Incremental maintenance of aggregate and outerjoin expressions. Information Systems, 31 (6), 435–464. doi: 10.1016/j.is.2004.11.011
  7. Kungurtsev, A. B., Vinh, Quoc Nguyen Tran, Blazhko, A. A. (2004). Comparison of queries in relational databases for materialized views creation. Pratsi UNDIRT, 39, 35–38.
  8. Novokhatska, E. A. (2015). Calculation the materialization factor in query evaluation during the maintenance of materialized views. Vestnik KhNTU, 2 (53), 128–133.
  9. Novokhatska, E. A., Kungurtsev, A. B. (2014). Formation of tokens in query grouping in the method of incremental maintenance of materialized views. Vestnik ChGTU. Seriya "Tehnicheskie nauki", 1 (71), 193–199.
  10. Novokhatska, K. A. (2015). Application of clustering algorithm CLOPE to the query grouping problem in the field of materialized view maintenance. CIT. Journal of Computing and Information Technology, 23, 4.
  11. Yang, Y., Guan, X., You, J. (2002). CLOPE: A Fast and Effective Clustering Algorithm for Transactional Data. Proceedings of the eighth ACM SIGKDD international conference on Knowledge discovery and data mining, 682–687. doi: 10.1145/775047.775149
  12. Aho, A., Lam, M., Sethi, R., Ullman, J. (2002). Compilers: Principles, Techniques, and Tools, 1184.
  13. Parr, T. (2013). The Definitive ANTLR Reference, 328.

Downloads

Published

2015-10-23

How to Cite

Новохатская, Е. А., & Кунгурцев, А. Б. (2015). Development of technology for automated creation of materialized views. Eastern-European Journal of Enterprise Technologies, 5(4(77), 64–73. https://doi.org/10.15587/1729-4061.2015.50892

Issue

Vol. 5 No. 4(77) (2015): Mathematics and Cybernetics - applied aspects

Section

Mathematics and Cybernetics - applied aspects

License

Copyright (c) 2015 Екатерина Андреевна Новохатская, Алексей Борисович Кунгурцев

Creative Commons License

This work is licensed under a Creative Commons Attribution 4.0 International License.

The consolidation and conditions for the transfer of copyright (identification of authorship) is carried out in the License Agreement. In particular, the authors reserve the right to the authorship of their manuscript and transfer the first publication of this work to the journal under the terms of the Creative Commons CC BY license. At the same time, they have the right to conclude on their own additional agreements concerning the non-exclusive distribution of the work in the form in which it was published by this journal, but provided that the link to the first publication of the article in this journal is preserved.

A license agreement is a document in which the author warrants that he/she owns all copyright for the work (manuscript, article, etc.).
The authors, signing the License Agreement with TECHNOLOGY CENTER PC, have all rights to the further use of their work, provided that they link to our edition in which the work was published.
According to the terms of the License Agreement, the Publisher TECHNOLOGY CENTER PC does not take away your copyrights and receives permission from the authors to use and dissemination of the publication through the world's scientific resources (own electronic resources, scientometric databases, repositories, libraries, etc.).
In the absence of a signed License Agreement or in the absence of this agreement of identifiers allowing to identify the identity of the author, the editors have no right to work with the manuscript.
It is important to remember that there is another type of agreement between authors and publishers – when copyright is transferred from the authors to the publisher. In this case, the authors lose ownership of their work and may not use it in any way.