| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Does Oracle have a Sense of Humour?
>It's not clear why Oracle chooses t_idx2 index full scan versus range scan on t_idx when index explicitly not specified.
This is the darned covering index effect. Oracle assumes it's better to scan t_idx2 which contains column x.
One more thing:
it could be(also should not) that this query hits the bug 3663924 "Bad cardinality for out-of-range range predicates", which is available at 9i and 10g. It looks very much like that. To be on the safe side the estimate_percent must be 100% (it is 15% in this case)
Brgds, Laimis N.
(btw, linkejimai)
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mindaugas Navickas
Sent: 7. júní 2006 15:47
To: jaromir_at_db-nemec.com; oracle-l_at_freelists.org
Subject: Re: Does Oracle have a Sense of Humour?
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
> 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
>
>
>
-- http://www.freelists.org/webpage/oracle-l Fyrirvari/Disclaimer http://www.landsbanki.is/disclaimer -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 07 2006 - 11:04:30 CDT
 |
 |