Does Oracle have a Sense of Humour?

  • From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 7 Jun 2006 10:33:58 +0200 (CEST)

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


Other related posts: