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: ...

Get Oracle Database 11gR2 Performance Tuning Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.