A Data Warehouse Optimization Strategy Using Binary Chimp for View Materialization
DOI:
https://doi.org/10.22399/ijcesen.3774Keywords:
Materialized Views, Data Warehouse, Optimization, Binary Chimp Optimization, Query ProcessingAbstract
The Materialized View Selection (MVS) problem is a critical NP-complete challenge in data warehouse design, aimed at optimizing query performance while balancing storage and maintenance costs. This paper proposes BChOMVS, a novel metaheuristic approach that utilizes a Binary Chimp Optimization Algorithm (BChO) to efficiently identify near-optimal view sets. Using the Multiple View Processing Plan (MVPP) for view representation, BChOMVS encodes solutions as binary vectors and evaluates them with a comprehensive cost model. Rigorous experimental evaluation on the TPC-H benchmark up to 50GB demonstrates that BChOMVS significantly outperforms state-of-the-art methods like ACOMVS, PSOMVS, and GTMVS. It achieves superior total cost reduction by consistently selecting the smallest, most impactful view sets, establishing itself as the premier choice for large-scale data warehouse optimization where ultimate cost minimization is paramount.
References
[1] Chandra, P., & Gupta, M. K. (2018). Comprehensive survey on data warehousing research. International Journal of Information Technology. 10(2):217–224.
[2] Lechtenbörger, J., & Vossen, G. (2003). Multidimensional normal forms for data warehouse design. Information Systems. 28(5):415–434.
[3] Inmon, W. H. (2005). Building the data warehouse. Wiley.
[4] Dhote, C. A., & Ali, M. S. (2009). Materialized view selection in data warehousing: A survey. Journal of Applied Sciences. 9(3):401–414.
[5] Gupta, H., & Mumick, I. S. (1998). Selection of views to materialize under a maintenance cost constraint. In Proceedings of the International Conference on Data Engineering.
[6] Mami, I., & Bellahsene, Z. (2012). A survey of view selection methods. ACM SIGMOD Record. 41(1):20–29.
[7] Yang, J., Karlapalem, K., & Li, Q. (1997). Algorithms for materialized view design in data warehousing environment. In Proceedings of the VLDB Conference. 97:25–29.
[8] Roy, P., Seshadri, S., Sudarshan, S., & Bhobe, S. (2000). Efficient and extensible algorithms for multi query optimization. ACM SIGMOD Record. 29(2):249–260.
[9] Harinarayan, V., Rajaraman, A., & Ullman, J. D. (1996). Implementing data cubes efficiently. ACM SIGMOD Record. 25(2):205–216.
[10] Gosain, A., & Sachdeva, K. (2020). Materialized view selection for query performance enhancement using stochastic ranking based cuckoo search algorithm. International Journal of Reliability, Quality and Safety Engineering. 27(3):2050008.
[11] Gupta, H. (1997). Selection of views to materialize in a data warehouse. In Proceedings of the 6th International Conference of Data Theory. 98–112.
[12] Khishe, M., & Mosavi, M. R. (2020). Chimp optimization algorithm. Expert Systems with Applications. 149:113338. https://doi.org/10.1016/j.eswa.2020.113338
[13] Wang, J., Khishe, M., Kaveh, M., & Mohammadi, H. (2021). Binary chimp optimization algorithm (BChOA): a new binary meta-heuristic for solving optimization problems. Cognitive Computation. 13:1297-1316.
[14] Zhang, C., Yao, X., & Yang, J. (2001). An evolutionary approach to materialized views selection in a data warehouse environment. IEEE Transactions on Systems, Man and Cybernetics, Part C (Applications and Reviews). 31(3):282–294.
[15] Derakhshan, R., Stantic, B., Korn, O., & Dehne, F. (2008). Parallel simulated annealing for materialized view selection in data warehousing environments. In Algorithms and Architectures for Parallel Processing. 121–132.
[16] TPC-H data warehouse. http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-h_v2.17.1.pdf
[17] Derakhshan, R., Dehne, F. K., Korn, O., & Stantic, B. (2006). Simulated Annealing for Materialized View Selection in Data Warehousing Environment. In Databases and Applications. 89-94.
[18] Sun, X., & Wang, Z. (2009). An efficient materialized views selection algorithm based on PSO. In 2009 International Workshop on Intelligent Systems and Applications. 1-4.
[19] Song, X., & Gao, L. (2010). An ant colony based algorithm for optimal selection of materialized view. In 2010 International Conference on Intelligent Computing and Integrated Systems. 534-536.
[20] Azgomi, H., & Sohrabi, M. K. (2019). A novel coral reefs optimization algorithm for materialized view selection in data warehouse environments. Applied Intelligence. 49:3965-3989.
[21] Azgomi, H., & Sohrabi, M. K. (2018). A game theory based framework for materialized view selection in data warehouses. Engineering Applications of Artificial Intelligence. 71:125-137.
[22] Sohrabi, M. K., & Azgomi, H. (2019). Evolutionary game theory approach to materialized view selection in data warehouses. Knowledge-Based Systems. 163:558-571.
[23] Srinivasarao, P., & Satish, A. R. (2023). Multi‐objective materialized view selection using flamingo search optimization algorithm. Software: Practice and Experience. 53(4):988-1012.
[24] Jindal, A. et al. (2018). Machine learning for view recommendation. SIGMOD Record. 47(2):45-50.
[25] Zhou, J. et al. (2007). Dynamic materialized view management using reinforcement learning. IEEE Transactions on Knowledge and Data Engineering. 19(3):352-365.
[26] Baralis, E., Paraboschi, S., & Teniente, E. (1997). Materialized view selection in a multidimensional database. In Proceedings of the VLDB Conference. 97:156–165.
[27] Agrawal, S., Chaudhuri, S., & Narasayya, V. R. (2000). Automated selection of materialized views and indexes in SQL databases. In Proceedings of the VLDB Conference. 496–505.
[28] Shukla, A., Deshpande, P. M., & Naughton, J. F. (1996). Materialized view selection for multidimensional datasets. In Proceedings of the VLDB Conference. 96:488–499.
[29] Lin, C., et al. (2015). A new materialized view selection method based on query cost. Journal of Systems and Software. 110:16-28.
[30] Gorla, N. et al. (2007). PSO-based view selection in cloud warehouses. Journal of Cloud Computing. 6(1):12.
[31] Aouiche, K., Jouve, P. E., & Darmont, J. (2006). Clustering-based materialized view selection in data warehouses. In Advances in Databases and Information Systems. 81-95.
[32] Chen, Y., et al. (2022). Cost-aware view management for cloud-based analytics. Distributed and Parallel Databases. 40(4):789-815.
[33] Rahmani, A. M., et al. (2024). A hybrid heuristic for view selection in distributed data warehouses. Future Generation Computer Systems. 150:112-125.
[34] Chaudhuri, S., et al. (2019). Auto-administration of materialized views. In Proceedings of the CIDR Conference.
[35] Leis, V., et al. (2018). Adaptive query processing in the cloud. In Proceedings of the IEEE International Conference on Cloud Engineering. 142-151.
[36] Dageville, B., et al. (2021). Cloud-cost optimization of materialized views. Proceedings of the VLDB Endowment. 14(12):2829-2842.
[37] Mirjafari, S., et al. (2022). Adaptive materialization with deep RL. In 2022 IEEE 38th International Conference on Data Engineering (ICDE). 2850-2863.
[38] Wang, L., et al. (2023). GNN-based view benefit prediction. In Proceedings of the 29th ACM SIGKDD Conference on Knowledge Discovery and Data Mining. 4320-4330.
[39] Li, R., et al. (2024). DQN for streaming warehouses. In Proceedings of the 2024 ACM SIGMOD International Conference on Management of Data. 1245-1258.
[40] Kumar, A., & Singh, K. (2023). A multi-objective optimization framework for view selection using evolutionary strategies. Data & Knowledge Engineering. 144:102128.
[41] Feng, L., et al. (2024). Real-time view maintenance in large-scale data lakes. Information Sciences. 654:119876.
[42] Zhao, X., et al. (2024). Leveraging federated learning for privacy-preserving view recommendation. IEEE Transactions on Knowledge and Data Engineering.
[43] Yu, X., et al. (2020). Workload-aware view materialization for graph databases. World Wide Web. 23:2525–2547.
[44] Kannan, R., et al. (2023). Predictive view caching for low-latency analytics. In Proceedings of the ACM Symposium on Cloud Computing. 432-446.
[45] Liu, W., et al. (2024). An end-to-end deep reinforcement learning approach for autonomous view management. ACM Transactions on Database Systems. 49(1).
[46] Tang, Y., et al. (2024). Budget-constrained view selection using meta-heuristics in edge computing environments. Journal of Parallel and Distributed Computing. 183:104768.
[47] Teplukhin, M., et al. (2023). Optimizing materialized views for complex scientific queries. In Proceedings of the International Conference on Scientific and Statistical Database Management. 145-156.
Downloads
Published
How to Cite
Issue
Section
License
Copyright (c) 2025 International Journal of Computational and Experimental Science and Engineering
This work is licensed under a Creative Commons Attribution 4.0 International License.
