Re: Which index is used when multiple are valid

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Mon, 03 Nov 2003 23:17:15 GMT
Message-ID: <%lBpb.32103$Q9.27514@nwrddc02.gnilink.net>

"Daniel Roy" <danielroy10junk_at_hotmail.com> wrote in message news:3722db.0311031439.5e69a927_at_posting.google.com...
> 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!
--snip--

Well .. the rebuilt theory is what could not survive the counter example. However, the last one created still holds.

When an index is rebuilt, its create date does not change. Only its modified time changes. You can verify this by issuing a query as below:

select name, ctime,mtime from sys.obj$ where name in ('idx1','idx2');

Anurag Received on Mon Nov 03 2003 - 17:17:15 CST