Fake Histograms

I got an email a few days ago asking me how to create a “height-balanced” histogram using the dbms_stats.set_column_stats() procedure.

I posted an example of creating a frequency histogram a few months ago – and it doesn’t take much to turn this into an example for the height-balanced type; the key change apppears in three critical lines.

In the original I had the following description for the histogram:

	n_array		:= dbms_stats.numarray(75, 81,  88, 91,   99);
	srec.bkvals	:= dbms_stats.numarray( 2, 40, 200,  3,  755);
	srec.epc	:= 5;

This sets up the data for a frequency histogram with five distinct values (those shown in the n_array) with the (relative) frequencies shown in the src.bkvals array.

Here’s the change I have to make to define a height-balanced histogram with 10 buckets (which often means 11 end-points).

	n_array		:= dbms_stats.numarray(20, 60, 2000, 3000, 3000, 3000, 3000, 3000, 3000, 4000,  10000);
	srec.bkvals	:= null;
	srec.epc	:= 11;

These figures say that the column has a low value of 20 and a high value of 10,000; and with 10 buckets each bucket represents one tenth of the available data (dba_table.num_rows – dba_tab_columns.num_nulls). Note that the value 3,000 appears six times in the n_array; this is an example of a popular value (the repetitions will be “compressed” out of the data dictionary when you query dba_tab_histograms).

Here’s a complete example so that you don’t have to cut-n-paste two articles into one. I haven’t changed the distcnt, density and nullcnt from the values I used for the frequency histogram – but they’re not the point of interest in this case. I’ve also added a couple of queries at the end of the example to show you what gets into the data dictionary, and a couple of queries against the table so you can see the impact of the histogram on the cardinality calculations.

rem
rem     Script:         c_height_hist.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2010
rem

create table t1 as
select
        rownum  n1
from    all_objects
where
        rownum <= 10000  --> comment to avoid wordpress format issue
;

declare

        m_distcnt               number;
        m_density               number;
        m_nullcnt               number;
        srec                    dbms_stats.statrec;
        m_avgclen               number;

        n_array                 dbms_stats.numarray;

begin

        m_distcnt       := 100;
        m_density       := 0.02;
        m_nullcnt       := 0;
        m_avgclen       := 3;

        n_array         := dbms_stats.numarray(20, 60, 2000, 3000, 3000, 3000, 3000, 3000, 3000, 4000,  10000);
        srec.bkvals     := null;
        srec.epc        := 11;

        dbms_stats.prepare_column_values(srec, n_array);

        dbms_stats.set_column_stats(
                ownname         => user,
                tabname         => 't1',
                colname         => 'n1',
                distcnt         => m_distcnt,
                density         => m_density,
                nullcnt         => m_nullcnt,
                srec            => srec,
                avgclen         => m_avgclen
        ); 

end;
/

select
	num_distinct,
	low_value,
	high_value,
	density,
	num_nulls,
	num_buckets,
	histogram
from
	user_tab_columns
where
	table_name = 'T1'
and	column_name = 'N1'
;

select
	endpoint_value,
	endpoint_number,
	lag(endpoint_number,1) over(
		order by endpoint_number
	) prev_number
from
	user_tab_histograms
where
	table_name = 'T1'
and	column_name = 'N1'
order by
	endpoint_value
;

set autotrace traceonly explain

select * from t1 where n1 = 3000;
select * from t1 where n1 between 7500 and 8000;

set autotrace off

And here are the results from the queries at the end of the example, when run against 10.2.0.3.

NUM_DISTINCT LOW_VALUE                HIGH_VALUE                  DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM
------------ ------------------------ ------------------------ ---------- ---------- ----------- ---------------
         100 C115                     C302                            .02          0          10 HEIGHT BALANCED

1 row selected.

ENDPOINT_VALUE ENDPOINT_NUMBER PREV_NUMBER
-------------- --------------- -----------
            20               0
            60               1           0
          2000               2           1
          3000               8           2
          4000               9           8
         10000              10           9

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  6000 | 18000 |     4 |
|*  1 |  TABLE ACCESS FULL| T1   |  6000 | 18000 |     4 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=3000)

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    83 |   249 |     4 |
|*  1 |  TABLE ACCESS FULL| T1   |    83 |   249 |     4 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1">=7500 AND "N1"<=8000)

As you can see, all the statistics I supplied have been written into the database, and the histogram figures show the “storage compression” effect on the popular 3,000. (Ignore the “prev_number” column in that output, it’s only relevent for frequency histograms).

The first query (n1 = 3000) shows that the optimizer has picked up the 6 buckets that end at 3,000 to get a cardinality of 6,000 of the 10,000 rows; and the range predicate (n1 between 7500 and 8000) covers a range of 500 from the last bucket which has a range of 6,000 (viz: 4000 – 10000) gives it a cardinality of 1,000 * 500/6,000 = 83.3

[Further reading on Histograms]