A solution for synchronous incremental maintenance of materialized views based on SQL recursive query

Authors

DOI:

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

Keywords:

materialized view, SQL recursive query, CTE, incremental update, source code generating

Abstract

Materialized views are excessively stored query execution results in the database. They can be used to partially or completely answer queries which will be further appeared instead of re-executing query from the scratch. There is a large number of published works that address the maintenance, especially incremental update, of materialized views and query rewriting for using those ones. Some of them support materialized views based on recursive query in datalog language. Although most of datalog queries can be transferred into SQL queries and vise versa but it is not the case for recursive queries. Recursive queries in the data log try to find all possible transitive closures. Recursive queries in SQL (Common Table Expression – CTE) return direct links but not transitive closures. In this paper, we propose efficient methods for incremental update of materialized views based on CTE; and then propose an algorithm for generating source codes in C language for any input SQL recursive queries. The synthesized source codes implement our proposed incremental update algorithms according to inserted/deleted/updated record set in the base tables. This paper focuses mainly on the recursive queries whose execution results are directed tree-structured data. The two cases of tree node are considered. In the first case, a child node has only one parent node and in the second case, a child node can have many parent nodes. Those two cases represent the two types of relationships between entities in real world, that are one–to–many and many–to–many, respectively. For the one–to–many relationships, the relationship data is accompanied with the records describing the child using some fields. Those fields are set as null in deleting a concrete relationship. For the many–to–many relationships, it is stored in a separate table and the concrete relationships are removed by deleting describing records from that table. Considering of enforcing referential integrity may help to reduce the searching space and therefore, help to improve the performance. However, the set of tree nodes or tree edges can be manipulated. All those combinations lead to different algorithms. The experimental results are provided and discussed to confirm the effectiveness of our proposed methods

Author Biographies

Nguyen Tran Quoc Vinh, The University of Da Nang – University of Science and Education Ton Duc Thang, 459, Lien Chieu Dist., Da Nang city, Vietnam, 550000

PhD

Faculty of Information Technology

Dang Thanh Hao, The University of Da Nang – University of Science and Education Ton Duc Thang, 459, Lien Chieu Dist., Da Nang city, Vietnam, 550000

Student

Faculty of Information Technology

Pham Duong Thu Hang, The University of Da Nang – University of Science and Education Ton Duc Thang, 459, Lien Chieu Dist., Da Nang city, Vietnam, 550000

PhD Student

Faculty of Information Technology

Abeer Alsadoon, Charles Sturt University Level 1, Oxford str., 63, Darlinghurst NSW 2010, Australia

PhD, Associate Professor

School of Computing and Mathematics, Sydney Campus

PW Chandana Prasad, Charles Sturt University Level 1, 63 Oxford str., Darlinghurst NSW 2010, Australia

PhD, Associate Professor

School of Computing and Mathematics, Sydney Campus

Nguyen Viet Anh, Vietnam Academy of Science and Technology Hoang Quoc Viet, 108, Cau Giay Dist., Hanoi City, Vietnam, 100000

PhD

Department of Data Science and Application

Institute of Information Technology

References

  1. Zaharioudakis, M., Cochrane, R., Lapis, G., Pirahesh, H., Urata, M. (2000). Answering complex SQL queries using automatic summary tables. Proceedings of the 2000 ACM SIGMOD International Conference on Management of Data - SIGMOD’00. doi: https://doi.org/10.1145/342009.335390
  2. Goldstein, J., Larson, P.-Å. (2001). Optimizing queries using materialized views. Proceedings of the 2001 ACM SIGMOD International Conference on Management of Data - SIGMOD’01. doi: https://doi.org/10.1145/375663.375706
  3. Halevy, A. Y. (2001). Answering queries using views: A survey. The VLDB Journal, 10 (4), 270–294. doi: https://doi.org/10.1007/s007780100054
  4. Park, C.-S., Kim, M. H., Lee, Y.-J. (2002). Finding an efficient rewriting of OLAP queries using materialized views in data warehouses. Decision Support Systems, 32 (4), 379–399. doi: https://doi.org/10.1016/s0167-9236(01)00123-3
  5. Chirkova, R., Li, C., Li, J. (2005). Answering queries using materialized views with minimum size. The VLDB Journal, 15 (3), 191–210. doi: https://doi.org/10.1007/s00778-005-0162-8
  6. Ileana, I., Cautis, B., Deutsch, A., Katsis, Y. (2014). Complete yet practical search for minimal query reformulations under constraints. Proceedings of the 2014 ACM SIGMOD International Conference on Management of Data - SIGMOD’14. doi: https://doi.org/10.1145/2588555.2593683
  7. Afrati, F., Chandrachud, M., Chirkova, R., Mitra, P. (2009). Approximate Rewriting of Queries Using Views. Lecture Notes in Computer Science, 164–178. doi: https://doi.org/10.1007/978-3-642-03973-7_13
  8. Larson, P.-Å., Zhou, J. (2006). View matching for outer-join views. The VLDB Journal, 16 (1), 29–53. doi: https://doi.org/10.1007/s00778-006-0027-9
  9. Cohen, S., Nutt, W., Sagiv, Y. (2006). Rewriting queries with arbitrary aggregation functions using views. ACM Transactions on Database Systems, 31 (2), 672–715. doi: https://doi.org/10.1145/1138394.1138400
  10. Chen, S., Rundensteiner, E. A. (2005). GPIVOT: Efficient Incremental Maintenance of Complex ROLAP Views. 21st International Conference on Data Engineering (ICDE’05). doi: https://doi.org/10.1109/icde.2005.71
  11. Lee, K. Y., Kim, M. H. (2005). Optimizing the incremental maintenance of multiple join views. Proceedings of the 8th ACM International Workshop on Data Warehousing and OLAP - DOLAP. doi: https://doi.org/10.1145/1097002.1097021
  12. Gupta, H., Mumick, I. S. (2006). Incremental maintenance of aggregate and outerjoin expressions. Information Systems, 31 (6), 435–464. doi: https://doi.org/10.1016/j.is.2004.11.011
  13. Larson, P.-Å. (2018). Maintenance of Materialized Views with Outer-Joins. Encyclopedia of Database Systems, 2165–2170. doi: https://doi.org/10.1007/978-1-4614-8265-9_841
  14. Nica, A. (2012). Incremental maintenance of materialized views with outerjoins. Information Systems, 37 (5), 430–442. doi: https://doi.org/10.1016/j.is.2011.06.001
  15. Quoc Vinh, N. T. (2016). Synchronous incremental update of materialized views for PostgreSQL. Programming and Computer Software, 42 (5), 307–315. doi: https://doi.org/10.1134/s0361768816050066
  16. Gupta, H., Mumick, I. S. (2005). Selection of views to materialize in a data warehouse. IEEE Transactions on Knowledge and Data Engineering, 17 (1), 24–43. doi: https://doi.org/10.1109/tkde.2005.16
  17. Kungurtsev, O. B., Vozovikov, Y. N., Vinh, N. T. Q. (2012). Determination Of The Parameters of Periodic On / Off Materialized View in the Information System. Eastern-European Journal of Enterprise Technologies, 4 (2 (58)), 42–45. Available at: http://journals.uran.ua/eejet/article/view/4217/3980
  18. Novokhatska, K., Kungurtsev, O. (2016). Developing methodology of selection of materialized views in relational databases. Eastern-European Journal of Enterprise Technologies, 3 (2 (81)), 9–14. doi: https://doi.org/10.15587/1729-4061.2016.68737
  19. Novokhatska, K., Kungurtsev, O. (2016). Application of Clustering Algorithm CLOPE to the Query Grouping Problem in the Field of Materialized View Maintenance. Journal of Computing and Information Technology, 24 (1), 79–89. doi: https://doi.org/10.20532/cit.2016.1002694
  20. Sebaa, A., Tari, A. (2019). Materialized View Maintenance: Issues, Classification, and Open Challenges. International Journal of Cooperative Information Systems, 28 (01), 1930001. doi: https://doi.org/10.1142/s0218843019300018
  21. Zhou, J., Larson, P.-A., Elmongui, H. G. (2007). Lazy maintenance of materialized views. Proceedings of the 33rd international conference on Very large data bases. Vienna, 231–242. Available at: http://www.vldb.org/conf/2007/papers/research/p231-zhou.pdf
  22. Chak, D. Materialized views that work. Available at: https://www.pgcon.org/2008/schedule/attachments/64_BSDCan2008-MaterializedViews-paper.pdf
  23. Almazyad, A., Siddiquim, M. K. (2010). Incremental View Maintenance: An Algorithmic Approach. International Journal of Electrical & Computer Sciences IJECS-IJENS, 10 (03), 16–21.
  24. Koch, C., Lupei, D., Tannen, V. (2016). Incremental View Maintenance For Collection Programming. Proceedings of the 35th ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems - PODS’16. doi: https://doi.org/10.1145/2902251.2902286
  25. Duan, H., Hu, H., Qian, W., Ma, H., Wang, X., Zhou, A. (2018). Incremental Materialized View Maintenance on Distributed Log-Structured Merge-Tree. Lecture Notes in Computer Science, 682–700. doi: https://doi.org/10.1007/978-3-319-91458-9_42
  26. Jain, H., Gosain, A. (2012). A comprehensive study of view maintenance approaches in data warehousing evolution. ACM SIGSOFT Software Engineering Notes, 37 (5), 1. doi: https://doi.org/10.1145/2347696.2347705
  27. Yang, Y., Golab, L., Tamer Ozsu, M. (2017). ViewDF: Declarative incremental view maintenance for streaming data. Information Systems, 71, 55–67. doi: https://doi.org/10.1016/j.is.2017.07.002
  28. Dietrich, S. W. (2017). Maintenance of Recursive Views. Encyclopedia of Database Systems, 1–7. doi: https://doi.org/10.1007/978-1-4899-7993-3_842-2

Downloads

Published

2019-10-09

How to Cite

Vinh, N. T. Q., Hao, D. T., Hang, P. D. T., Alsadoon, A., Prasad, P. C., & Anh, N. V. (2019). A solution for synchronous incremental maintenance of materialized views based on SQL recursive query. Eastern-European Journal of Enterprise Technologies, 5(2 (101), 6–17. https://doi.org/10.15587/1729-4061.2019.180226

Issue

Vol. 5 No. 2 (101) (2019): Information technology. Industry control systems

Section

Information technology

License

Copyright (c) 2019 Nguyen Tran Quoc Vinh, Dang Thanh Hao, Pham Duong Thu Hang, Abeer Alsadoon, PW Chandana Prasad, Nguyen Viet Anh

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.