11 Histograms

This scenario shows how to generate a frequency histogram using the sample schemas.

Assumptions

This scenario assumes that you want to generate a frequency histogram on the sh.countries.country_subregion_id column. This table has 23 rows.

The following query shows that the country_subregion_id column contains 8 distinct values (sample output included) that are unevenly distributed:

SELECT country_subregion_id, count(*)
FROM   sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
 
COUNTRY_SUBREGION_ID   COUNT(*)
-------------------- ----------
               52792          1
               52793          5
               52794          2
               52795          1
               52796          1
               52797          2
               52798          2
               52799          9

To generate a frequency histogram:

1. Gather statistics for sh.countries and the country_subregion_id column, letting the number of buckets default to 254.

   For example, execute the following PL/SQL anonymous block:

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS ( 
       ownname    => 'SH'
   ,   tabname    => 'COUNTRIES'
   ,   method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID'
   );
   END;
   

2. Query the histogram information for the country_subregion_id column.

   For example, use the following query (sample output included):

   SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
   FROM   USER_TAB_COL_STATISTICS
   WHERE  TABLE_NAME='COUNTRIES'
   AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
    
   TABLE_NAME COLUMN_NAME          NUM_DISTINCT HISTOGRAM
   ---------- -------------------- ------------ ---------------
   COUNTRIES  COUNTRY_SUBREGION_ID            8 FREQUENCY
   

   The optimizer chooses a frequency histogram because n or fewer distinct values exist in the column, where n defaults to 254.

3. Query the endpoint number and endpoint value for the country_subregion_id column.

   For example, use the following query (sample output included):

   SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
   FROM   USER_HISTOGRAMS
   WHERE  TABLE_NAME='COUNTRIES'
   AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
    
   ENDPOINT_NUMBER ENDPOINT_VALUE
   --------------- --------------
                 1          52792
                 6          52793
                 8          52794
                 9          52795
                10          52796
                12          52797
                14          52798
                23          52799
   

   Figure 11-2 is a graphical illustration of the 8 buckets in the histogram. Each value is represented as a coin that is dropped into a bucket.

   Figure 11-2 Frequency Histogram

   
   Description of "Figure 11-2 Frequency Histogram"

   As shown in Figure 11-2, each distinct value has its own bucket. Because this is a frequency histogram, the endpoint number is the cumulative frequency of endpoints. For 52793, the endpoint number 6 indicates that the value appears 5 times (6 - 1). For 52794, the endpoint number 8 indicates that the value appears 2 times (8 - 6).

   Every bucket whose endpoint is at least 2 greater than the previous endpoint contains a popular value. Thus, buckets 6, 8, 12, 14, and 23 contain popular values. The optimizer calculates their cardinality based on endpoint numbers. For example, the optimizer calculates the cardinality (C) of value 52799 using the following formula, where the number of rows in the table is 23:

   C = 23 * ( 9 / 23 )
   

   Buckets 1, 9, and 10 contain nonpopular values. The optimizer estimates their cardinality based on density.

This scenario shows how to generate a top frequency histogram using the sample schemas.

Assumptions

This scenario assumes that you want to generate a top frequency histogram on the sh.countries.country_subregion_id column. This table has 23 rows.

The following query shows that the country_subregion_id column contains 8 distinct values (sample output included) that are unevenly distributed:

SELECT country_subregion_id, count(*)
FROM   sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
 
COUNTRY_SUBREGION_ID   COUNT(*)
-------------------- ----------
               52792          1
               52793          5
               52794          2
               52795          1
               52796          1
               52797          2
               52798          2
               52799          9

To generate a top frequency histogram:

1. Gather statistics for sh.countries and the country_subregion_id column, specifying fewer buckets than distinct values.

   For example, enter the following command to specify 7 buckets:

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS (
       ownname    => 'SH'
   ,   tabname    => 'COUNTRIES'
   ,   method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID SIZE 7'
   );
   END;
   

2. Query the histogram information for the country_subregion_id column.

   For example, use the following query (sample output included):

   SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
   FROM   USER_TAB_COL_STATISTICS
   WHERE  TABLE_NAME='COUNTRIES'
   AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
    
   TABLE_NAME COLUMN_NAME          NUM_DISTINCT HISTOGRAM
   ---------- -------------------- ------------ ---------------
   COUNTRIES  COUNTRY_SUBREGION_ID            7 TOP-FREQUENCY
   

   The sh.countries.country_subregion_id column contains 8 distinct values, but the histogram only contains 7 buckets, making n=7. In this case, the database can only create a top frequency or hybrid histogram. In the country_subregion_id column, the top 7 most frequent values occupy 95.6% of the rows, which exceeds the threshold of 85.7%, generating a top frequency histogram (see "Criteria For Frequency Histograms").

3. Query the endpoint number and endpoint value for the column.

   For example, use the following query (sample output included):

   SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
   FROM   USER_HISTOGRAMS
   WHERE  TABLE_NAME='COUNTRIES'
   AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
    
   ENDPOINT_NUMBER ENDPOINT_VALUE
   --------------- --------------
                 1          52792
                 6          52793
                 8          52794
                 9          52796
                11          52797
                13          52798
                22          52799
   

   Figure 11-3 is a graphical illustration of the 7 buckets in the top frequency histogram. The values are represented in the diagram as coins.

   Figure 11-3 Top Frequency Histogram

   
   Description of "Figure 11-3 Top Frequency Histogram"

   As shown in Figure 11-3, each distinct value has its own bucket except for 52795, which is excluded from the histogram because it is nonpopular and statistically insignificant. As in a standard frequency histogram, the endpoint number represents the cumulative frequency of values.

This scenario shows how to generate a height-balanced histogram using the sample schemas.

Assumptions

This scenario assumes that you want to generate a height-balanced histogram on the sh.countries.country_subregion_id column. This table has 23 rows.

The following query shows that the country_subregion_id column contains 8 distinct values (sample output included) that are unevenly distributed:

SELECT country_subregion_id, count(*)
FROM   sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
 
COUNTRY_SUBREGION_ID   COUNT(*)
-------------------- ----------
               52792          1
               52793          5
               52794          2
               52795          1
               52796          1
               52797          2
               52798          2
               52799          9

To generate a height-balanced histogram:

1. Gather statistics for sh.countries and the country_subregion_id column, specifying fewer buckets than distinct values.

   Note:

   To simulate Oracle Database 11g behavior, which is necessary to create a height-based histogram, set estimate_percent to a nondefault value. If you specify a nondefault percentage, then the database creates frequency or height-balanced histograms.

   For example, enter the following command:

   BEGIN  DBMS_STATS.GATHER_TABLE_STATS ( 
       ownname          => 'SH'
   ,   tabname          => 'COUNTRIES'
   ,   method_opt       => 'FOR COLUMNS COUNTRY_SUBREGION_ID SIZE 7'
   ,   estimate_percent => 100 
   );
   END;
   

2. Query the histogram information for the country_subregion_id column.

   For example, use the following query (sample output included):

   SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
   FROM   USER_TAB_COL_STATISTICS
   WHERE  TABLE_NAME='COUNTRIES'
   AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
    
   TABLE_NAME COLUMN_NAME          NUM_DISTINCT HISTOGRAM
   ---------- -------------------- ------------ ---------------
   COUNTRIES  COUNTRY_SUBREGION_ID            8 HEIGHT BALANCED
   

   The optimizer chooses a height-balanced histogram because the number of distinct values (8) is greater than the number of buckets (7), and the estimate_percent value is nondefault.

3. Query the number of rows occupied by each distinct value.

   For example, use the following query (sample output included):

   SELECT COUNT(country_subregion_id) AS NUM_OF_ROWS, country_subregion_id 
   FROM   countries 
   GROUP BY country_subregion_id 
   ORDER BY 2;
    
   NUM_OF_ROWS COUNTRY_SUBREGION_ID
   ----------- --------------------
             1                52792
             5                52793
             2                52794
             1                52795
             1                52796
             2                52797
             2                52798
             9                52799
   

4. Query the endpoint number and endpoint value for the country_subregion_id column.

   For example, use the following query (sample output included):

   SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
   FROM   USER_HISTOGRAMS
   WHERE  TABLE_NAME='COUNTRIES'
   AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
    
   ENDPOINT_NUMBER ENDPOINT_VALUE
   --------------- --------------
                 0          52792
                 2          52793
                 3          52795
                 4          52798
                 7          52799
   

   Figure 11-4 is a graphical illustration of the height-balanced histogram. The values are represented in the diagram as coins.

   Figure 11-4 Height-Balanced Histogram

   
   Description of "Figure 11-4 Height-Balanced Histogram"

   The bucket number is identical to the endpoint number. The optimizer records the value of the last row in each bucket as the endpoint value, and then checks to ensure that the minimum value is the endpoint value of the first bucket, and the maximum value is the endpoint value of the last bucket. In this example, the optimizer adds bucket 0 so that the minimum value 52792 is the endpoint of a bucket.

   The optimizer must evenly distribute 23 rows into the 7 specified histogram buckets, so each bucket contains approximately 3 rows. However, the optimizer compresses buckets with the same endpoint. So, instead of bucket 1 containing 2 instances of value 52793, and bucket 2 containing 3 instances of value 52793, the optimizer puts all 5 instances of value 52793 into bucket 2. Similarly, instead of having buckets 5, 6, and 7 contain 3 values each, with the endpoint of each bucket as 52799, the optimizer puts all 9 instances of value 52799 into bucket 7.

   In this example, buckets 3 and 4 contain nonpopular values because the difference between the current endpoint number and previous endpoint number is 1. The optimizer calculates cardinality for these values based on density. The remaining buckets contain popular values. The optimizer calculates cardinality for these values based on endpoint numbers.

This scenario shows how to generate a hybrid histogram using the sample schemas.

Assumptions

This scenario assumes that you want to generate a hybrid histogram on the sh.products.prod_subcategory_id column. This table has 72 rows. The prod_subcategory_id column contains 22 distinct values.

To generate a hybrid histogram:

1. Gather statistics for sh.products and the prod_subcategory_id column, specifying 10 buckets.

   For example, enter the following command:

   BEGIN  DBMS_STATS.GATHER_TABLE_STATS ( 
       ownname     => 'SH'
   ,   tabname     => 'PRODUCTS'
   ,   method_opt  => 'FOR COLUMNS PROD_SUBCATEGORY_ID SIZE 10'
   );
   END;
   

2. Query the number of rows occupied by each distinct value.

   For example, use the following query (sample output included):

   SELECT COUNT(prod_subcategory_id) AS NUM_OF_ROWS, prod_subcategory_id
   FROM   products
   GROUP BY prod_subcategory_id
   ORDER BY 1 DESC;
    
   NUM_OF_ROWS PROD_SUBCATEGORY_ID
   ----------- -------------------
             8                2014
             7                2055
             6                2032
             6                2054
             5                2056
             5                2031
             5                2042
             5                2051
             4                2036
             3                2043
             2                2033
             2                2034
             2                2013
             2                2012
             2                2053
             2                2035
             1                2022
             1                2041
             1                2044
             1                2011
             1                2021
             1                2052
    
   22 rows selected.
   

   The column contains 22 distinct values. Because the number of buckets (10) is less than 22, the optimizer cannot create a frequency histogram. The optimizer considers both hybrid and top frequency histograms. To qualify for a top frequency histogram, the percentage of rows occupied by the top 10 most frequent values must be equal to or greater than threshold p, where p is (1-(1/10))*100, or 90%. However, in this case the top 10 most frequent values occupy 54 rows out of 72, which is only 75% of the total. Therefore, the optimizer chooses a hybrid histogram because the criteria for a top frequency histogram do not apply.

3. Query the histogram information for the country_subregion_id column.

   For example, use the following query (sample output included):

   SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
   FROM   USER_TAB_COL_STATISTICS
   WHERE  TABLE_NAME='PRODUCTS'
   AND    COLUMN_NAME='PROD_SUBCATEGORY_ID';
   
   TABLE_NAME COLUMN_NAME         NUM_DISTINCT HISTOGRAM
   ---------- ------------------- ------------ ---------
   PRODUCTS   PROD_SUBCATEGORY_ID 22           HYBRID
   

4. Query the endpoint number, endpoint value, and endpoint repeat count for the country_subregion_id column.

   For example, use the following query (sample output included):

   SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_REPEAT_COUNT
   FROM   USER_HISTOGRAMS
   WHERE  TABLE_NAME='PRODUCTS'
   AND    COLUMN_NAME='PROD_SUBCATEGORY_ID'
   ORDER BY 1;
    
   ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_REPEAT_COUNT
   --------------- -------------- ---------------------
                 1           2011                     1
                13           2014                     8
                26           2032                     6
                36           2036                     4
                45           2043                     3
                51           2051                     5
                52           2052                     1
                54           2053                     2
                60           2054                     6
                72           2056                     5
    
   10 rows selected.
   

   In a height-based histogram, the optimizer would evenly distribute 72 rows into the 10 specified histogram buckets, so that each bucket contains approximately 7 rows. Because this is a hybrid histogram, the optimizer distributes the values so that no value occupies more than one bucket. For example, the optimizer does not put some instances of value 2036 into one bucket and some instances of this value into another bucket: all instances are in bucket 36.

   The endpoint repeat count shows the number of times the highest value in the bucket is repeated. By using the endpoint number and repeat count for these values, the optimizer can estimate cardinality. For example, bucket 36 contains instances of values 2033, 2034, 2035, and 2036. The endpoint value 2036 has an endpoint repeat count of 4, so the optimizer knows that 4 instances of this value exist. For values such as 2033, which are not endpoints, the optimizer estimates cardinality using density.