Multi-Objective Genetic Algorithm for Materialized View Optimization in Data Warehouses (2403.19906v1)
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.
- V. Kharat and M. Shelar, “An efficient Query optimizer with materialized intermediate views in distributed and cloud environment,” Tehnickiglasnik ˇ , vol. 15, no. 1, pp. 105–111, 2021.
- A. Gosain and K. Sachdeva, “Materialized view selection for query performance enhancement using stochastic ranking based cuckoo search algorithm,” International Journal of Reliability, Quality and Safety Engineering, vol. 27, no. 3, pp. 2050008, 2020.
- S. Roy, B. Shit, S. Sen and A. Cortesi, “Construction and distribution of materialized views in non-binary data space,” Innovations in Systems and Software Engineering, vol. 17, no. 3, pp. 205–217, 2021.
- M. C. Mouna, L. Bellatreche and N. Boustia, “ProRes: Proactive re-selection of materialized views,” Computer Science and Information Systems, vol. 19, no. 2, pp. 735–762, 2022.
- Y. Wang, X. Qiao and G. G. Wang, “Architecture evolution of convolutional neural network using monarch butterfly optimization,” Journal of Ambient Intelligence and Humanized Computing, vol. 8, no. 6, pp. 1–15, 2022.
- Council, Transaction Processing Performance. ”TPC BENCHMARK H (Decision Support) Standard Specification Revision 2.1. 0.” http://www. tpc. org/tpch/spec/tpch2. 1.0. pdf (2003).