Re: How many indexes can 9i use on one table in a single query?

On 6 Jan 2003 09:35:05 -0800, dba_222_at_yahoo.com (Roger Redford) wrote:

>Dear experts,
>
>I'm not sure where I might find this documented, so I
>will ask all of you.
>
>You can add a number of indexes to a table.
>
>But in my experience with Explain plan, I don't recall
>more than one index being used to scan a table
>in the same query. You would see INDEX RANGE SCAN
>and the name of the table and index. I don't recall ever
>seeing another index being used on the same table.
>
>What is correct in 9i? If you have more than one index
>in 9i, for one query, how many of those indexes can Oracle
>actually use at once?
>
>
>Thanks

Oracle uses the most selective index, so usually one. Oracle can merge indexes, and there is a hint to accomplish this. You would need to search in the list of hints in the doco.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Jan 06 2003 - 15:46:07 CST