Warning: This one really just for the old nerds out there
Decades ago, one of the great contributions to the Oracle community was made by Wolfgang Breitling.
This presentation and associated PDF was probably the first true deep dive into how the optimizer works.
One of Wolfgang’s discoveries was the default selectivity’s the optimizer would use in the absence of any assisting information
To put the bottom two in simpler terms
- When you have WHERE mycol > :b1, we assume we’ll get 5% of the rows
- When you have WHERE mycol between :b1 and :b2, we assume we’ll get 5% of 5% of the rows (ie, 0.25% because we are in effect compounding the condition above)
Here’s a trivial demo of that
SQL> create table t1 as select rownum r , rownum r1 from dual connect by level <= 100000; SQL> explain plan for select * from t1 where r >= :a; -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5000 | 50000 | 63 (4)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 5000 | 50000 | 63 (4)| 00:00:01 | --------------------------------------------------------------------------
But colleague Alexander Day pinged with a new variation on this theme.
What if there is an index on this column?
Repeating the demo with an index, we still see the 5000 rows estimate coming from the table, but there’s an interesting figure for the index. (line 2)
SQL> create table t2 as select rownum r , rownum r1 from dual connect by level <= 100000; SQL> create index ix2 on t2 ( r ); SQL> explain plan for select * from t2 where r >= :a; -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5000 | 50000 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 5000 | 50000 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IX2 | 900 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
A bit of investigation with the help of Optimizer guru Nigel Bayliss and we discovered a new default selectivity value deep down in the bowels of the code, which you’ll also see in a 10053 trace
Access Path: index (RangeScan) Index: IX2 resc_io: 5.000000 resc_cpu: 368607 ix_sel: 0.009000 ix_sel_with_filters: 0.009000 <<<===== Cost: 5.022687 Resp: 5.022687 Degree: 1
The usefulness of knowing this value is probably close to zero. Let’s say … 0.009
But once again, I tip my hat to Wolfgang for that great research so many years ago.
