Coupling Materialized View Selection to Multi Query Optimization

Abstract

Materialized views are queries whose results are stored and maintained in order to facilitate access to data in their underlying base tables of extremely large databases. Selecting the best materialized views for a given query workload is a hard problem. Studies on view selection have considered sharing common sub expressions and other multi-query optimization techniques. Multi-Query Optimization is a well-studied domain in traditional and advanced databases. It aims at optimizing a workload of queries by finding and reusing common sub-expression between queries. Finding the best shared expression is known as a NP-hard problem. The shared expressions usually identified by graph structure have been used to be candidate for materialized views. This shows the strong interdependency between the problems of materialized view selection (PVS) and multi query optimization (PMQO), since the PVS uses the graph structure of the PMQO. Exploring the existing works on PVS considering the interaction between PVS and PMQO figures two main categories of studies: (i) those considering the PMQO as a black box where the output is the graph and (ii) those preparing the graph to guide the materialized view selection process. In this category, the graph generation is based on individual query plans, an approach that does not scale, especially with the explosion of Big Data applications requiring large number of complex queries with high interaction. To ensure a scalable solution, this work proposes a new technique to generate a global processing plan without using individual plans by borrowing techniques used in the electronic design automation (EDA) domain. This paper first presents a rich state of art regarding the PVS and a classification of the most important existing work. Secondly, an analogy between the MQO problem and the EDA domain, in which large circuits are manipulated, is established. Thirdly, it proposes to model the problem with hypergraphs which are massively used to design and test integrated circuits. Fourthly, it proposes a deterministic algorithm to select materialized views using the global processing plan. Finally, experiments are conducted to show the scalability of our approach.

ProRes: Proactive re-selection of materialized views

“…To fill this gap, hypergraphs have been proposed in [7] to capture the query interaction among very large sets of static queries, then to study their contributions for selecting an appropriate set of materialized views. Promising results have been obtained showing the great benefit of hypergraphs to deal jointly with PMQO and the VSP [7] [49].…”

“…(c) Remove the pivot node from the hypergraph. We mention that we were inspired by the work proposed by [7] to generate the UQP which ensured the scalability of our approach. Figure 8 shows an example for the transformation step of the hypergraph to an oriented graph.…”

“…They contributed in several domains in capturing the interaction between studied objects such as data mining, text/image retrieval, bio-informatics, social mining, and machine learning [26]. In the database fields, Hypergraphs have been used at logical and physical phases (e.g., the detection of functional dependencies [20], data partitioning for optimizing OLTP workloads [16] and materialized views selection for optimizing largescale OLAP workloads [7]).…”

Semantic Web Query Join Optimization Using Modified Grey Wolf Optimization Algorithm

“…Generally, the computational time of the query depends on the optimization algorithm and query path. The size of the query path increases with the size of queries, so it consumes less time to optimize the query path [9,10]. Presently, numerous soft computing methodologies utilized to reduce the time consumption and query path optimization.…”