HISTOGRAMS IN ORACLE 12C DATABASE

Optimizer statistics are a collection of data that describe the database and the objects in it. The optimizer uses these statistics to choose the best execution plan for each SQL statement. Being able to gather the appropriate statistics in a timely manner is critical to maintaining acceptable performance on any Oracle system. With each new release, Oracle strives to provide the necessary statistics automatically and improve their precision.Histograms complement the available table statistics, providing detailed insight into the distribution of column data. By default, the optimizer assumes a uniform distribution of rows across the distinct values in a column and will calculate the cardinality for a query with an equality predicate by dividing the total number of rows in the table by the number of distinct values in the column used in the equality predicate. The presence of a histogram changes the formula used by the optimizer to determine the cardinality estimate, and allows it to generate a more accurate estimate. Prior to Oracle Database 12c, there were two types of histograms, frequency and height balance. Two additional types of histogram are now available: top-frequency and hybrid.This paper provides an overview of new types of histograms in Oracle 12c as well as examples on how a specific type of histogram help Oracle's cost-based optimizer to make better decision and improve performance.