A new solution for asynchronous incremental maintenance of materialized views

Authors

  • 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, Viet Nam https://orcid.org/0000-0003-2281-0429
  • Le Van Khanh The University of Da Nang – University of Science and Education Ton Duc Thang, 459, Lien Chieu Dist., Da Nang city, Vietnam, 550000, Viet Nam https://orcid.org/0000-0002-8093-9215
  • Tran Trong Nhan The University of Da Nang – University of Science and Education Ton Duc Thang, 459, Lien Chieu Dist., Da Nang city, Vietnam, 550000, Viet Nam https://orcid.org/0000-0001-6516-6579
  • Tran Dang Hung Hanoi National University of Education Xuan Thuy str., 136, Cau Giay District, Hanoi, Vietnam, 110000, Viet Nam https://orcid.org/0000-0001-7691-6486
  • PW Chandana Prasad School of Computing and Mathematics, Sydney Campus Charles Sturt University Level 1, Oxford str., 63, Darlinghurst NSW 2010, Australia, Australia https://orcid.org/0000-0002-3007-687X
  • Abeer Alsadoon School of Computing and Mathematics, Sydney Campus Charles Sturt University Level 1, Oxford str., 63, Darlinghurst NSW 2010, Australia, Australia https://orcid.org/0000-0002-2309-3540
  • 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, Viet Nam https://orcid.org/0000-0002-0813-827X

DOI:

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

Keywords:

materialized view, pre-update state, asynchronous incremental maintenance, source code synthesis, PostgreSQL

Abstract

Materialized views can help improve query execution speed dramatically using full or part of stored precalculated query results. Incremental maintenance takes materialized views up-to-date adjusting them accordingly to the changes in related base tables. It is often more effective than a full refresh replacing materialized view tables by the new query execution result. Asynchronous maintenance, which brings the materialized views to the actual state, is not a constituent of the transaction making changes in the base tables. Most of the published works are dedicated to synchronous incremental updating of views, algorithms for which require accessing the pre-update state of base tables and cannot be applied directly to asynchronous updates which are performed on the post-update state. A few works are devoted to asynchronous maintenance of views, either restrict changes in only one of base tables or assume there is only one base table, which is impractical, either is wrong, either provides too high level and complex incremental update algorithms or can be implemented only if the database management system supports data versioning at the table and row levels. In this paper, we propose a solution for the asynchronous incremental update of views which can be implemented with any database management systems. We collect changes in base tables, access the pre-update state of base tables exploiting the condensing process and apply the pre-update incremental maintenance algorithms for asynchronous maintenance on the post-update state of base tables considering the specifics of asynchronous maintenance. It is applicable for SPJ queries with inner joins, queries with inner joins and aggregations. We also build a prototype and provide experiments on automatic generating source codes in C to collect changes in base tables and to perform the asynchronous incremental update of materialized views in PostgreSQL

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

Le Van Khanh, The University of Da Nang – University of Science and Education Ton Duc Thang, 459, Lien Chieu Dist., Da Nang city, Vietnam, 550000

Master of Computer Science

Faculty of Information Technology

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

Master of Science in Computer Science

Faculty of Information Technology

Tran Dang Hung, Hanoi National University of Education Xuan Thuy str., 136, Cau Giay District, Hanoi, Vietnam, 110000

PhD, Associate Professor

Faculty of Information Technology

PW Chandana Prasad, School of Computing and Mathematics, Sydney Campus Charles Sturt University Level 1, Oxford str., 63, Darlinghurst NSW 2010, Australia

PhD, Associate Professor

Abeer Alsadoon, School of Computing and Mathematics, Sydney Campus Charles Sturt University Level 1, Oxford str., 63, Darlinghurst NSW 2010, Australia

PhD, Associate Professor

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

References

  1. 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
  2. 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. doi: https://doi.org/10.15587/1729-4061.2019.180226
  3. 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
  4. 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
  5. 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
  6. O’Gorman, K., Agrawal, D., El Abbadi, A. (2000). On the Importance of Tuning in Incremental View Maintenance: An Experience Case Study. Lecture Notes in Computer Science, 77–82. doi: https://doi.org/10.1007/3-540-44466-1_8
  7. 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
  8. 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’ (VLDB Endowment, 2007, edn.), 231–242.
  9. Colby, L. S., Griffin, T., Libkin, L., Mumick, I. S., Trickey, H. (1996). Algorithms for deferred view maintenance. ACM SIGMOD Record, 25 (2), 469–480. doi: https://doi.org/10.1145/235968.233364
  10. Yan, W. P., Larson, P.-A. (1995). Eager Aggregation and Lazy Aggregation. Proceedings of the 21th International Conference on Very Large Data Bases, 345–357.
  11. Nguyen, T. Q. V., Tran, T. N. (2014). Automatic generating C-languague-triggers modul for synchronized incremental updating materialized views in PostgreSQL. Proc. National Conference on Fundamental and Applied IT Research (FAIR).
  12. Agrawal, P., Silberstein, A., Cooper, B. F., Srivastava, U., Ramakrishnan, R. (2009). Asynchronous view maintenance for VLSD databases. Proceedings of the 35th SIGMOD International Conference on Management of Data - SIGMOD ’09. doi: https://doi.org/10.1145/1559845.1559866
  13. Mikami, K., Morishita, S., Onizuka, M. (2010). Lazy View Maintenance for Social Networking Applications. Lecture Notes in Computer Science, 347–358. doi: https://doi.org/10.1007/978-3-642-12098-5_29
  14. Chun, S., Jung, J., Lee, K.-H. (2019). Proactive Policy for Efficiently Updating Join Views on Continuous Queries Over Data Streams and Linked Data. IEEE Access, 7, 86226–86241. doi: https://doi.org/10.1109/access.2019.2923414
  15. Phani, A., Tekur, C., Sai Krishna, R. K. N. (2019). Commit Time Materialized View Maintenance for Bulk Load Operations in Teradata. 2019 IEEE International Conference on Electrical, Computer and Communication Technologies (ICECCT). doi: https://doi.org/10.1109/icecct.2019.8869100
  16. Almazyad, A. S., Siddiqui, M. K., Ahmad, Y., Khan, Z. I. (2009). An Incremental View Maintenance Approach Using Version Store in Warehousing Environment. 2009 Second International Workshop on Computer Science and Engineering. doi: https://doi.org/10.1109/wcse.2009.624
  17. Zhuge, Y., García-Molina, H., Hammer, J., Widom, J. (1995). View maintenance in a warehousing environment. ACM SIGMOD Record, 24 (2), 316–327. doi: https://doi.org/10.1145/568271.223848

Downloads

Published

2020-02-29

How to Cite

Vinh, N. T. Q., Khanh, L. V., Nhan, T. T., Hung, T. D., Prasad, P. C., Alsadoon, A., & Hang, P. D. T. (2020). A new solution for asynchronous incremental maintenance of materialized views. Eastern-European Journal of Enterprise Technologies, 1(2 (103), 6–13. https://doi.org/10.15587/1729-4061.2020.193715