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

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

Development of the procedure for the estimation of reliability of reinforced concrete beams, strengthened by building up the stretched reinforcing bars under load

<p>We studied the stresses state of rectangular reinforced concrete beams strengthened by building up the stretched reinforcing bars under the action of load. We developed two principal methods for assessing the reliabil...

Comparative investigation of electrochemically synthesized (α+β) layered nickel hydroxide with mixture of α-Ni(OH)2 and β-Ni(OH)2

<p>Nickel hydroxides, owing to their high electrochemical activity are promising materials for chemical power sources (alkaline accumulators, lithium accumulators and hybrid supercapacitors), electrochromic devices, elec...

Determination of the optimal parameters of a tubular belt conveyor depending on such an economical

<p class="20">We have investigated the influence of belt operation durability on the cost of transporting a ton of cargo. The study revealed that the longer the durability of a transporting installation, the lower the co...

The synthesis of control algorithm over a technical condition of the hydrogen generators based on hydro­reactive compositions

<p>We have synthesized a control algorithm over technical condition of the hydrogen generators, which includes the following: forming initial data, determining the values for a phase-frequency characteristic of the gener...

Improving the model of decision making about abnormal network state using a positioning system

<p>We have proposed to supplement the model of decision making about abnormal states of a wireless network under conditions of uncertainty by another attribute ‒ the location of wireless mobile and stationary devices in...

Download PDF file
  • EP ID EP667097
  • DOI 10.15587/1729-4061.2019.180226
  • Views 91
  • Downloads 0

How To Cite

Nguyen Tran Quoc Vinh, Dang Thanh Hao, Pham Duong Thu Hang, Abeer Alsadoon, PW Chandana Prasad, Nguyen Viet Anh (2019). A solution for synchronous incremental maintenance of materialized views based on SQL recursive query. Восточно-Европейский журнал передовых технологий, 5(2), 6-17. https://europub.co.uk/articles/-A-667097