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

Using two indexes to scan a table does not make sense - Oracle's CBO will always use one index/full table to read the table.

However, for a complex query, or same table joins, different indexes might be used for first and second table if say , search criteria on first table is different than the join criteria on second table.

Hope this helps.

SA

"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message news:3e1c7454_2_at_mk-nntp-1.news.uk.worldonline.com...
> "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 Fri Jan 17 2003 - 12:53:12 CST