Review of Query Optimization Techniques in Database Management Systems

Abstract

In database management systems (DBMSs), query optimization is a frequent practice. Its primary goal is to maximize query execution efficiency among many possibilities. In recent years, the world of data-driven applications has grown rapidly, and effective query processing is an essential element of high-performance systems and efficient resource use in cloud computing environments. In this paper, the authors review key query optimization strategies used in state-of-the-art DBMSs. These include heuristic-based optimization, cost-based optimization, semantic-based optimization for both join and indexing strategies and sub-query rewriting methods. The study contextually explores new AI/ML-based optimization techniques that contribute towards enhancing adaptive query execution and automated tuning in the dynamic environment. Apart from this, the paper also discusses the role of query execution plans, cost models and optimization frameworks and how they can improve the performance of databases in enterprise systems, databases in a distributed environment of the cloud and big data analytics platforms. Using a systematic literature review of recent relevant studies, researchers identify current progress and challenges in query optimization alongside possible future research directions. The paper shows that in the context of many complex (possibly correlated) workloads, modern database systems need some degree of intelligent/elastic behaviour to operate with bounded latency and resource consumption.