A solution for synchronous incremental maintenance of materialized views based on SQL recursive query
Keywords:materialized view, SQL recursive query, CTE, incremental update, source code generating
AbstractMaterialized 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
- 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
- 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
- Halevy, A. Y. (2001). Answering queries using views: A survey. The VLDB Journal, 10 (4), 270–294. doi: https://doi.org/10.1007/s007780100054
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- Chak, D. Materialized views that work. Available at: https://www.pgcon.org/2008/schedule/attachments/64_BSDCan2008-MaterializedViews-paper.pdf
- Almazyad, A., Siddiquim, M. K. (2010). Incremental View Maintenance: An Algorithmic Approach. International Journal of Electrical & Computer Sciences IJECS-IJENS, 10 (03), 16–21.
- 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
- 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
- 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
- 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
- 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
How to Cite
Copyright (c) 2019 Nguyen Tran Quoc Vinh, Dang Thanh Hao, Pham Duong Thu Hang, Abeer Alsadoon, PW Chandana Prasad, Nguyen Viet Anh
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 PC TECHNOLOGY CENTER, 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 PC TECHNOLOGY CENTER 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.