Re: Which index is used when multiple are valid

Thanx Rene for the test. I was able to find an Oracle test-box (9.2 on AIX), and I did exactly the same steps that you performed, except that I created first ind_b, then ind_a. Oracle used ind_a instead of ind_b (it used the last one created). I then rebuilt ind_b, in the hope that it would then be used (to verify the argument of my collegue who says that the last one created or rebuilt is used), and Oracle sticked with ind_a! I still don't know which index Oracle will use in case of an index "tie" under RBO, but now at least I know that my collegue's theory couldn't survive my counter-example!

Daniel

> > Hi guys,
> > I've been asked a question for which I'm not too sure. We use Oracle
> > 9.2, with RBO (we're stuck with that choice because we use Siebel,
> > please don't go there...). One of the DBA's asserts that if 2 indices
> > are valid for a query, then the one created the latest will be used.
> > Here's an example to make things clearer:
> >
> > select * from my_table where col_a = 'A' and col_b = 'B';
> >
> > Let's say I have 2 examples on this table: ind_a(col_a, col_b) and
> > ind_b(col_b, col_a). Which one will my query use, and why?
>
>
> Daniel,
>
> This is a case for explain plan:
>
>
> create table my_table (
> col_a varchar2(10),
> col_b varchar2(10)
> );
>
> begin
> for i in 1 .. 10000 loop
> insert into my_table values(
> dbms_random.string('l',10),
> dbms_random.string('l',10)
> );
> end loop;
> end;
> /
>
>
> create index ind_a on my_table(col_a, col_b);
>
> create index ind_b on my_table(col_b, col_a);
>
>
> delete plan_table;
>
> EXPLAIN PLAN FOR
> select * from my_table where
> col_a = 'kdofifockw' and
> col_b = 'qodkfwkcka';
>
> select
> substr (lpad(' ', level-1) || operation ||
> ' (' || options || ')',1,30 ) "Operation",
> object_name "Object"
> from
> plan_table
> start with id = 0
> connect by prior id=parent_id;
>
>
>
> It returns:
> Operation Object
> ------------------------------ ------------------------------
> SELECT STATEMENT ()
> INDEX (RANGE SCAN) IND_B
>
>
> => so, it uses ind_b.
>
>
> hth
> Rene
Received on Mon Nov 03 2003 - 16:39:56 CST