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 table TEST_HIST:

   EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'SH', -
     TABNAME => 'TEST_HIST', -
     METHOD_OPT => 'FOR COLUMNS SIZE 10 FIELD1');
   

5. Query USER_TAB_HISTOGRAMS to see the values stored in the histogram for FIELD1...