On Enhancing Query Optimization in the Oracle Database System by Utilizing Attribute Cardinality Maps

Abstract

Central to the process of query optimization in all real-life modern-day Database Management Systems (DBMS) is the use of histograms. These have been used for decades in approximating query result sizes in the query optimizer, and methods such as the Equi-Width and Equi-Depth histograms have been incorporated in all real-life systems. This is because histograms are simple structures, and can be easily utilized in determining efficient Query Evaluation Plans (QEPs). This paper demonstrates how we can incorporate two recently-developed histogram methods into the ORACLE real-life DBMS. These two new histograms methods were introduced by Oommen and Thiyagarajah [1], and called the the Rectangular Attribute Cardinality Map (R-ACM), and the Trapezoidal Attribute Cardinality Map (T-ACM).

The superiority of the R-ACM and the T-ACM in yielding more accurate query result size estimates has been well demonstrated, and the resulting superior QEPs for a theoretically-modeled database was shown in [2]. In this paper we make a “conceptual leap” and demonstrate how the ACMs can be incorporated into a real-life DBMS. This has been done by designing and implementing a prototype which sits on top of an ORACLE 9i system. The integration is achieved in C/C++ and PL/SQL, and serves as a prototype “plug-in” to the ORACLE system, since it is fully integrated and completely transparent to users. The superiority of utilizing the ACM histograms is rigorously validated by conducting an extensive set of experiments on the TPC-H benchmark data sets, and by testing on equi-select and equi-join queries. The entire set of experimental results obtained by integrating the underlying algorithms into the ORACLE query optimizer can be found in [3].