Histogram

From Oracle FAQ
Jump to: navigation, search

A histogram is a frequency distribution (metadata) that describes the distribution of data values within a table.

Query optimizer[edit]

The Oracle Query Optimizer uses histograms to predict better query plans. The ANALYZE command or DBMS_STATS package can be used to compute these histograms.

SQL to generate histograms[edit]

In the below example, the value returned by COUNT(*) is used to control the number of "+" characters to return for each department. We simply pass COUNT(*) as an argument to the string function LPAD (or RPAD) to return the desired number of +'s.

SELECT d.dname AS "Department",
             LPAD('+', COUNT(*), '+') as "Histogram graph"
  FROM emp e, dept d
 WHERE e.deptno = d.deptno
 GROUP BY d.dname;

Sample output:

Department     Histogram graph
-------------- ---------------
ACCOUNTING     +++
RESEARCH       +++++
SALES          ++++++
Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #