wuff. That's not funny. That's the fact that optimizer never knows all the facts.
Sometimes I would like Oracle to stop overoptimizing and revert to the old good rules approach...
BTW, it may be merely a bug. I found quite a good article on that issue:
Doc ID: Note:244040.1
Recommended Performance Patches
for Oracle E-Business Suite
https://metalink.oracle.com/metalink/plsql/f?p=130:14:4167759382313069588::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,244040.1,1,1,1,helvetica
-----Original Message-----
From: oracle-l-bounce@(protected)
Sent: 7. j?n? 2006 08:34
To: oracle-l@(protected)
Subject: Does Oracle have a Sense of Humour?
Hello,
I can't help posting this topic even it is not really a core theme of this list.
I hope at least some may find this useful of just funny.
SQL> select * from t where d_id in (1111,1112,1113,1114,1115);
. . . . .
9 rows selected.
Elapsed: 00:00:55.31
Well not really best response time, but wait there is an index on the table ...
SQL> select /*+ INDEX(t) */ * from t where d_id in
(1111,1112,1113,1114,1115);
. . . .
9 rows selected.
Elapsed: 00:00:00.23
Excellent! But actually I need only one particular column of the table ...
SQL> select /*+ INDEX(t) */ x_id from t where d_id in
(1111,1112,1113,1114,1115);
. . . .
9 rows selected.
Elapsed: 00:00:21.67
Upps!
The script to build the table is bellow.
You can find more discussion under http://www.db-nemec.com/SenseofHumour.html
In a OLTP configured DB you may need to add some members to the IN list to see the effect.
Regards,
Jaromir
--- the script ---
create table t
(d_id number,
x_id number,
y number,
pad char(100));
--- stuff out histogram (with 255 different values)
insert into t
select
mod(rownum-1,255),
rownum, rownum,'x'
from dual
connect by level <= 5000000; --
--- and fill the table with approx. 1 records per dim. key
insert into t
select
1000+trunc(DBMS_RANDOM.VALUE(0,1)* 5000000),
rownum, rownum,'x'
from dual
connect by level <= 5000000;
--
commit;
--
create index t_idx on t(d_id);
create index t_idx2 on t(x_id,y,d_id);
--
begin
dbms_stats.gather_table_stats(ownname=>user, tabname=>'t',
method_opt=>'for all columns size 254', cascade => true,
estimate_percent => 10);
end;
/
--
http://www.freelists.org/webpage/oracle-l
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l
