Re: Which index is used when multiple are valid

Daniel Roy wrote:
>
> 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

Under some circumstances the time the index is created will be used in RBO tie break situations.

There's quite a nice description in "The Search for Intelligent Life in the Cost-Based Optimizer" by Tim Gorman. I have a feeling I found it on the Hotsos site, but I'm sure a good Google would turn it up. Received on Mon Nov 03 2003 - 12:00:11 CST