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

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).

[1]  David G. Stork,et al.  Pattern Classification , 1973 .

[2]  B. John Oommen,et al.  Benchmarking attribute cardinality maps for database systems using the TPC-D specifications , 2003, IEEE Trans. Syst. Man Cybern. Part B.

[3]  Sudipto Guha,et al.  Dynamic multidimensional histograms , 2002, SIGMOD '02.

[4]  B. John Oommen,et al.  The Efficiency of Histogram-like Techniques for Database Query Optimization , 2002, Comput. J..

[5]  Keinosuke Fukunaga,et al.  Introduction to Statistical Pattern Recognition , 1972 .

[6]  David G. Stork,et al.  Pattern Classification (2nd ed.) , 1999 .

[7]  Yannis E. Ioannidis,et al.  Histogram-Based Approximation of Set-Valued Query-Answers , 1999, VLDB.

[8]  B. John Oommen,et al.  A new histogram method for sparse attributes: the averaged rectangular attribute cardinality map , 2003, ISICT.

[9]  Michael V. Mannino,et al.  Statistical profile estimation in database systems , 1988, CSUR.

[10]  Jeffrey Scott Vitter,et al.  Approximate computation of multidimensional aggregates of sparse data using wavelets , 1999, SIGMOD '99.

[11]  B. John Oommen,et al.  Query result size estimation using the Trapezoidal Attribute Cardinality Map , 2000, Proceedings 2000 International Database Engineering and Applications Symposium (Cat. No.PR00789).

[12]  Jeffrey F. Naughton,et al.  Sampling-Based Estimation of the Number of Distinct Values of an Attribute , 1995, VLDB.

[13]  Gregory Piatetsky-Shapiro,et al.  Accurate estimation of the number of tuples satisfying a condition , 1984, SIGMOD '84.

[14]  Peter J. Haas,et al.  Improved histograms for selectivity estimation of range predicates , 1996, SIGMOD '96.

[15]  Peter J. Haas,et al.  Sequential sampling procedures for query size estimation , 1992, SIGMOD '92.

[16]  Yannis E. Ioannidis,et al.  Balancing histogram optimality and practicality for query result size estimation , 1995, SIGMOD '95.

[17]  B. John Oommen,et al.  Attribute cardinality maps: new query result size estimation techniques for database systems , 1999 .

[18]  B. John Oommen,et al.  Query result size estimation using a novel histogram-like technique: the rectangular attribute cardinality map , 1999, Proceedings. IDEAS'99. International Database Engineering and Applications Symposium (Cat. No.PR00265).

[19]  Stavros Christodoulakis,et al.  Optimal histograms for limiting worst-case error propagation in the size of join results , 1993, TODS.

[20]  Matthias Jarke,et al.  Query Optimization in Database Systems , 1984, CSUR.