Oracle Histograms
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Histogram Types Data skew in a column can make it difficult for the optimizer to accurately estimate the cardinality of an operation. Without a histogram it will assume an even distribution amongst the distinct values for the column. Take the case of a Yes/No flag for active records in a table. There may be 1 million rows in the table with only 100 being marked as active. The optimizer would assume half are marked as "Yes" and half as "No", which would be a really bad assumption in this case. Histograms describe the distribution of data in a column, which helps identify data skew and allows the optimizer to make a better decision.

Oracle uses four types of histograms to assist the optimizer in better understanding the nature of the data. All histograms data is stored in the HISTOGRAM column of the [CDB_, DBA_, ALL_, and USER_] TAB_COL_STATISTICS views.
Creation Histograms are most commonly created by the DBMS_STATS built-in package when it runs but examining predicates logged in the sys.col_usage$ table. When stats collection next runs this table is checked to see if histogram creation will have a positive impact on future access.

Non default parameters supplied to the METHOD_OPT parameter can be used to manually set the column(s) for histogram creation and the desired bucket size.
Histogram Note As far as histograms go, the best strategy is to have none by default, and only create specific histograms when you are sure you need them.
~ Jonathan Lewis, 31-Jan-2010 in the OTN Database-General forum.
Dependencies
ALL_HISTOGRAMS CDB_SUBPART_HISTOGRAMS DBA_TAB_HISTOGRAMS
ALL_PART_HISTOGRAMS CDB_TAB_HISTOGRAMS USER_HISTOGRAMS
ALL_SUBPART_HISTOGRAMS DBA_HISTOGRAMS USER_PART_HISTOGRAMS
ALL_TAB_HISTOGRAMS DBA_PART_HISTOGRAMS USER_USBPART_HISTOGRAMS
CDB_HISTOGRAMS DBA_SUBPART_HISTOGRAMS USER_TAB_HISTOGRAMS
CDB_PART_HISTOGRAMS    
 
Frequency Balanced
Description In a frequency histogram, each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of that single value. Frequency histograms are automatically created instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified (the number of buckets defaults to 254 if not otherwise specified). Frequency histograms can be viewed using the *TAB_HISTOGRAMS views.
Demo Table, Indexes and Data CREATE TABLE uwclass.freqbal(
rec_id   NUMBER(6),
rec_type NUMBER(5),
rec_desc VARCHAR2(30));

ALTER TABLE uwclass.freqbal
ADD CONSTRAINT pk_freqbal
PRIMARY KEY (rec_id);

CREATE INDEX ix_freqbal_rec_type
ON uwclass.freqbal(rec_type);

INSERT INTO uwclass.freqbal
SELECT level AS recid,
  CASE WHEN MOD(level, 2) = 0 THEN 0
       ELSE TRUNC(dbms_random.value(1,10))
  END AS rec_type, dbms_random.string('x', 30)
FROM dual
CONNECT BY level <= 10000;
COMMIT;

SELECT rec_type, COUNT(*)
FROM uwclass.freqbal
GROUP BY rec_type
ORDER BY 1;

 REC_TYPE  COUNT(*)
--------- --------
        0     5000  -- note the skew and explain plan estimates
        1      562
        2      542
        3      556
        4      568
        5      574
        6      552
        7      535
        8      548
        9      563


EXPLAIN PLAN FOR
SELECT rec_desc
FROM freqbal
WHERE rec_type = 0;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------------------------
| Id | Operation                           | Name                | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                     | 4371 |  128K |    15   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| FREQBAL             | 4371 |  128K |    15   (0)|
|* 2 |   INDEX RANGE SCAN                  | IX_FREQBAL_REC_TYPE | 4371 |       |    16   (0)|
----------------------------------------------------------------------------------------------
Manual Histogram Creation col column_name format a20

SELECT column_id, column_name, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'FREQBAL'
ORDER BY 1;

 COLUMN_ID COLUMN_NAME  HISTOGRAM
---------- ------------ ---------------
         1 REC_ID       NONE
         2 REC_TYPE     NONE
         3 REC_DESC     NONE

exec dbms_stats.gather_table_stats('UWCLASS', 'FREQBAL', METHOD_OPT => 'FOR COLUMNS SIZE 10 rec_type');

PL/SQL procedure successfully completed.

SELECT column_id, column_name, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'FREQBAL'
ORDER BY 1;

 COLUMN_ID COLUMN_NAME  HISTOGRAM
---------- ------------ ---------------
         1 ID           NONE
         2 RECORD_TYPE  FREQUENCY
         3 DESCRIPTION  NONE
View Histogram Data CREATE OR REPLACE FUNCTION raw2num(rawval IN RAW) RETURN NUMBER AUTHID DEFINER IS
 n NUMBER;
BEGIN
  dbms_stats.convert_raw_value(rawval, n);
  RETURN n;
END raw2num;
/

Function created.

SELECT column_name, num_distinct, raw2num(low_value) LV, raw2num(high_value) HV,
density, num_nulls, avg_col_len, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'FREQBAL';

COLUMN_NAME NUM_DISTINCT  LV  HV    DENSITY  NUM_NULLS  AVG_COL_LEN HISTOGRAM
----------- ------------ --- --- ---------- ---------- ------------ ---------------
REC_ID                                                              NONE
REC_TYPE              10   0   9 .000050051          0            3 FREQUENCY
REC_DESC                                                            NONE


SELECT endpoint_value, endpoint_number, (endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value)) AS frequency
FROM dba_tab_histograms
WHERE owner = 'UWCLASS'
AND table_name = 'FREQBAL'
AND column_name = 'REC_TYPE'
ORDER BY 1;

ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ---------
             0            2374      2374
             1            2634       260
             2            2901       267
             3            3161       260
             4            3398       237
             5            3677       279
             6            3946       269
             7            4203       257
             8            4466       263
             9            4730       264


EXPLAIN PLAN FOR
SELECT rec_desc
FROM freqbal
WHERE rec_type = 0;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------
| Id | Operation          | Name    | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |         | 5051 |    98K|   25    (0)| 00:00:01 |
|* 1 |  TABLE ACCESS FULL | FREQBAL | 5051 |    98K|   25    (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Height Balanced
Description In Oracle database 12c and above, height-balanced histograms are created only if dynamic sampling is used during statistics collection. When explicit sampling does not take place the database performs a full table scan and creates a top frequency or hybrid histogram (both covered below).
Demo Table, Indexes and Data CREATE TABLE uwclass.htbal(
rec_id   NUMBER(6),
rec_type NUMBER(5),
rec_desc VARCHAR2(30));

ALTER TABLE uwclass.htbal
ADD CONSTRAINT pk_htbal
PRIMARY KEY (rec_id);

CREATE INDEX ix_htbal_rec_type
ON uwclass.htbal(rec_type);

INSERT INTO uwclass.htbal
SELECT level AS recid,
  CASE WHEN MOD(level, 2) = 0 THEN 0
       ELSE level
  END AS rec_type, dbms_random.string('x', 30)
FROM dual
CONNECT BY level <= 10000;
COMMIT;

SELECT COUNT(*)
FROM uwclass.htbal;

 COUNT(*)
---------
    10000


SELECT rec_type, COUNT(*)
FROM uwclass.htbal
GROUP BY rec_type
HAVING COUNT(*) > 1
ORDER BY 1;

 REC_TYPE  COUNT(*)
--------- --------
        0     5000


EXPLAIN PLAN FOR
SELECT rec_desc
FROM htbal
WHERE rec_type = 0;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------------------------
| Id | Operation                           | Name              | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                   | 4813 |  141K |    14   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| HTBAL             | 4813 |  141K |    14   (0)|
|* 2 |   INDEX RANGE SCAN                  | IX_HTBAL_REC_TYPE | 4813 |       |    14   (0)|
--------------------------------------------------------------------------------------------
Manual Histogram Creation col column_name format a20
col range format a20

SELECT column_id, column_name, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'HTBAL'
ORDER BY 1;

 COLUMN_ID COLUMN_NAME  HISTOGRAM
---------- ------------ ---------------
         1 REC_ID       NONE
         2 REC_TYPE     NONE
         3 REC_DESC     NONE


exec dbms_stats.gather_table_stats('UWCLASS', 'HTBAL', ESTIMATE_PERCENT => 10);

SELECT column_id, column_name, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'HTBAL'
ORDER BY 1;

 COLUMN_ID COLUMN_NAME  HISTOGRAM
---------- ------------ ---------------
         1 REC_ID       NONE
         2 REC_TYPE     HEIGHT BALANCED
         3 REC_DESC     NONE
View Histogram Data CREATE OR REPLACE FUNCTION raw2num(rawval IN RAW) RETURN NUMBER AUTHID DEFINER IS
 n NUMBER;
BEGIN
  dbms_stats.convert_raw_value(rawval, n);
  RETURN n;
END raw2num;
/

SELECT column_name, num_distinct, raw2num(low_value) LV, raw2num(high_value) HV,
density, num_nulls, avg_col_len, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'HTBAL';

COLUMN_NAME NUM_DISTINCT         LV         HV    DENSITY NUM_NULLS AVG_COL_LEN HISTOGRAM
----------- ------------ ---------- ---------- ---------- --------- ----------- ---------------
REC_ID              9864          9       9996 .000099602         0           4 NONE
REC_TYPE            4832          0       9997 .000098752         0           4 HEIGHT BALANCED
REC_DESC            9864 -5.133E+29 -1.227E-55 .000097059         0          31 NONE


SELECT endpoint_value, endpoint_number, (endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value)) AS frequency
FROM dba_tab_histograms
WHERE owner = 'UWCLASS'
AND table_name = 'HTBAL'
AND column_name = 'REC_TYPE'
AND rownum < 11
ORDER BY 1;

ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ---------
             0             125       125
            13             126         1
            89             127         1
           173             128         1
           245             129         1
           333             130         1
           409             131         1
           489             132         1
           555             133         1
           623             134         1


EXPLAIN PLAN FOR
SELECT rec_desc
FROM htbal
WHERE rec_type = 0;

SELECT * FROM TABLE(dbms_xplan.display);

---------------------------------------------------------------
| Id | Operation          | Name  | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|  0 | SELECT STATEMENT   |       | 4854 |  165K |    25   (0)|
|* 1 |  TABLE ACCESS FULL | HTBAL | 4854 |  165K |    25   (0)|
----------------------------------------------------------------
 
Top Frequency
Description Top-frequency histograms are a frequency histogram variant. Frequency histograms target common values, ignoring the less common values as statistically insignificant.

Top-frequency histograms have maximum value when the number of distinct most common values is less than or equal to the number of histogram buckets, while the number of less common values are significantly less common in comparison to the most common values.
Demo Table, Indexes and Data dbms_stats.gather_table_stats(<schema_name>, <table_name>, METHOD_OPT => 'FOR COLUMNS <column_name> SIZE <integer>');
CREATE TABLE topfreq(
rec_id   NUMBER(6),
rec_type NUMBER(5),
rec_desc VARCHAR2(30));

ALTER TABLE topfreq
ADD CONSTRAINT pktopfreq
PRIMARY KEY (rec_id);

CREATE INDEX ix_topfreq_rec_type
ON topfreq(rec_type);

INSERT INTO topfreq
SELECT level AS recid,
  CASE WHEN level <= 9990 THEN TRUNC(dbms_random.value(1,10))
       ELSE level
  END AS rec_type, dbms_random.string('x', 30)
FROM dual
CONNECT BY level <= 10000;

COMMIT;

SELECT rec_type, COUNT(*)
FROM uwclass.topfreq
GROUP BY rec_type
ORDER BY 1;

 REC_TYPE  COUNT(*)
--------- ---------
        1      1139
        2      1075
        3      1107
        4      1148
        5      1075
        6      1145
        7      1091
        8      1127
        9      1083
     9991         1
     9992         1
     9993         1
     9994         1
     9995         1
     9996         1
     9997         1
     9998         1
     9999         1
    10000         1


EXPLAIN PLAN FOR
SELECT rec_desc
FROM uwclass.topfreq
WHERE rec_type = 2;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------------------------
| Id | Operation                           | Name                | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                     | 1075 | 32250 |     6   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TOPFREQ             | 1075 | 32250 |     6   (0)|
|* 2 |   INDEX RANGE SCAN                  | IX_TOPFREQ_REC_TYPE | 1075 |       |     6   (0)|
----------------------------------------------------------------------------------------------


EXPLAIN PLAN FOR
SELECT rec_desc
FROM uwclass.topfreq
WHERE rec_type = 10000;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------------------------
| Id | Operation                           | Name                | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                     |    1 |    30 |     1   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TOPFREQ             |    1 |    30 |     1   (0)|
|* 2 |   INDEX RANGE SCAN                  | IX_TOPFREQ_REC_TYPE |    1 |       |     1   (0)|
----------------------------------------------------------------------------------------------
Manual Histogram Creation col column_name format a20
col range format a20

SELECT column_id, column_name, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'TOPFREQ'
ORDER BY 1;

 COLUMN_ID COLUMN_NAME  HISTOGRAM
---------- ------------ ---------------
         1 REC_ID       NONE
         2 REC_TYPE     NONE
         3 REC_DES      NONE


exec dbms_stats.gather_table_stats('UWCLASS', 'TOPFREQ', METHOD_OPT => 'FOR COLUMNS rec_type SIZE 10');

SELECT column_id, column_name, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'TOPFREQ'
ORDER BY 1;

 COLUMN_ID COLUMN_NAME  HISTOGRAM
---------- ------------ ---------------
         1 REC_ID       NONE
         2 REC_TYPE     TOP-FREQUENCY
         3 REC_DES      NONE


SELECT (LAG(endpoint_value, 1, 0) OVER (ORDER BY endpoint_value)+1) || '-' || endpoint_value AS RANGE,
endpoint_value - (LAG(endpoint_value, 1, 0) OVER (ORDER BY endpoint_value)+1) + 1 AS VALS_IN_RNG,
endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value) AS FREQUENCY
FROM dba_tab_histograms
WHERE owner = 'UWCLASS'
AND table_name = 'TOPFREQ'
AND column_name = 'REC_TYPE'
ORDER BY endpoint_value;

RANGE       VALS_IN_RNG  FREQUENCY
---------- ------------ ----------
1-1                   1       1139
2-2                   1       1075
3-3                   1       1107
4-4                   1       1148
5-5                   1       1075
6-6                   1       1145
7-7                   1       1091
8-8                   1       1127
9-9                   1       1083
10-10000           9991          1
View Histogram Data CREATE OR REPLACE FUNCTION raw2num(rawval IN RAW) RETURN NUMBER AUTHID DEFINER IS
 n NUMBER;
BEGIN
  dbms_stats.convert_raw_value(rawval, n);
  RETURN n;
END raw2num;
/

SELECT column_name, num_distinct, raw2num(low_value) LV, raw2num(high_value) HV,
density, num_nulls, avg_col_len, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'TOPFREQ';

COLUMN_NAME  NUM_DISTINCT  LV     HV  DENSITY  NUM_NULLS AVG_COL_LEN HISTOGRAM
------------ ------------ --- ------ -------- ---------- ----------- ----------
REC_ID                                                               NONE
REC_TYPE               19   1  10000   .00005          0           4 TOP-FREQUENCY
REC_DESC                                                             NONE


SELECT endpoint_value, endpoint_number, (endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value)) AS frequency
FROM dba_tab_histograms
WHERE owner = 'UWCLASS'
AND table_name = 'TOPFREQ'
AND column_name = 'REC_TYPE'
ORDER BY 1;

ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ---------
             1            1139      1139
             2            2224      1075
             3            3321      1107
             4            4469      1148
             5            5544      1075
             6            6689      1145
             7            7780      1091
             8            8907      1127
             9            9990      1083
         10000
            9991         1

EXPLAIN PLAN FOR
SELECT rec_desc
FROM topfreq
WHERE rec_type = 2;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------------------------
| Id | Operation                           | Name                | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                     | 1075 | 22575 |    25   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TOPFREQ             | 1075 | 22575 |    25   (0)|
----------------------------------------------------------------------------------------------

-- compare the above plan with the one from before histogram creation
-- the index scan is gone


EXPLAIN PLAN FOR
SELECT rec_desc
FROM topfreq
WHERE rec_type = 10000;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------------------------------
| Id | Operation                            | Name                | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |                     |    1 |    21 |     2   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | TOPFREQ             |    1 |    21 |     2   (0)|
|* 2 |   INDEX RANGE SCAN                   | IX_TOPFREQ_REC_TYPE |    1 |       |     1   (0)|
-----------------------------------------------------------------------------------------------
 
Hybrid Histogramh
Description Hybrid histograms combine the value of both frequency and height-balanced histograms. The database will, if possible, create a hybrid histograms rather than height-balanced histograms due to their increased value.

Unlike height-balanced histograms, a single endpoint value cannot span buckets. In addition to the highest value in the bucket, the histogram stores the number of occurrences the highest value is represented in the bucket, providing an accurate representation of its popularity, as well as giving an indication of the popularity of the other endpoints in the bucket.
Demo Table, Indexes and Data dbms_stats.gather_table_stats(<schema_name>, <table_name>, METHOD_OPT => 'FOR COLUMN SIZE <integer> <column_name>');
CREATE TABLE uwclass.hybrid(
rec_id   NUMBER(6),
rec_type NUMBER(5),
rec_desc VARCHAR2(30));

ALTER TABLE uwclass.hybrid ADD CONSTRAINT pk_hybrid PRIMARY KEY (rec_id);

CREATE INDEX ix_hybrid_rec_type ON uwclass.hybrid(rec_type);

INSERT INTO uwclass.hybrid
SELECT level AS recid,
  CASE WHEN MOD(level,2) = 0 THEN TRUNC(dbms_random.value(1,100))
       ELSE level
  END AS rec_type, dbms_random.string('x', 30)
FROM dual
CONNECT BY level <= 10000;

COMMIT;

SELECT rec_type, COUNT(*)
FROM hybrid
WHERE rec_type IN (2, 9999)
GROUP BY rec_type;

 REC_TYPE   COUNT(*)
---------- ---------
         2        40
      9999         1


EXPLAIN PLAN FOR
SELECT rec_desc
FROM uwclass.hybrid
WHERE rec_type = 2;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------------------------
| Id | Operation                            | Name               | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |                    |   40 | 12000 |     1   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | HYBRID             |   40 |  1200 |     1   (0)|
|* 2 |   INDEX RANGE SCAN                   | IX_HYBRID_REC_TYPE |   40 |       |     1   (0)|
----------------------------------------------------------------------------------------------


EXPLAIN PLAN FOR
SELECT rec_desc
FROM uwclass.hybrid
WHERE rec_type = 9999;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------------------------------
| Id | Operation                             | Name               | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                      |                    |    1 |    30 |     1   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED  | HYBRID             |    1 |    30 |     1   (0)|
|* 2 |   INDEX RANGE SCAN                    | IX_HYBRID_REC_TYPE |    1 |       |     1   (0)|
-----------------------------------------------------------------------------------------------
Manual Histogram Creation col column_name format a20
col range format a20

SELECT column_id, column_name, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'HYBRID'
ORDER BY 1;

 COLUMN_ID COLUMN_NAME  HISTOGRAM
---------- ------------ ---------------
         1 REC_ID       NONE
         2 REC_TYPE     NONE
         3 REC_DESC     NONE

exec dbms_stats.gather_table_stats('UWCLASS', 'HYBRID', CASCADE=>TRUE);

SELECT column_id, column_name, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = HYBRID'
ORDER BY 1;

 COLUMN_ID COLUMN_NAME  HISTOGRAM
---------- ------------ ---------------
         1 REC_ID       NONE
         2 REC_TYPE     HYBRID
         3 REC_DESC     NONE


SELECT (LAG(endpoint_value, 1, 0) OVER (ORDER BY endpoint_value)+1) || '-' || endpoint_value AS range,
endpoint_value - (LAG(endpoint_value, 1, 0) OVER (ORDER BY endpoint_value)+1) + 1 AS vals_in_range,
endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value) AS frequency,
endpoint_repeat_count
FROM dba_tab_histograms
WHERE owner = 'UWCLASS'
AND table_name = 'HYBRID'
AND column_name = 'REC_TYPE'
ORDER BY endpoint_value;

RANGE                VALS_IN_RANGE FREQUENCY  ENDPOINT_REPEAT_COUNT
-------------------- ------------- ---------- ---------------------
1-1                              1         49                    49
2-2                              1         40                    40
3-3                              1         56                    56
4-4                              1         52                    52
5-5                              1         59                    59
6-6                              1         51                    51
7-7                              1         47                    47
8-8                              1         44                    44
9-9                              1         64                    64
10-10                            1         43                    43
11-11                            1         58                    58
12-12                            1         52                    52
13-13                            1         41                    41
14-14                            1         51                    51
15-15                            1         48                    48
16-16                            1         48                    48
17-17                            1         46                    46
18-18                            1         59                    59
19-19                            1         49                    49
20-20                            1         47                    47
...
9694-9757                       64         32                     1
9758-9821                       64         32                     1
9822-9885                       64         32                     1
9886-9949                       64         32                     1
9950-9999                       50         25                     1
View Histogram Data CREATE OR REPLACE FUNCTION raw2num(rawval IN RAW) RETURN NUMBER AUTHID DEFINER IS
 n NUMBER;
BEGIN
  dbms_stats.convert_raw_value(rawval, n);
  RETURN n;
END raw2num;
/

SELECT column_name, num_distinct, raw2num(low_value) LV, raw2num(high_value) HV,
density, num_nulls, avg_col_len, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'HYBRID';

COLUMN_NAME  NUM_DISTINCT         LV         HV  DENSITY NUM_NULLS AVG_COL_LEN HISTOGRAM
------------ ------------ ---------- ---------- -------- ---------- ----------- --------
REC_ID              10000          1      10000    .0001          0           4 NONE
REC_TYPE             5049          1       9999  .000177          0           4 HYBRID
REC_DESC            10000 -5.318E+29 -1.114E-55    .0001          0          31 NONE


SELECT endpoint_value, endpoint_number, (endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value)) AS frequency
FROM dba_tab_histograms
WHERE owner = 'UWCLASS'
AND table_name = 'HYBRID'
AND column_name = 'REC_TYPE'
ORDER BY 1;

ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ---------
             1              46        46
             2              94        48
             3             141        47
             4             196        55
             5             254        58
             6             312        58
             7             362        50
             8             413        51
             9             460        47
            10             495        35
...
          9785            9893        32
          9849            9925        32
          9915            9958        33
          9979            9990        32
          9999           10000        10


EXPLAIN PLAN FOR
SELECT rec_desc
FROM hybrid
WHERE rec_type = 2;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------------------------
| Id | Operation                            | Name               | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |                    |   48 |  1680 |    18   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | HYBRID             |   48 |  1680 |    18   (0)|
|* 2 |   INDEX RANGE SCAN                   | IX_HYBRID_REC_TYPE |   48 |       |     1   (0)|
----------------------------------------------------------------------------------------------

-- compare the above plan with the one from before histogram creation,
-- the index scan is gone


EXPLAIN PLAN FOR
SELECT rec_desc
FROM hybrid
WHERE rec_type = 9999;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------------------------
| Id | Operation                           | Name                | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                     |    1 |    35 |     2   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| HYBRID              |    1 |    35 |     2   (0)|
|* 2 |   INDEX RANGE SCAN                  | IX_TOPFREQ_REC_TYPE |    1 |       |     1   (0)|
----------------------------------------------------------------------------------------------
 
Histogram Demos
Histogram Demo Create the servers and serv_inst tables: Click Here

SELECT column_id, column_name, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name IN ('SERVERS', 'SERV_INST')
ORDER BY 1;

set autotrace traceonly explain

SELECT s.srvr_id, i.type
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
AND type = 'WIN';

--------------------------------------------------------------------------------
| Id | Operation           | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |            |  417 | 12510 |     9   (0)| 00:00:01 |
|  1 |  NESTED LOOPS       |            |  417 | 12510 |     9   (0)| 00:00:01 |
|* 2 |   TABLE ACCESS FULL | SERV_INST  |  417 |  7098 |     9   (0)| 00:00:01 |
|* 3 |   INDEX UNIQUE SCAN | PK_SERVERS |    1 |    13 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------


SELECT s.srvr_id, i.type
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
AND type = 'MAC';

--------------------------------------------------------------------------------
| Id | Operation           | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |            |   96 |  2880 |     9   (0)| 00:00:01 |
|  1 |  NESTED LOOPS       |            |   96 |  2880 |     9   (0)| 00:00:01 |
|* 2 |   TABLE ACCESS FULL | SERV_INST  |   96 |  1632 |     9   (0)| 00:00:01 |
|* 3 |   INDEX UNIQUE SCAN | PK_SERVERS |    1 |     4 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------


set autotrace off

exec dbms_stats.gather_table_stats('UWCLASS', 'SERVERS', METHOD_OPT => 'FOR COLUMNS SIZE 10 srvr_id');
exec dbms_stats.gather_table_stats('UWCLASS', 'SERV_INST', METHOD_OPT => 'FOR COLUMNS SIZE 10 srvr_id');

SELECT column_id, column_name, histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'T'
ORDER BY 1;

 COLUMN_ID COLUMN_NAME  HISTOGRAM
---------- ------------ ---------------
         1 SRVR_ID      HYBRID
         7 SRVR_ID      TOP-FREQUENCY
Generated using DBMS_STATS

Based closely on a demonstrated developed by Tom Kyte and used at Harmony 2012
CREATE TABLE uwclass.t AS
SELECT do.*, CASE WHEN rownum < 500 THEN 1 ELSE 99 END AS some_status
FROM dba_objects do;

CREATE INDEX ix_t
ON uwclass.t(some_status);

SELECT histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'T'
AND column_name = 'SOME_STATUS';

HISTOGRAM
---------------
NONE


exec dbms_stats.gather_table_stats('UWCLASS', 'T', METHOD_OPT => 'FOR COLUMNS SIZE 10 some_status');

SELECT histogram
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'T'
AND column_name = 'SOME_STATUS';

HISTOGRAM
---------------
FREQUENCY


SELECT some_status, COUNT(*)
FROM t
GROUP BY some_status;

SOME_STATUS  COUNT(*)
----------- ---------
          1       499
         99     72629


set autotrace traceonly explain

SELECT * FROM t WHERE some_status = 1;

--------------------------------------------------------------------------------
| Id | Operation                            | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |      |  499 | 68363 |    12   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | T    |  499 | 68363 |    12   (0)|
|* 2 |   INDEX RANGE SCAN                   | IX_T |  499 |       |     1   (0)|
--------------------------------------------------------------------------------


SELECT * FROM t WHERE some_status = 99;

---------------------------------------------------------------
| Id | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|  0 | SELECT STATEMENT   |      | 72629 | 9716K |   420   (2)|
|* 1 |  TABLE ACCESS FULL | T    | 72629 | 9716K |   420   (2)|
---------------------------------------------------------------


set autotrace off
col value format 9999999999

SELECT *
FROM (
  SELECT *
  FROM sys.col_usage$
  WHERE obj# = (
    SELECT object_id
    FROM dba_objects
    WHERE object_name = 'T'
    AND owner = 'UWCLASS')
    )
  UNPIVOT (value FOR x IN (
  EQUALITY_PREDS, EQUIJOIN_PREDS, NONEQUIJOIN_PREDS, RANGE_PREDS, LIKE_PREDS, NULL_PREDS));

no rows selected

SELECT * FROM t WHERE some_status > 100;

exec dbms_stats.gather_table_stats('UWCLASS', 'T');

SELECT *
FROM (
  SELECT *
  FROM sys.col_usage$
  WHERE obj# = (
    SELECT object_id
    FROM dba_objects
    WHERE object_name = 'T'
    AND owner = 'UWCLASS')
    )
  UNPIVOT (value FOR x IN (
  EQUALITY_PREDS, EQUIJOIN_PREDS, NONEQUIJOIN_PREDS, RANGE_PREDS, LIKE_PREDS, NULL_PREDS));

  OBJ#  INTCOL# TIMESTAMP             FLAGS      X             VALUE
------ -------- -------------------- ------ ----------------- ------
 74587       27 14-JAN-2022 16:48:46      9 EQUALITY_PREDS         1
 74587       27 14-JAN-2022 16:48:46      9 EQUIJOIN_PREDS         0
 74587       27 14-JAN-2022 16:48:46      9 NONEQUIJOIN_PREDS      0
 74587       27 14-JAN-2022 16:48:46      9 RANGE_PREDS            1
 74587       27 14-JAN-2022 16:48:46      9 LIKE_PREDS             0
 74587       27 41-JAN-2022 16:48:46      9 NULL_PREDS             0
This demo is from Oracle 10.2.0.5 but serves as an important cautionary example for anyone using histograms.

The demo code is mine and the explanation from Jonathan Lewis.
Both of these explain plans were created using the same session logged into the same database within a matter of a few minutes. Note in the first explain plan "BITMAP CONVERSION" ... sometimes I get the first result, other times the second. All indexes are verified to be B*Tree (database version 10.2.0.5 on RedHat Linux V5U6). The SQL statement that generated the plans, below, was not altered in any way.

EXPLAIN PLAN FOR
  2  SELECT member_id, SUM(cnt)
  3  FROM (
  4    SELECT member_id, 1 CNT
  5    FROM webstore.orders
  6    WHERE site_id IN (23,24,25,29,30,31,32,33)
  7    AND status_id = 7
  8    AND date_received BETWEEN add_months(SYSDATE, -12) AND SYSDATE
  9    AND rownum = 1
 10    UNION ALL
 11    SELECT member_id, 1
 12    FROM webstore.orders
 13    WHERE site_id IN (23,24,25,29,30,31,32,33)
 14    AND status_id = 7
 15    AND date_received BETWEEN add_months(SYSDATE, -24) AND add_months(SYSDATE, -12)
 16    AND rownum = 1
 17    UNION ALL
 18    SELECT member_id, 1
 19    FROM webstore.orders
 20    WHERE site_id IN (23,24,25,29,30,31,32,33)
 21    AND status_id = 7
 22    AND date_received BETWEEN add_months(SYSDATE, -36) AND add_months(SYSDATE, -24)
 23    AND rownum = 1)
 24  WHERE rownum < 4
 25  GROUP BY member_id
 26  HAVING SUM(cnt) > 1;

Explained.

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------------------
|  Id | Operation                    | Name                | Rows| Bytes| Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |   3 |  111 |   32    (7)|
|*  1 | FILTER                       |                     |     |      |            |
|   2 | HASH GROUP BY                |                     |   3 |  111 |   32    (7)|
|*  3 | COUNT STOPKEY                |                     |     |      |            |
|   4 | VIEW                         |                     |   3 |  111 |   31    (4)|
|   5 | UNION-ALL                    |                     |     |      |            |
|*  6 | COUNT STOPKEY                |                     |     |      |            |
|*  7 | TABLE ACCESS BY INDEX ROWID  | ORDERS              |   4 |  192 |    9    (0)|
|*  8 | INDEX RANGE SCAN             | XIE2ORDERS          |     |      |    3    (0)|
|*  9 | COUNT STOPKEY                |                     |     |      |            |
|* 10 | TABLE ACCESS BY INDEX ROWID  | ORDERS              |   5 |  240 |   12    (9)|
|  11 | BITMAP CONVERSION TO ROWIDS  |                     |     |      |            |
|  12 | BITMAP AND                   |                     |     |      |            |
|  13 | BITMAP CONVERSION FROM ROWIDS|                     |     |      |            |

|* 14 | INDEX RANGE SCAN             | XIE2ORDERS          |     |      |    3    (0)|
|  15 | BITMAP CONVERSION FROM ROWIDS|                     |     |      |            |
|  16 | SORT ORDER BY                |                     |     |      |            |
|* 17 | INDEX RANGE SCAN             | ORDERS_DATE_RECEIVED|     |      |    3    (0)|

|* 18 | COUNT STOPKEY                |                     |     |      |            |
|* 19 | TABLE ACCESS BY INDEX ROWID  | ORDERS              |   4 |  192 |   10    (0)|
|* 20 | INDEX RANGE SCAN             | XIE2ORDERS          |     |      |    3    (0)|
--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
|  Id | Operation                    | Name                | Rows| Bytes| Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |   3 |  111 |  34     (3)|
|*  1 | FILTER                       |                     |     |      |            |
|   2 | HASH GROUP BY                |                     |   3 |  111 |  34     (3)|
|*  3 | COUNT STOPKEY                |                     |     |      |            |
|   4 | VIEW                         |                     |   3 |  111 |  33     (0)|
|   5 | UNION-ALL                    |                     |     |      |            |
|*  6 | COUNT STOPKEY                |                     |     |      |            |
|*  7 | TABLE ACCESS BY INDEX ROWID  | ORDERS              |   5 |  240 |  11     (0)|
|*  8 | INDEX RANGE SCAN             | XIE2ORDERS          |     |      |   3     (0)|
|*  9 | COUNT STOPKEY                |                     |     |      |            |
|* 10 | TABLE ACCESS BY INDEX ROWID  | ORDERS              |   4 |  192 |  10     (0)|
|* 11 | INDEX RANGE SCAN             | XIE2ORDERS          |     |      |   3     (0)|
|* 12 | COUNT STOPKEY                |                     |     |      |            |
|* 13 | TABLE ACCESS BY INDEX ROWID  | ORDERS              |   5 |  240 |  12     (0)|
|* 14 | INDEX RANGE SCAN             | XIE2ORDERS          |     |      |   3     (0)|
--------------------------------------------------------------------------------------


/*
The anomaly is, in principle, perfectly reasonable - and in your case could be explained simply by a histogram on the date column.

add_months(sysdate,N) is a known constant at optimisation time so, with a re-optimisation, a small change in sysdate, could produce different cardinalities and therefore different plans.
    ~Jonathan Lewis 19 November, 2011
*/

Related Topics
Built-in Functions
Built-in Packages
DBMS_STATS
Explain Plan
Outlines
TK Prof & Trace
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx