A solution for synchronous incremental maintenance of materialized views based on SQL recursive query
Journal Title: Восточно-Европейский журнал передовых технологий - Year 2019, Vol 5, Issue 2
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
Authors and Affiliations
Nguyen Tran Quoc Vinh, Dang Thanh Hao, Pham Duong Thu Hang, Abeer Alsadoon, PW Chandana Prasad, Nguyen Viet Anh
Developing and programming the algorithm of refinement of the crystal structure of materials with possible isomorphous substitution
In general, the software for analyzing data using the X-ray diffraction method does not include the possibility for using stoichiometric principles between chemical elements and the relations between the occupation of cr...
Analysis of the influence of the inter-wheel differentials design on the resistance of the car curved motion
<p>The method and results of the analysis of the influence of the inter-wheel differentials design and the corresponding proportionality factors for the additional locking torque on the curved motion resistance of the fo...
Effect of the cryopowder "beet" on quality indicators of new curd desserts
<p>Natural plant bio-additives deserve special attention, because due to their natural properties they provide dairy products with functional properties. The use of such additives makes it possible to fill the shortage o...
Analysis of convergence of adaptive singlestep algorithms for the identification of nonstationary objects
<p>The study deals with the problem of identification of non-stationary parameters of a linear object which can be described by first-order Markovian model, with the help of the simplest in computational terms single-ste...
Numerical study of flows in axial compressors of aircraft gas-turbine engines
<p>Design and adjustment of compressors at modern gas-turbine engines are based on the wide use of numerical analysis methods of a various level of complexity. Such approaches make it possible to analyze the alignment of...