Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Paul.

Asked: April 19, 2006 - 9:59 am UTC

Last updated: December 12, 2006 - 7:21 am UTC

Version: 9.2.0.4

Viewed 1000+ times

You Asked

Tom,
This is an academic question, supposing I have a non-unique index on a field in a table (IDX1).Assuming the index and table have been analyzed.The index is on a datetime column, with 6 distinct values.

Num of rows in table - 24737

On doing an explain plan on my query which simply does a
select * from table where indexed date field <= (sysdate -4).

Num of rows that satisfy this predicate - 24737

but the explain plan only shows 1246 in the cardinality step that does an INDEX RANGE SCAN on the index (IDX1).Any ideas?

and Tom said...

It is the 5% "guess" that the CBO will use when the compared to value is "not well known". In 9i - the optimizer looks at "sysdate-4" and says "hmm, every time sysdate will be different, therefore, we shall GUESS"

ops$tkyte@ORA9IR2> create table t
2 as
3 select sysdate-10-mod(rownum,6) dt
4 from all_objects
5 where rownum <= 24737;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(dt);

Index created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=> true );

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where dt <= (sysdate-4);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1237 Bytes=9896)
1 0 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=1237 Bytes=9896)



ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*), count(*)*0.05 pct_guess
2 from t
3 where dt <= (sysdate-4);

COUNT(*) PCT_GUESS
---------- ----------
24737 1236.85

1237 = round(1236.85)


However, things change - in 10g, you'll see the optimizer says "let us be smarter with this sysdate thing:


ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select * from t where dt <= (sysdate-4);

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24899 | 194K| 15 (14)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 24899 | 194K| 15 (14)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("DT"<=SYSDATE@!-4)

ops$tkyte@ORA10GR2> set autotrace off
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select count(*), count(*)*0.05 pct_guess
2 from t
3 where dt <= (sysdate-4);

COUNT(*) PCT_GUESS
---------- ----------
24737 1236.85


It did not use the 5% guess anymore.

thanks to Jonathan Lewis - whose recent writings on another thread on asktom this week inspired this answer :)


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

mind-blowing answer

Paul, April 20, 2006 - 3:41 am UTC

As usual the reply was spot on, this really helped me understand the concept better, just one doubt is the 5% guess that the optimizer uses a hard-coded value or does it change with other statistics?

Once again thanks a lot.Tom ,your pic should be in the Oracle Hall of fame.


Tom Kyte
April 20, 2006 - 8:04 am UTC

5% is a "rule" used by the CBO

A reader, April 20, 2006 - 8:16 am UTC

so we have to use hexedit oracle.exe to change it ?

Tom Kyte
April 20, 2006 - 10:10 am UTC

umm, that would be "no"

you do not change it.

Alberto Dell'Era, April 20, 2006 - 3:39 pm UTC

For completeness, I think it's worth mentioning that this "issue" about sysdate has been fully investigated in Jonathan's book, page 130, chapter "Surprising Sysdate" ...

In particular, the use of 5% as a guess in 9i (but not in 10g) is limited to range predicates containing "function(sysdate)", but not "sysdate":

-- note the use of sysdate - zero
dellera@ORACLE9I> select * from t where dt <= (sysdate-0);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1237 Bytes=9896)
1 0 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=1237 Bytes=9896)

dellera@ORACLE9I> select * from t where dt <= sysdate;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=24737 Bytes=197896)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=7 Card=24737 Bytes=197896)

[test above in 9.2.0.6]

In that chapter, many more cases are discussed - such as "between sysdate and sysdate+1", etcetera.

sysdate in query

Dave, May 23, 2006 - 11:43 am UTC

Fix the use of SYSDATE in query/cursor with a bind variable to help CBO. very Nice.

Cardinality of Like predicate

Jens Schauder, December 12, 2006 - 7:12 am UTC 

Hi Tom,
how can I influence the estimate Oracle uses for the cardinality of a like clause like the following:

select * from t
where a like '%sometext%'

From the way people use the application I am working on I know that this predicate will be true for typically 0.1% of the rows, so accessing the data through an index is way faster then a full table scan (tested that using hints).

But Oracle insists on using a full table scan, because it uses the 5% rule (at least it looks like):


SQL> SET autotrace OFF;
SQL> SELECT * FROM v$version;

BANNER                                                                          
----------------------------------------------------------------                
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production                
PL/SQL Release 9.2.0.8.0 - Production                                           
CORE    9.2.0.8.0    Production                                                       
TNS for HPUX: Version 9.2.0.8.0 - Production                                    
NLSRTL Version 9.2.0.8.0 - Production                                           

SQL> 
SQL> SELECT COUNT(*) FROM TALL_SACHSTAMM;

  COUNT(*)                                                                      
----------                                                                      
   2596931                                                                      

SQL> 
SQL> SELECT COUNT(*) FROM TALL_SACHSTAMM
  2  WHERE snr_ben_d LIKE '%eistell%';

  COUNT(*)                                                                      
----------                                                                      
      1500                                                                      

SQL> 
SQL> SET autotrace traceonly;
SQL> 
SQL> SELECT * FROM TALL_SACHSTAMM
  2  WHERE snr_ben_d LIKE '%eistell%';

1500 rows selected.


Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6094 Card=129844 Byt          
          es=13503776)                                                          
                                                                                
   1    0   TABLE ACCESS (FULL) OF 'TALL_SACHSTAMM' (Cost=6094 Card=12          
          9844 Bytes=13503776)                                                  
                                                                                




Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
      40338  consistent gets                                                    
      33817  physical reads                                                     
       5624  redo size                                                          
      41953  bytes sent via SQL*Net to client                                   
        969  bytes received via SQL*Net from client                             
        101  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
       1500  rows processed                                                     

SQL> 
SQL> SELECT /*+ index(tall_sachstamm tall_sachstamm_b05) */
  2  *
  3  FROM TALL_SACHSTAMM
  4  WHERE snr_ben_d LIKE '%eistell%';

1500 rows selected.


Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=45294,876 Card=12984          
          4 Bytes=13503776)                                                     
                                                                                
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TALL_SACHSTAMM' (Cost=45          
          294,876 Card=129844 Bytes=13503776)                                   
                                                                                
   2    1     BITMAP CONVERSION (TO ROWIDS)                                     
   3    2       BITMAP INDEX (FULL SCAN) OF 'TALL_SACHSTAMM_B05'                




Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
       2155  consistent gets                                                    
        286  physical reads                                                     
          0  redo size                                                          
      41953  bytes sent via SQL*Net to client                                   
        969  bytes received via SQL*Net from client                             
        101  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
       1500  rows processed                                                     

SQL> spool off


kind regards
Jens Schauder

Tom Kyte
December 12, 2006 - 7:20 am UTC 

ops$tkyte%ORA9IR2> select /*+ first_rows(1500) */ * from t where rowid in (select rowid from t where x like '%eistell%');

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1501 Card=1500 Bytes=150000)
   1    0   NESTED LOOPS (Cost=1501 Card=1500 Bytes=150000)
   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP INDEX (FAST FULL SCAN) OF 'T_IDX'
   4    1     TABLE ACCESS (BY USER ROWID) OF 'T' (Cost=1 Card=1 Bytes=76)



ops$tkyte%ORA9IR2> set autotrace off

This is just evil ....

Jens Schauder, December 12, 2006 - 9:17 am UTC

evil but beautiful 8)



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library