Hi Jeromir, I would be more specific specifying index hint: select /*+ INDEX(t t_idx) */ x_id from t where d_id in (1111,1112,1113,1114,1115); Elapsed: 00:00:00.03 select /*+ INDEX(t t_idx) */ * from t where d_id in (1111,1112,1113,1114,1115) Elapsed: 00:00:00.06 It's not clear why Oracle chooses t_idx2 index full scan versus range scan on t_idx when index explicitly not specified. Regards Mindaugas Navickas --- jaromir nemec <jaromir@xxxxxxxxxxxx> wrote: > 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 > > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- //www.freelists.org/webpage/oracle-l