Multi-Objective Genetic Algorithm for Materialized View Optimization in Data Warehouses

TL;DR

This work tackles the MV selection problem in data warehouses by casting it as a multi-objective optimization solved via a genetic algorithm. Key innovations include a binary encoding of MV configurations, Lexicase-based parent selection to preserve diversity, localized BLX crossover to accelerate search, and an adaptive mutation mechanism guided by population diversity. The fitness function combines response time, maintenance cost, and memory usage with configurable shaping and weighting to reflect priorities. On the TPC-H benchmark, the approach achieves faster optimization (203 seconds) and lowers maintenance and total costs compared with state-of-the-art methods, demonstrating a data-driven, scalable pathway to cost-effective MV utilization in enterprise systems. The results suggest strong practical impact for automated MV configuration in analytics workloads and point to future work in dynamic, workload-aware adaptations.

Abstract

Materialized views can significantly improve database query performance but identifying the optimal set of views to materialize is challenging. Prior work on automating and optimizing materialized view selection has limitations in execution time and total cost. In this paper, we present a novel genetic algorithm based approach to materialized view selection that aims to minimize execution time and total cost. Our technique encodes materialized view configurations as chromosomes and evolves the population over generations to discover high quality solutions. We employ an adaptive mutation rate, multi-objective fitness function, and lexicase selection to enhance genetic search. Comprehensive experiments on the TPC-H benchmark demonstrate the effectiveness of our algorithm. Compared to stateof-the-art methods, our approach improves average execution time by 11% and reduces total materialized view costs by an average of 16 million. These gains highlight the benefits of a datadriven evolutionary approach. Our genetic algorithm framework significantly outperforms current materialized view selection techniques in both efficiency and total cost reduction. This work represents an important advance in enabling performant and cost-effective utilization of materialized views in enterprise systems.

Figures (4)

• Figure 1: The fitness values of each chromosome.
• Figure 2: Comparison between our proposed approach and other methods in terms of execution time.
• Figure 3: The maintenance cost of the methods.
• Figure 4: The total cost of the methods.