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

dba_222_at_yahoo.com (Roger Redford) wrote in message news:<a8c29269.0301060935.4647f8fd_at_posting.google.com>...
> 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

Normally Oracle can and will use only one (normal btrieve) index per table per step. If a table has to be accessed more than once to solve a query then the second step might use a different index that the first step. However, the CBO has the ability to merge btrieve indexes, but it is rarely beneficial enough for the optimizer to make this choice. See the AND_EQUAL hint.

When bitmap indexes exist on the table and the where clause refers to two or more separately indexed columns then I believe that bitmap index merges are chosen by the CBO fairly often. But then bitmap indexes are generally only suitable for warehouse applications.

IMHO -- Mark D Powell -- Received on Mon Jan 06 2003 - 18:16:32 CST