Re: forcing multiple indexes (9.2.0.6)
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 1 Feb 2007 11:46:12 -0000
Created on 9.2.0.8
script and plan attached
drop table t1;
drop table t2;
create table t1 (
n1 number not null,
n2 number not null,
padding varchar2(100)
);
insert into t1
select
mod(rownum,1000),
mod(rownum,1000),
rpad('x',100)
from
all_objects
where
rownum <= 3000
;
create table t2 (
b1 number not null,
b2 number not null,
small_v varchar2(10),
padding varchar2(100)
);
insert into t1
select
mod(rownum,50),
mod(rownum,50),
rpad('a',10),
rpad('x',100)
from
all_objects
where
rownum <= 3000
;
create index t1_n1 on t1(n1);
create index t2_b1 on t2(b1);
create index t2_b2 on t2(b2);
set autotrace traceonly explain
spool temp
select
/*+ ordered use_nl(t2) index_combine(t2 t2_b1 t2_b2) */
t1.n2, t2.small_v
from
t1, t2
where
t1.n1 = 500
and t2.b1 = t1.n2
and t2.b2 = 50
;
set autotrace off
spool off
set doc off
doc
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=59)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=7 Card=1 Bytes=33)
2 1 NESTED LOOPS (Cost=7 Card=1 Bytes=59)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1 Bytes=26)
4 3 INDEX (RANGE SCAN) OF 'T1_N1' (NON-UNIQUE) (Cost=1 Card=1)
5 2 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP AND
7 6 BITMAP CONVERSION (FROM ROWIDS)
8 7 INDEX (RANGE SCAN) OF 'T2_B2' (NON-UNIQUE)
9 6 BITMAP CONVERSION (FROM ROWIDS)
10 9 INDEX (RANGE SCAN) OF 'T2_B1' (NON-UNIQUE)
#
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "FreeLists Mailing List Manager" <ecartis@xxxxxxxxxxxxx>
To: "oracle-l digest users" <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, February 01, 2007 7:05 AM
Subject: oracle-l Digest V4 #32
Date: Wed, 31 Jan 2007 15:33:41 -0800 (PST)
From: cosmin ioan <cosmini@xxxxxxxxxxxxxxx>
Subject: re: forcing multiple indexes (9.2.0.6)
hello all,
I was looking in the manuals to find any hint or trace (no pun intended) of
examples of how to force multiple (single column) indexes of different tables,
**perhaps as bitmap join** as an added bonus ;-).
May not be desirable or optimal, but at least, to try them out as an exercise:
TabA, Col1, Col2 ind1, ind2
TabB, Col1, Col2 indb1, indb2
select /*+ index(A ind1 ind2) index(B indb1 indb2)*/ * from tabA,tabB
where a.col1=b.col1
and a.col2='x'
and b.col2='y'
or the index_combine(A ind1 ind2) index_combine(B indb1 indb2) .... + bitmap
join them --how !?....
I think I'm trying to outsmart the CBO and it does not like something... ;-)
reason is I'm working on a large Ora APPS implementation and apparently,
adding multiple proper indexes is the last resort.... which I agree with them,
somewhat...; and there is the clustering factor for indexes that we could
tweak, but I'm looking at the hint forcing above, at least as an exercise.
thx,
Cos
--
//www.freelists.org/webpage/oracle-l
Other related posts:
