Dynamic materialized view selection based on two-phase optimization

Abstract

A Data Warehouses is a repository of information integrated from a distributed data source. Information stored in a data warehouse is the form of the materialized view. Materializing view is a technique to improve query response time in a data warehouse. Deciding which of the appropriated views should be materialized views is one of the significant problems in data warehouse design. In order to solve this problem, constructing a search space close to optimal is a necessary task. It provides effective results for the selection of views to be materialized. The Multiple View Processing Plan (MVPP) is one of the several approaches to construct the optimal search space for the view selection problem. However, some merged queries in MVPP provide the query processing cost not close to optimal. Therefore, we proposed the re-optimized MVPP algorithm to improve the query processing cost of those queries by rewriting them using global common subexpression. In the real situation, the requirements are frequently changed by the stakeholder. Therefore, the existing materialized views and virtual views derived from static materialized view selection should be considered whether they are still suitable to support all requirements, the existing and new requirements. In this research, we propose an approach for dynamic materialized view selection based on proposed reoptimized MVPP algorithm. We propose the algorithm to determine the existing materialized views and virtual views that are affected by changing the requirement rather than all existing resource in the search space. The experiment shows that our approach, the re-optimized MVPP, improves the total query processing cost of MVPP. Also the summation of query processing costs and materialized view maintenance costs are reduced after the set of views are selected to be materialized by using the Two-Phase Optimization algorithm. For our dynamic materialized view selection approach, the experiment shows that our approach can specify the member of a set of views to be selected rather than all existing views in the search space. It provides optimal total cost without recalculating all requirements from scratch.