Re: Does Oracle have a Sense of Humour?

From: Mindaugas Navickas <mnavickas_at_yahoo.com>
Date: Wed, 7 Jun 2006 11:46:39 -0400 (EDT)
Message-ID: <20060607154639.60673.qmail@web30108.mail.mud.yahoo.com>

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_at_db-nemec.com> 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;
> /
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

---

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 07 2006 - 10:46:39 CDT