Oracle dynamic sampling helps CBO cardinality estimates

Also see these related notes on cardinality estimation:

The central problem with cardinality estimation is the in cases of complex WHERE clauses the optimizer does not have enough information about inter-join result set sizes to determine the optimal table join order. Einstein would have trouble figuring out this optimal table join order:

select
   stuff
from
   customer
natural join
   orders
natural join
   item
natural join
   product
where
   credit_rating * extended_credit > .07
and (qty_in_stock * velocity) /.075 < 30
or (sku_price / 47) * (qty_in_stock / velocity) > 47;

Oracle professional Jeff Moss has published an article showing how Oracle10g dynamic sampling might help in cases of missing histograms when using built-in functions on date range scans, noting research by noted Oracle professional, Wolfgang Breitling:

I wrote a while ago in a post here that the CBO has no way of knowing how many rows it will return for a given fixed date when applied to the FROM_DATE / TO_DATE of an SCD2 table . . .

He suggested that using dynamic sampling at level 4 or above might allow the CBO to determine the selectivity for combinations of FROM_DATE / TO_DATE in the target SCD2 table…

This suggests that setting dynamic sampling to level 4 may remove the tedium of identifying and maintaining histograms for columns referenced in complex WHERE clause predicates to estimate the cardinality returned from a table. This has important ramifications for helping the CBO join tables together in the fastest order.

Here is a portion of his excellent test and evidence for Oracle 10g dynamic sampling in lieu of histograms:

jeff[28/5]@XE> SELECT COUNT(1)
2 FROM jeff_dynamic_sampling
3 WHERE TO_DATE('15-JAN-2005','DD-MON-YYYY') BETWEEN from_date AND NVL(to_date,TO_DATE('31-DEC-9999','DD-MON-YYYY'));

COUNT(1)
----------
13

1 row selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2444057389

--------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 13 3 (0) 00:00:01
1 SORT AGGREGATE 1 13
* 2 TABLE ACCESS FULL JEFF_DYNAMIC_SAMPLING 1 13 3 (0) 00:00:01