Re: Which index is used when multiple are valid

Now - given that its the RBO, you need
to see if the choice of index changes if you reverse the order of the predicates.

Then you need to repeat the whole thing, dropping and recreating the table, but
creating the indexes in the opposite order and seeing if ind_a gets used. And don't forget to check the object IDs to test
whether it is the index with the higher
object ID that is used, rather than the
index that is created later, as object IDs can be re-used in more recent versions
of Oracle.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message
news:bo67an$17cs1i$1_at_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