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.
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 ?
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
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)