Understanding Histograms in the Oracle Database

[Pages:28]Understanding Histograms in the Oracle Database

Robert Gaydos R Wave Solutions

Robert.Gaydos@

Copyright ? R Wave Solutions 2008 All rights reserved

Addenda

What is a Histogram? Types of Histograms How to create Histograms How to identify existing Histograms Rules and tips when creating Histograms Q & A

Copyright ? R Wave Solutions 2008 All rights reserved

What is a Histogram?

A histogram holds the data distribution of values within a column of a table.

? Number of occurrences for a specific value/range ? Used by CBO to optimize a query.

It is collected by using DBMS_STATS.

? By default DBMS_STATS does not collect histogram stats.

Min/Max Value and # of distinct values NOTE: DBMS_STAT is used to delete histogram data.

Copyright ? R Wave Solutions 2008 All rights reserved

Types of Histograms

Two types of Histograms

? frequency histograms ? height-balanced histograms ? Type of histogram is stored in the HISTOGRAM

column of the *tab_col_statistics views (USER/DBA) ? Value = (`HEIGHT BALANCED', `FREQUENCY',

or `NONE')

Copyright ? R Wave Solutions 2008 All rights reserved

Histogram and Buckets

When Histograms are created the number of buckets can be specified. It is this number that controls the type of histogram created. # Buckets = # of Rows of information. When Distinct Values ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download