Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: oracle can ignore hints

Re: oracle can ignore hints

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 5 Mar 2004 12:30:27 -0000
Message-ID: <013601c402ad$a5e91450$7002a8c0@Primary>

Notes in-line

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

March 2004 Hotsos Symposium - The Burden of Proof   Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar

: Based on experience for versions 7 - 8 the CBO will do as it is told via a
: syntactically valid hint if the join order and method allow do not
: invalidate the hint. If you provide an index hint to use an index into
: table B and Oracle chooses to drive on table B then the hint is unusable.

I don't think that's true, but I don't have a 7 or 8 on hand to check. The script at the end of the note shows it to be false in 9; but more significantly shows how Oracle will follow a totally ridiculous path if hinted correctly.

drop table t1;

create table t1 (
 id number,
 v1 number,
 small_vc varchar2(10),
 padding varchar2(100)
);

drop table t2;

create table t2(
 id number,
 v1 number,
 small_vc varchar2(10),
 padding varchar2(100)
);

insert into t1
select rownum, 1, rpad('x',10), rpad('x',100) from all_objects
where rownum <= 5000;
commit;

insert into t2
select rownum, rownum, rpad('x',10), rpad('x',100) from all_objects
where rownum <= 5000;
commit;

alter table t1 add constraint t1_pk primary key(id); alter table t2 add constraint t2_pk primary key(id);

create index t1_junk on t1(v1);

analyze table t1 compute statistics;
analyze table t2 compute statistics;

set autotrace traceonly explain

spool temp

select t1.small_vc, t2.small_vc
from
 t1,t2
where
 t2.id = 99
and t1.id = t2.id
and t1.v1 = 1
;

select
 /*+ ordered index(t1 t1_junk) */
 t1.small_vc, t2.small_vc
from
 t1,t2
where
 t2.id = 99
and t1.id = t2.id
and t1.v1 = 1
;

spool off

doc

Execution plans on 9.2.0.4 with DBCA installed database CBO chose this path:

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=28)    1 0 NESTED LOOPS (Cost=4 Card=1 Bytes=28)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=3 Card=1 Bytes=13)
   3    2       INDEX (UNIQUE SCAN) OF 'T2_PK' (UNIQUE) (Cost=2 Card=5000)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=15)
   5    4       INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE) (Cost=1 Card=5000)



Elapsed: 00:00:00.00

CBO did as it was told - and got all 5,000 rows from the T1 table using a completely useless index.

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=99 Card=1 Bytes=28)    1 0 MERGE JOIN (CARTESIAN) (Cost=99 Card=1 Bytes=28)    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=98 Card=1 Bytes=15)

   3 2 INDEX (RANGE SCAN) OF 'T1_JUNK' (NON-UNIQUE) (Cost=11 Card=5000)

   4    1     BUFFER (SORT) (Cost=1 Card=1 Bytes=13)
   5    4       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1
Bytes=13)
   6    5         INDEX (UNIQUE SCAN) OF 'T2_PK' (UNIQUE) (Cost=1 Card=5000)

#



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Mar 05 2004 - 06:29:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US