Update on Histograms

OK, that was a bad example. Here's a new approach.

When the Oracle documentation talks about skewed values they are referring to particular values in a column occuring with significantly different frequency than other values. A classical example of this is 95% "M"and 5% "F".

However, let me propose that what this misses is the consideration of situations like the following: 95% A, 5% B, 0% C, 0% D ... ie. there are values that do not occur in the column but which the optimizer might believe do occur -- or might occur, at any rate.

Let's take a slightly more "real world" example. Let's say that we have a wholesale transactions table that is range partitioned on "Date Of Transaction" at a monthly level, so that there are approximately 30 days to the month. Now the key to this example is that no sales occur on Saturday or Sunday, so in fact there are around twenty unique values to each partition. Each value in our example has the same number of rows, and the distinct values have a pretty even distribution.

Now even if the data does not possess the classical type of skew, there is still a type in there that is hidden from the optimizer, because Saturday and Sunday values of data have 0% representation in the table. The key is to let the optimizer know about these gaps that it is not aware of from High-Low-N column statistics.

Example script:

SQL> drop table demo_hist;

Table dropped.

SQL>
SQL> create table demo_hist
2 as
3 select my_date,
4 num
5 From (
6 Select To_Date('01-aug-2005')+
7 Mod(rownum,
8 Add_months('01-Aug-2005',1)-To_Date('01-Aug-2005'))
9 my_date,
10 rownum num
11 From dual
12 Connect By 1=1 and level <= 100000 13 ) 14 Where to_char(my_date,'D') not in ('1','7'); Table created. SQL>
SQL> commit;

Commit complete.

SQL>
SQL> set autotrace on
SQL>
SQL> select count(*) from demo_hist
2 where my_date = to_date('01-aug-2005');

COUNT(*)
----------
3225


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=44 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DEMO_HIST' (TABLE) (Cost=44 Card
=2943 Bytes=26487)





Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
251 consistent gets
184 physical reads
0 redo size
394 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> select count(*) from demo_hist
2 where my_date between to_date('06-aug-2005') and to_date('09-aug-2005');

COUNT(*)
----------
6452


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=44 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DEMO_HIST' (TABLE) (Cost=44 Card
=5830 Bytes=52470)





Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
247 consistent gets
0 physical reads
0 redo size
394 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL>
SQL> begin
2 dbms_stats.gather_table_stats
3 (
4 ownname => user,
5 tabname => 'DEMO_HIST',
6 estimate_percent => 100,
7 method_opt => 'for columns NUM size 254'
8 );
9 end;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from demo_hist
2 where my_date = to_date('01-aug-2005');

COUNT(*)
----------
3225


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=45 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DEMO_HIST' (TABLE) (Cost=45 Card
=742 Bytes=6678)





Statistics
----------------------------------------------------------
42 recursive calls
0 db block gets
192 consistent gets
0 physical reads
0 redo size
394 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> select count(*) from demo_hist
2 where my_date between to_date('06-aug-2005') and to_date('09-aug-2005');

COUNT(*)
----------
6452


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=45 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DEMO_HIST' (TABLE) (Cost=45 Card
=185 Bytes=1665)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
188 consistent gets
0 physical reads
0 redo size
394 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> begin
2 dbms_stats.gather_table_stats
3 (
4 ownname => user,
5 tabname => 'DEMO_HIST',
6 estimate_percent => 100,
7 method_opt => 'for columns MY_DATE size 254'
8 );
9 end;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from demo_hist
2 where my_date = to_date('01-aug-2005');

COUNT(*)
----------
3225


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=45 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DEMO_HIST' (TABLE) (Cost=45 Card
=3225 Bytes=25800)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
188 consistent gets
0 physical reads
0 redo size
394 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> select count(*) from demo_hist
2 where my_date between to_date('06-aug-2005') and to_date('09-aug-2005');

COUNT(*)
----------
6452


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=45 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DEMO_HIST' (TABLE) (Cost=45 Card
=6452 Bytes=51616)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
188 consistent gets
0 physical reads
0 redo size
394 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Summary ...

Predicate = Between
------------------------ ------ --------
Correct Answer 3,225 6,452
No Stats Estimate 2,943 5,830
Col Stats Estimate 742 185
Histogram Stats Estimate 3,225 6,452

Obviously there's more to this ... the histogram-based estimate was so good because the number of distinct values was low, and you'd expect the quality of the estimate to degenerate as the number of distinct values increases.

Thoughts?