Re: Which index is used when multiple are valid

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 3 Nov 2003 18:38:16 GMT
Message-ID: <bo67an$17cs1i$1@ID-82536.news.uni-berlin.de>

> 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        

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Mon Nov 03 2003 - 12:38:16 CST