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

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:2687bb95.0301061616.318f9251_at_posting.google.com...
> 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 --

I agree with most of what has been said. Bitmap indexes have their place, as long as it's nowhere near me.

Regards,
Paul Received on Tue Jan 07 2003 - 14:59:03 CST