Re: oracle can ignore hints

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 5 Mar 2004 12:30:27 -0000

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


----- Original Message ----- 
From: "Powell, Mark D" <mark.powell@xxxxxxx>

: 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: