Histogram Usage Example

Purpose :
This document shows the importance of histogram. Mainly required for non-bind values(OLAP system mainly uses the non-bind values. Not OLTP)

Time Needed : 10min

Preparation

create table t as select mod(rownum,3) X, a.* from all_objects a;
insert /*+ append */ into t select * from t;
commit;
insert /*+ append */ into t select * from t;
commit;
insert /*+ append */ into t select * from t;
commit;
select count(*) from t;
— around 1 million rows.
update t set x = 4 where rownum <= 10;
so, I have 1million records. 10 of which have the #4. the rest have values
of 0, 1, 2. Now I index that column
create index t_idx on t(x) unrecoverable parallel 8
analyze table t compute statistics;
set autotrace traceonly explain

1. Test Case 1 (This is what generally happens without histogram)

select * from t where x = 4;

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=446 Card=276944 Bytes=24371072)
1 0 TABLE ACCESS (FULL) OF ‘T’ (Cost=446 Card=276944 Bytes=24371072) == CARD = TOTAL ROWS/DISTINCT VALUE = 1107776/4

full tablescan just like you. Now, lets play with the histograms.

2. Test Case 2 (Now see the difference with Histogram in place)

analyze table t compute statistics for columns x size 100;
select * from t where x = 4;

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=10 Bytes=880)
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=4 Card=10 Bytes=880) == now cardinality knows how many rows are reqired.
2 1 INDEX (RANGE SCAN) OF ‘T_IDX’ (NON-UNIQUE) (Cost=3 Card=10)

and the optimizer has the right info — does the right thing. Just to
validate, lets see what happens when we user the #1:

select * from t where x = 1;

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=446 Card=369276 Bytes=32496288)
1 0 TABLE ACCESS (FULL) OF ‘T’ (Cost=446 Card=369276 Bytes=32496288)

back to a full scan – AS IT SHOULD be for that one

Backout Procedure

set autotrace off
drop table t;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: