Oracle Histograms
Version 18c

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_PART_HISTOGRAMS DBA_TAB_HISTOGRAMS
ALL_PART_HISTOGRAMS CDB_SUBPART_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      537
        2      570
        3      555
        4      535
        5      543
        6      561
        7      556
        8      589
        9      554

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                    |                     | 5678 |  166K |    14   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| FREQBAL             | 5678 |  166K |    14   (0)|
|* 2 |   INDEX RANGE SCAN                  | IX_FREQBAL_REC_TYPE | 5678 |       |    14   (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;

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

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;
/

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   .00005          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            5000      5000
             1            5537       537
             2            6107       570
             3            6662       555
             4            7197       535
             5            7740       543
             6            8301       561
             7            8857       556
             8            9446       589
             9           10000       554

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                    |                     | 5000 |   97K |    25   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| FREQBAL             | 5000 |   97K |    25   (0)|
|* 2 |   INDEX RANGE SCAN                  | IX_FREQBAL_REC_TYPE | 5000 |       |    25   (0)|
----------------------------------------------------------------------------------------------
 
Height Balanced
Description In Oracle 11gR2 and before, when the number of distinct column values exceeds the number of buckets, Oracle automatically creates a height balanced histogram: Thus each bucket represents a range of values with each bucket containing about the same number of segment rows.

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                    |                   | 5224 |  153K |    14   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| HTBAL             | 5224 |  153K |    14   (0)|
|* 2 |   INDEX RANGE SCAN                  | IX_HTBAL_REC_TYPE | 5224 |       |    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;

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 ID           NONE
         2 RECORD_TYPE  HEIGHT BALANCED
         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;
/

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 10040 9 9997 .000099602 0 4 NONE
REC_TYPE 4941 0 9999 .000098752 0 4 HEIGHT BALANCED
REC_DESC 10303 -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
            27 126 1
           127 127 1
           213 128 1
           281 129 1
           361 130 1
           421 131 1
           495 132 1
           557 133 1
           637 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   |       | 5070 |  173K |    25   (0)|
|* 1 |  TABLE ACCESS FULL | HTBAL | 5070 |  173K |    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      1178
        2      1062
        3      1059
        4      1123
        5      1186
        6      1081
        7      1096
        8      1142
        9      1063
     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                    |                     | 1116 | 33480 |     6   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TOPFREQ             | 1116 | 33480 |     6   (0)|
|* 2 |   INDEX RANGE SCAN                  | IX_TOPFREQ_REC_TYPE | 1116 |       |     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;

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       1178
2-2                   1       1062
3-3                   1       1059
4-4                   1       1123
5-5                   1       1186
6-6                   1       1081
7-7                   1       1096
8-8                   1       1142
9-9                   1       1063
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            1178      1178
             2            2240      1062
             3            3299      1059
             4            4422      1123
             5            5608      1186
             6            6689      1081
             7            7785      1096
             8            8927      1142
             9            9990      1063
         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                    |                     | 1116 | 23436 |    25   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TOPFREQ             | 1116 | 23436 |    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 Histogram
Description Hybrid histograms combine the value of both frequency and height-balanced histograms. As of version 12cR1 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 represetation 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_desd 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        53
      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                    |                    |   53 |  1590 |     1   (0)|
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | HYBRID             |   53 |  1590 |     1   (0)|
|* 2 | INDEX RANGE SCAN                    | IX_HYBRID_REC_TYPE |   53 |       |     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;

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 IN ('SERVERS', 'SERV_INST')
ORDER BY 1;

 COLUMN_ID COLUMN_NAME  HISTOGRAM
---------- ------------ ---------------
         1 REC_ID       NONE
         2 REC_TYPE     HYBRID
         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_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'
AND rownum < 21
ORDER BY endpoint_value;

RANGE                VALS_IN_RANGE FREQUENCY  ENDPOINT_REPEAT_COUNT
-------------------- ------------- ---------- ---------------------
1-1                              1         47                    56
2-2                              1         52                    53
3-3                              1         62                    49
4-4                              1         55                    42
5-5                              1         48                    63
6-6                              1         53                    36
7-7                              1         58                    50
8-8                              1         52                    49
9-9                              1         45                    55
10-10                            1         48                    61
11-11                            1         62                    56
12-12                            1         46                    49
13-13                            1         62                    58
14-14                            1         50                    66
15-15                            1         56                    56
16-16                            1         50                    51
17-17                            1         58                    60
18-18                            1         47                    64
19-19                            1         59                    34
20-20                            1         55                    52
...
9718-9781                       64         32                     1
9782-9845                       64         32                     1
9846-9909                       64         32                     1
9910-9973                       64         32                     1
9974-9999                       26         13                     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 .000176 0 4 HYBRID
REC_DESC 10000 -5.345E+29 -1.119E-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 56 56
2 109 53
3 158 49
4 200 42
5 263 63
6 299 36
7 349 50
8 398 49
9 453 55
10 514 61
...
 9781 9891 32
9845 9923 32
9909 9955 32
9973 9987 32
9999 10000 13

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                      |                    |   53 |  1855 |    20   (0)|
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED   | HYBRID             |   53 |  1855 |    20   (0)|
|* 2 | INDEX RANGE SCAN                      | IX_HYBRID_REC_TYPE |   53 |       |     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 29-NOV-2018 16:48:46      9 EQUALITY_PREDS         1
 74587       27 29-NOV-2018 16:48:46      9 EQUIJOIN_PREDS         0
 74587       27 29-NOV-2018 16:48:46      9 NONEQUIJOIN_PREDS      0
 74587       27 29-NOV-2018 16:48:46      9 RANGE_PREDS            1
 74587       27 29-NOV-2018 16:48:46      9 LIKE_PREDS             0
 74587       27 29-NOV-2018 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.

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

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
|  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)|
--------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
|  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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx