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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of jaromir nemec Sent: 7. júní 2006 08:34 To: oracle-l@xxxxxxxxxxxxx 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; / -- //www.freelists.org/webpage/oracle-l Fyrirvari/Disclaimer http://www.landsbanki.is/disclaimer -- //www.freelists.org/webpage/oracle-l