Re: forcing multiple indexes (9.2.0.6)

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_at_freelists.org> To: "oracle-l digest users" <oracle-l_at_freelists.org> 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_at_bridge-tech.com>
> 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
>

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