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

For b-tree indexes, the path is the 'and equal' path, which applies only for single column indexes, and is a little rare under CBO because it tends to require finally balanced statistics. The hint is the AND_EQUAL hint - the limit is 5 indexes.

For bitmap indexes, there is an 'index combine' hint which does not seem to have a limit on the number of indexes. The hint is INDEX_COMBINE; the restriction to single column indexes does not apply.

Bear in mind two things:
a) Oracle can operate a "btree to bitmap" conversion

    algorithm at run time, to do an index_combine on     a table which has only b-tree indexes

b) bitmap indexes should not be created on tables

    which are subject to update of the indexed columns     unless the index can be dropped and rebuilt around     the update.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23
____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Sybrand Bakker wrote in message
<77uj1vkf5qbgg1m0ajeb9esktqj93a3td0_at_4ax.com>...


>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