Materialized View Maintenance Research Articles

Parallel Maintenance of Materialized Views in Large-Scale Analytic Platforms

Speeding up queries processing is an important issue in database management. Materialized views are largely used to address this issue. They have been proven successful for query performance optimization. However, updating data sources of the corresponding view requires maintaining the related views. Therefore, a view maintenance strategy is required. This paper presents a novel approach for materialized view maintenance that overcomes the limitations of prior approaches using a parallel “Divide and Conquer” strategy. We modeled the view maintenance problem using a new concept called “Multiple-Views-Matrix” as a matrix that brings up all affected views and their corresponding base relations. Moreover, we introduce a new algorithm for performing maintenance of multiple views, the proposed algorithm is able to use multiple parallelism and recursivity; this allows it to maintain multiple views and to process several updates at the same time. We show that our method provides a significant improvement in terms of maintenance process cost.

Meet me halfway

From Industry 4.0-driven factories to real-time trading algorithms, businesses depend on analytics on high-velocity real-time data. Often these analytics are performed not in dedicated stream processing engines but on views within a general-purpose database to combine current with historical data. However, traditional view maintenance algorithms are not designed with both the volume and velocity of data streams in mind. In this paper, we propose a new type of view specialized for queries involving high-velocity inputs, called continuous view. The key component of continuous views is a novel maintenance strategy, splitting the work between inserts and queries. By performing initial parts of the view's query for each insert and the remainder at query time, we achieve both high input rates and low query latency. Further, we keep the memory overhead of our views small, independent of input velocity. To demonstrate the practicality of this strategy, we integrate continuous views into our Umbra database system. We show that split maintenance can outperform even dedicated stream processing engines on analytical workloads, all while still offering similar insert rates. Compared to modern materialized view maintenance approaches, such as deferred and incremental view maintenance, that often need to materialize expensive deltas, we achieve up to an order of magnitude higher insert throughput.

Materialized View Maintenance: Issues, Classification, and Open Challenges

Views materialization is a powerful technique for optimization of query evaluation. However, when data sources are updated, these materialized views (MVs) should be maintained in order to get appropriate queries’ answers. This work provides an overview of the research field of view maintenance, focusing on view maintenance issues and dimensions that cover the most important ones proposed in the literature review. We provide a classification and review the state-of-the-art of view maintenance approaches according to several parameters characterizing their algorithms, including information considered during the view maintenance process, principle used, and the execution mechanism. We provide the most important performance evaluation metrics of view maintenance process while performing a comparative study of some important approaches. Lastly, we give comprehensive perspectives on view maintenance in the different promising environments of MV applications.

Scalable Atomic Visibility with RAMP Transactions

Databases can provide scalability by partitioning data across several servers. However, multipartition, multioperation transactional access is often expensive, employing coordination-intensive locking, validation, or scheduling mechanisms. Accordingly, many real-world systems avoid mechanisms that provide useful semantics for multipartition operations. This leads to incorrect behavior for a large class of applications including secondary indexing, foreign key enforcement, and materialized view maintenance. In this work, we identify a new isolation model—Read Atomic (RA) isolation—that matches the requirements of these use cases by ensuring atomic visibility : either all or none of each transaction’s updates are observed by other transactions. We present algorithms for Read Atomic Multipartition (RAMP) transactions that enforce atomic visibility while offering excellent scalability, guaranteed commit despite partial failures (via coordination-free execution ), and minimized communication between servers (via partition independence ). These RAMP transactions correctly mediate atomic visibility of updates and provide readers with snapshot access to database state by using limited multiversioning and by allowing clients to independently resolve nonatomic reads. We demonstrate that, in contrast with existing algorithms, RAMP transactions incur limited overhead—even under high contention—and scale linearly to 100 servers.

English

In recent years, materialized views (MVs) are widely used to enhance the database performance by storing pre-calculated results of resource-intensive queries in the physical memory. In order to identify which queries may be potentially materialized, database transaction log for a long period of time should be analyzed. The goal of analysis is to distinguish resource-intensive and frequently used queries collected from database log, and optimize these queries by implementation of MVs. In order to achieve greater efficiency of MVs, they were used not only for the optimization of single queries, but also for entire groups of queries that are similar in syntax and execution results. Thus, the problem stated in this article is the development of approach that will allow forming groups of queries with similar syntax around the most resource-intensive queries in order to identify the list of potential candidates for materialization. For solving this problem, we have applied the algorithm of categorical data clustering to the query grouping problem on the step of database log analysis and searching candidates for materialization. In the current work CLOPE algorithm was modified to cover the introduced problem. Statistical and timing indicators were taken into account in order to form the clusters around the most resource intensive queries. of modified algorithm CLOPE allowed to decrease calculable complexity of clustering and to enhance the quality of formed groups. ACM CCS (2012) Classification : Information systems→ Data management systems → Database management system engines→Query optimization; Computing methodologies→Cluster analysis *To cite this article: K. Novokhatska and O. Kungurtsev, Application of Clustering Algorithm CLOPE to the Query Grouping Problem in the Field of Materialized View Maintenance, CIT. Journal of Computing and Information Technology , vol. 24, no. 1, pp. 79-89, 2016.

Interview with Frank Tompa

Since completing his ScB and ScM at Brown University in 1970 and his PhD in Computer Science at the University of Toronto in 1974, Frank Tompa has been on the faculty in Computer Science at the University of Waterloo. He has also worked for periods of several months at the Oxford University Press, Bellcore, Microsoft Research, the University of Toronto, and Stanford University. His teaching and research interests are in the fields of data structures and databases, particularly the design of text management systems suitable for maintaining large reference texts and large, heterogeneous text collections. He has co-authored papers in the areas of database dependency theory, storage structure selection, query processing, materialized view maintenance, text matching, XML processing, structured text conversion, database integration, data retention and security, and text classification. In 2005, the University of Waterloo and the City of Waterloo announced the naming of the road Frank Tompa Drive in recognition of Professor Tompa being one of those who "epitomize the energy and enterprise that characterize the University of Waterloo." In 2010, he was named a Fellow of the ACM for contributions to text-dominated and semi-structured data management.

The Design and Implementation of Agricultural Production Data Warehouse

This paper designed a data warehouse model of agricultural production. And it built an effective and viable agricultural production data warehouse, by using some key technologies: multidimensional data analysis, cube, materialized view selection, materialized view maintenance. Finally, it provided a solution for the effective management and maintenance problems about high-capacity heterogeneous data.

Scalable probabilistic databases with factor graphs and MCMC

Incorporating probabilities into the semantics of incomplete databases has posed many challenges, forcing systems to sacrifice modeling power, scalability, or treatment of relational algebra operators. We propose an alternative approach where the underlying relational database always represents a single world, and an external factor graph encodes a distribution over possible worlds; Markov chain Monte Carlo (MCMC) inference is then used to recover this uncertainty to a desired level of fidelity. Our approach allows the efficient evaluation of arbitrary queries over probabilistic databases with arbitrary dependencies expressed by graphical models with structure that changes during inference. MCMC sampling provides efficiency by hypothesizing modifications to possible worlds rather than generating entire worlds from scratch. Queries are then run over the portions of the world that change, avoiding the onerous cost of running full queries over each sampled world. A significant innovation of this work is the connection between MCMC sampling and materialized view maintenance techniques: we find empirically that using view maintenance techniques is several orders of magnitude faster than naively querying each sampled world. We also demonstrate our system's ability to answer relational queries with aggregation, and demonstrate additional scalability through the use of parallelization on a real-world complex model of information extraction. This framework is sufficiently expressive to support probabilistic inference not only for answering queries, but also for inferring missing database content from raw evidence.

A Space-Efficient Protocol for Consistency of External View Maintenance on Data Warehouse Systems

The materialized view approach is widely adopted in implementations of data warehouse systems in or-der for efficiency purposes. In terms of the construction of a materialized data warehouse system, some managerial problems still exist to most developers and users in the view resource maintenance area in particular. Resource redundancy and data inconsistency among materialized views in a data warehouse system is a problem that many developers and users struggle with. In this article, a space-efficient protocol for materialized view maintenance with a global data view on data warehouses with embedded proxies is proposed. In the protocol set, multilevel proxy-based protocols with a data compensating mechanism are provided to certify the consistency and uniqueness of materialized data among data resources and materialized views. The authors also provide a set of evaluation experiences and derivations to verify the feasibility of proposed protocols and mechanisms. With such protocols as proxy services, the performance and space utilization of the materialized view approach will be improved. Furthermore, the consistency issue among materialized data warehouses and heterogeneous data sources can be properly accomplished by applying a dynamic compensating and synchronization mechanism. The trade-off between efficiency, storage consumption, and data validity for view maintenance tasks can be properly balanced.

Locking protocols for materialized aggregate join views

The maintenance of materialized aggregate join views is a well-studied problem. However, to date the published literature has largely ignored the issue of concurrency control. Clearly, immediate materialized view maintenance with transactional consistency, if enforced by generic concurrency control mechanisms, can result in low levels of concurrency and high rates of deadlock. While this problem is superficially amenable to well-known techniques, such as fine-granularity locking and special lock modes for updates that are associative and commutative, we show that these previous high concurrency locking techniques do not fully solve the problem, but a combination of a "value-based" latch pool and these previous high concurrency locking techniques can solve the problem.

Data warehouse clustering on the web

In collaborative e-commerce environments, interoperation is a prerequisite for data warehouses that are physically scattered along the value chain. Adopting system and information quality as success variables, we argue that what is required for data warehouse refreshment in this context is inherently more complex than the materialized view maintenance problem and we offer an approach that addresses refreshment in a federation of data warehouses. Defining a special kind of materialized views, we propose an open multi-agent architecture for their incremental maintenance while considering referential integrity constraints on source data.

Multiversion-based view maintenance over distributed data sources

Materialized views can be maintained by submitting maintenance queries to the data sources. However, the query results may be erroneous due to concurrent source updates. State-of-the-art maintenance strategies typically apply compensations to resolve such conflicts and assume all source schemata remain stable over time. In a loosely coupled dynamic environment, the sources may autonomously change not only their data but also their schema or semantics. Consequently, either the maintenance or the compensation queries may be broken. Unlike compensation-based approaches found in the literature, we instead model the complete materialized view maintenance process as a view maintenance transaction (VM_Transaction). This way, the anomaly problem can be rephrased as the serializability of VM_Transactions. To achieve VM_Transaction serializability, we propose a multiversion concurrency control algorithm, called TxnWrap , which is shown to be the appropriate design for loosely coupled environments with autonomous data sources. TxnWrap is complementary to the maintenance algorithms proposed in the literature, since it removes concurrency issues from consideration allowing the designer to focus on the maintenance logic. We show several optimizations of TxnWrap, in particular, (1) space optimizations on versioned data materialization and (2) parallel maintenance scheduling. With these optimizations, TxnWrap even outperforms state-of-the-art view maintenance solutions in terms of refresh time. Further, several design choices of TxnWrap are studied each having its respective advantages for certain environmental settings. A correctness proof based on transaction theory for TxnWrap is also provided. Last, we have implemented TxnWrap. The experimental results confirm that TxnWrap achieves predictable performance under a varying rate of concurrency.

On the abductive or deductive nature of database schema validation and update processing problems

We show that database schema validation and update processing problems such as view updating, materialized view maintenance, integrity constraint checking, integrity constraint maintenance or condition monitoring can be classified as problems of either abductive or deductive nature, according to the reasoning paradigm that inherently suites them. This is done by performing abductive and deductive reasoning on the event rules (Olive 1991), a set of rules that define the difference between consecutive database states. In this way, we show that it is possible to provide methods able to deal with all these problems as a whole. We also show how some existing general deductive and abductive procedures may be used to reason on the event rules. In this way, we show that these procedures can deal with all database schema validation and update processing problems considered in this paper.

Materialized view maintenance and integrity constraint checking

We investigate the problem of incremental maintenance of an SQL view in the face of database updates, and show that it is possible to reduce the total time cost of view maintenance by materializing (and maintaining) additional views. We formulate the problem of determining the optimal set of additional views to materialize as an optimization problem over the space of possible view sets (which includes the empty set). The optimization problem is harder than query optimization since it has to deal with multiple view sets, updates of multiple relations, and multiple ways of maintaining each view set for each updated relation.We develop a memoing solution for the problem; the solution can be implemented using the expression DAG representation used in rule-based optimizers such as Volcano. We demonstrate that global optimization cannot, in general, be achieved by locally optimizing each materialized subview, because common subexpressions between different materialized subviews can allow nonoptimal local plans to be combined into an optimal global plan. We identify conditions on materialized subviews in the expression DAG when local optimization is possible. Finally, we suggest heuristics that can be used to efficiently determine a useful set of additional views to materialize.Our results are particularly important for the efficient checking of assertions (complex integrity constraints) in the SQL-92 standard, since the incremental checking of such integrity constraints is known to be essentially equivalent to the view maintenance problem.