Coupling Materialized View Selection to Multi Query Optimization

2015 ◽  
Vol 11 (2) ◽  
pp. 62-84 ◽  
Author(s):  
Ahcene Boukorca ◽  
Ladjel Bellatreche ◽  
Sid-Ahmed Benali Senouci ◽  
Zoé Faget
Keyword(s):  
Query Optimization ◽  
Selection Process ◽  
Optimization Techniques ◽  
Global Processing ◽  
Materialized Views ◽  
Hard Problem ◽  
Graph Structure ◽  
View Selection ◽  
Materialized View ◽  
Materialized View Selection

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.

Efficient Materialized View Selection for Multi-Dimensional Data Cube Models

2016 ◽  
Vol 6 (3) ◽  
pp. 52-74 ◽  
Author(s):  
Naveen Dahiya ◽  
Vishal Bhatnagar ◽  
Manjeet Singh
Keyword(s):  
Decision Support ◽  
Response Time ◽  
Decision Support Systems ◽  
Query Optimization ◽  
Support Systems ◽  
Optimization Techniques ◽  
View Selection ◽  
Materialized View ◽  
Selection Approach ◽  
Materialized View Selection

Decision Support Systems help managers to make intelligent decisions by throwing complex queries on large databases. The response time to queries is a very crucial factor in governing the quality of decision support systems. The response time can be greatly improved by using query optimization techniques. A powerful query optimization technique selects only some of the views and not all views for materialization. The authors in this paper present a refined greedy selection approach using forward references to give better materialized view selection. The approach works on lattice framework of data that is capable enough to show inter dependencies of data. The choice of materialized views using the proposed approach gives a better trade off in terms of space/benefits, which is proved from the experimental results. The refined greedy selection approach is independent of space constraint and depends on number of passes entered by the user. The view selection is further enhanced by including space constraints to the results of greedy and refined greedy approach using knapsack implementation.

Materialized View Selection in the Data Warehouse

2010 ◽  
Vol 29-32 ◽  
pp. 1133-1138 ◽  
Author(s):  
Li Juan Zhou ◽  
Hai Jun Geng ◽  
Ming Sheng Xu
Keyword(s):  
Decision Support ◽  
Data Warehouse ◽  
Materialized Views ◽  
Storage Space ◽  
View Selection ◽  
Materialized View ◽  
Query Response Time ◽  
Materialized View Selection ◽  
Optimal Efficiency ◽  
The Cost

A data warehouse stores materialized views of data from one or more sources, with the purpose of efficiently implementing decision-support or OLAP queries. Materialized view selection is one of the crucial decisions in designing a data warehouse for optimal efficiency. The goal is to select an appropriate set of views that minimizes sum of the query response time and the cost of maintaining the selected views, given a limited amount of resource, e.g., materialization time, storage space, etc. In this article, we present an improved PGA algorithm to accomplish the view selection problem; the experiments show that our proposed algorithm shows it’s superior.

Multi-Objective Materialized View Selection Using Improved Strength Pareto Evolutionary Algorithm

2019 ◽  
Vol 9 (2) ◽  
pp. 1-21
Author(s):  
Jay Prakash ◽  
T. V. Vijay Kumar
Keyword(s):  
Evolutionary Algorithm ◽  
Selection Problem ◽  
Maintenance Cost ◽  
Materialized Views ◽  
Trade Off ◽  
View Selection ◽  
Materialized View ◽  
Strength Pareto Evolutionary Algorithm ◽  
Materialized View Selection ◽  
Selection Of

A data warehouse system uses materialized views extensively in order to speedily tackle analytical queries. Considering that all possible views cannot be materialized due to maintenance cost and storage constraints, the selection of an appropriate set of views to materialize that achieve an optimal trade-off among query response time, maintenance cost, and the storage constraint becomes an essential necessity. The selection of such an appropriate set of views for materialization is referred to as the materialized views selection problem, which is an NP-Complete problem. In the last two decades, several new selection approaches, based on heuristics, have been proposed. Most of these have used a single objective or weighted sum approach to address the various constraints. In this article, an attempt has been made to address the bi-objective materialized view selection problem, where the objective is to minimize the view evaluation cost of materialized views and the view evaluation cost of the non-materialized views, using the Improved Strength Pareto Evolutionary Algorithm. The experimental results show that the proposed multi-objective view selection algorithm is able to select the Top-K views that achieves a reasonable trade-off between the two objectives. Materializing these selected views would reduce the query response times for analytical queries and thereby facilitates the decision-making process.

Research on Materialized Views under Disk-Space Constraint in Data Warehouse

2014 ◽  
Vol 989-994 ◽  
pp. 1955-1958
Author(s):  
Lei Ma
Keyword(s):  
Data Warehouse ◽  
Space Constraint ◽  
Maintenance Cost ◽  
Materialized Views ◽  
Ranking Algorithm ◽  
Disk Space ◽  
View Selection ◽  
Materialized View ◽  
Processing Cost ◽  
Materialized View Selection

Materialized view is an important topic in data warehouse research, and also affects the query efficiency and maintenance cost. The disk-space view-selection problem is to select a set of materialized views for the purpose of minimizing the total query processing cost and the total maintenance cost. In this paper we introduce evolutionary algorithm using stochastic ranking algorithm, which can enable materialized view selection under disk-space constraint. The algorithm improve the stochastic ranking algorithm, which can find a near-optimal feasible solution. This paper use the algorithm in Police data warehouse.

Materialized view selection and maintenance using multi-query optimization

2001 ◽  
Vol 30 (2) ◽  
pp. 307-318 ◽  
Author(s):  
Hoshi Mistry ◽  
Prasan Roy ◽  
S. Sudarshan ◽  
Krithi Ramamritham
Keyword(s):  
Query Optimization ◽  
View Selection ◽  
Materialized View ◽  
Materialized View Selection

Materialized View Selection for Query Performance Enhancement Using Stochastic Ranking Based Cuckoo Search Algorithm

2019 ◽  
Vol 27 (03) ◽  
pp. 2050008
Author(s):  
Anjana Gosain ◽  
Kavita Sachdeva
Keyword(s):  
Query Processing ◽  
Search Algorithm ◽  
Cuckoo Search ◽  
Cuckoo Search Algorithm ◽  
Materialized Views ◽  
Processing Efficiency ◽  
View Selection ◽  
Materialized View ◽  
Materialized View Selection ◽  
And Performance

Materialized view selection (MVS) improves the query processing efficiency and performance for making decisions effectively in a data warehouse. This problem is NP-hard and constrained optimization problem which involves space and cost constraint. Various optimization algorithms have been proposed in literature for optimal selection of materialized views. Few works exist for handling the constraints in MVS. In this study, authors have proposed the Cuckoo Search Algorithm (CSA) for optimization and Stochastic Ranking (SR) for handling the constraints in solving the MVS problem. The motivation behind integrating CS with SR is that fewer parameters have to be fine tuned in CS algorithm than in genetic and Particle Swarm Optimization (PSO) algorithm and the ranking method of SR handles the constraints effectively. For proving the efficiency and performance of our proposed algorithm Stochastic Ranking based Cuckoo Search Algorithm for Materialized View Selection (SRCSAMVS), it has been compared with PSO, genetic algorithm and the constrained evolutionary optimization algorithm proposed by Yu et al. SRCSAMVS outperforms in terms of query processing cost and scalability of the problem.

Random Walk Grey Wolf Optimizer Algorithm for Materialized View Selection (RWGWOMVS)

2020 ◽  
pp. 101-122
Author(s):  
Anjana Gosain ◽  
Kavita Sachdeva
Keyword(s):  
Random Walk ◽  
Materialized Views ◽  
Grey Wolf Optimizer ◽  
Grey Wolf ◽  
View Selection ◽  
Materialized View ◽  
Bee Colony ◽  
Search Optimization ◽  
Materialized View Selection ◽  
Selection Of

Optimal selection of materialized views is crucial for enhancing the performance and efficiency of data warehouse to render decisions effectively. Numerous evolutionary optimization algorithms like particle swarm optimization (PSO), genetic algorithm (GA), bee colony optimization (BCO), backtracking search optimization algorithm (BSA), etc. have been used by researchers for the selection of views optimally. Various frameworks like multiple view processing plan (MVPP), lattice, and AND-OR view graphs have been used for representing the problem space of MVS problem. In this chapter, the authors have implemented random walk grey wolf optimizer (RWGWO) algorithm for materialized view selection (i.e., RWGWOMVS) on lattice framework to find an optimal set of views within the space constraint. RWGWOMVS gives superior results in terms of minimum total query processing cost when compared with GA, BSA, and PSO algorithm. The proposed method scales well on increasing the lattice dimensions and on increasing the number of queries triggered by users.

scholarly journals Optimal Materialized View Management in Distributed Environment Using Random Walk Approach

2016 ◽  
Vol 1 ◽  
pp. 67
Author(s):  
Purushottam Bagale ◽  
Shashidhar Ram Joshi
Keyword(s):  
Research Work ◽  
Search Time ◽  
Distributed Database ◽  
Maintenance Cost ◽  
Materialized Views ◽  
Distributed Environment ◽  
View Selection ◽  
Materialized View ◽  
Business Application ◽  
Materialized View Selection

<p>Materialized View selection and maintenance is a critical problem in many applications. In large databases particularly in distributed database, query response time plays an important role as timely access to information and it is the basic requirement of successful business application. The materialization of all views is not possible because of the space constraint and maintenance cost constraint. Materialized views selection is one of the crucial decisions in designing a data warehouse for optimal efficiency. Selecting a suitable set of views that minimizes the total cost associated with the materialized views is the key component in distributed database environment. Several solutions have been proposed in the literature to solve this problem. However, most studies do not encompass search time, storage constrains and maintenance cost. In this research work two algorithms are depicted; first for materialized view selection and maintenance in distributed environment where database is distributed, Second algorithm is for node selection in distributed environment. </p><p><em>Journal of Advanced College of Engineering and Management, Vol.1</em>, 2015, 69-75</p>

Research on Materialized View Selection in the Data Warehouse

2011 ◽  
Vol 55-57 ◽  
pp. 361-366
Author(s):  
Li Juan Zhou ◽  
Hai Jun Geng ◽  
Ming Sheng Xu
Keyword(s):  
Data Warehouse ◽  
High Efficiency ◽  
Resource Constraints ◽  
Maintenance Cost ◽  
Materialized Views ◽  
View Selection ◽  
Materialized View ◽  
Cost Constraints ◽  
Processing Cost ◽  
Materialized View Selection

Materialized view is an effective method for improving the efficiency of queries in data warehouse system, and the problem of materialized view selection is one of the most important decisions. In this paper, an algorithm was proposed to select a set of materialized views under maintenance cost constraints for the purpose of minimizing the total query processing cost; the algorithm adopts the dynamic penalty function to solve the resource constraints view selection. The experimental study shows that the algorithm has better solutions and high efficiency.

Sign in / Sign up

Export Citation Format

Share Document