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
Keywords
Related Articles
- EP ID EP667097
- DOI 10.15587/1729-4061.2019.180226
- Views 94
- Downloads 0
Development of a mobile decision support system based on the smart method for android platform
<p>The work is devoted to the development of a mobile decision support system for solving the multiple criteria decision-making problems. To ensure the autonomous operation of the system, it was proposed to use a three-l...
Determinig the parameters of the acoustic system for the primary treatment of wool
<p>We have studied the process of primary treatment of wool in order to remove soil and manure contaminants, vegetable impurities, fat, and sweat. The primary treatment of wool performs a key role in retaining the natura...
Algorithmizing the methods of basis matrices in the study of balace intersectoral ecological and economic models
<p>Ecological-economic models (such as Leontiev-Ford) play a special role in solving the fundamental problems of long-term planning, taking into account the factor of environmental management. On their basis, the task of...
Devising a procedure to choose optimal parameters for the electromechanical shock absorber for a subway car
<p>A procedure for determining basic estimation parameters has been devised for the proposed structure of the electromechanical shock absorber. The procedure is based on a simplified mathematical model for determining th...
Research into cavitation processes in the trapped volume of the gear pump
<p class="1CxSpFirst">We investigated processes in the trapped volume of the gear pump, formed due to the peculiarities in the geometry of the involute gearing characteristic of the pumps of a given type. In the fluid co...