O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required