Oracle Database 11gR2 Performance Tuning Cookbook

Using histograms

In this recipe, we will see how to use histograms on tables to provide a detailed estimate of value distribution inside a column.

How to do it...

The following steps will show how to represent our data in the form of histograms:

1. Connect to SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create the table TEST_HIST with some data from ALL_OBJECTS:

   CREATE TABLE sh.TEST_HIST AS
     SELECT
       ROWNUM AS ID,
       OBJECT_NAME AS NAME,
       MOD(ROWNUM, 10) AS FIELD1,
       TRUNC(MOD(ROWNUM, 10)/9) AS FIELD2
     FROM ALL_OBJECTS;
   

3. Query for FIELD1 and FIELD2 values grouped to see the data distribution:

   SELECT FIELD1, COUNT(*)
   FROM TEST_HIST
   GROUP BY FIELD1 ORDER BY 1;
   
   SELECT FIELD2, COUNT(*)
   FROM TEST_HIST
   GROUP BY FIELD2 ORDER BY 1;
   

4. Create histograms for column FIELD1 of the ...