Speeding Up Materialized View Selection in Data Warehouses Using a Randomized Algorithm

Abstract

A data warehouse stores information that is collected from multiple, heterogeneous information sources for the purpose of complex querying and analysis. Information in the warehouse is typically stored in the form of materialized views, which represent pre-computed portions of frequently asked queries. One of the most important tasks when designing a warehouse is the selection of materialized views to be maintained in the warehouse. The goal is to select a set of views in such a way as to minimize the total query response time over all queries, given a limited amount of time for maintaining the views (maintenance-cost view selection problem). In this paper, we propose an efficient solution to the maintenance-cost view selection problem using a genetic algorithm for computing a near-optimal set of views. Specifically, we explore the maintenance-cost view selection problem in the context of OR view graphs. We show that our approach represents a dramatic improvement in time complexity over existing search-based approaches using heuristics. Our analysis shows that the algorithm consistently yields a solution that lies within 10% of the optimal query benefit while at the same time exhibiting only a linear increase in execution time. We have implemented a prototype version of our algorithm which is used to simulate the measurements used in the analysis of our approach.

Modeling View Selection as a Constraint Satisfaction Problem

“…Randomize algorithms such as simulated annealing algorithms [7,8,14] and genetic algorithms [5,13,18,30,16] have been used and explored for the selection of materialized views in order to improve the quality of the solution. These algorithms use a randomized search strategy for deciding which views to materialize.…”

“…The most commonly used randomized algorithms in the context of view selection are simulated annealing algorithms and genetic algorithms. The motivation to compare our approach with genetic algorithm is based on the observation that genetic algorithm in contrast with simulated annealing algorithm use a multi-directional search which allows to find a point near the global optimum [18]. A comparison with hybrid approaches has not been made because of their excessive computation time [32].…”

“…Although, several heuristic algorithms have been proposed in literature to solve the view selection problem such as deterministic algorithms i.e., greedy algorithms [11,29,12,25,20,21,26,2], randomized algorithms i.e., genetic algorithms [31,18,13,30,16,5] and simulated annealing algorithms [14,7,8] or hybrid algorithms [32] which combine the strategies of pure deterministic algorithms and pure randomized algorithms.…”

When is it time to rethink the aggregate configuration of your OLAP server?

“…, Mn} that maximizes the benefit by taking space constraints into account. For this purpose, several algorithms have been presented in the literature [7,8,11]. However, these are rather expensive algorithms.…”

Multiobjective genetic algorithms for materialized view selection in OLAP data warehouses

“…In the view selection problem one wishes to select a set of views for materialization which minimizes one or more objectives, possibly subject to one or more constraints. Many variants of the view selection problem have been studied for example: i) minimizing the query cost of a materialized view set subject to a storage size constraint [12,22,15,21,3], ii) minimizing query cost and maintenance cost of a materialized view set [2, 9,27,14,26,24,25,30,28,11], and iii) minimizing query cost under a maintenance cost constraint [20,19,15,29,10].…”