Re: how come oracle is using only one of my indexes?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 23 Jul 2001 18:28:49 -0700
Message-ID: <9jij0h01h29@drn.newsguy.com>

In article <tlp1jok1p2md8f_at_xo.supernews.co.uk>, "Ian says...
>
>
>"DrowningPeople" <turboridelover_at_yahoo.com> wrote in message
>news:dc7ccafd.0107231136.5952d415_at_posting.google.com...
>> I have a table with 12 columns, and there is an index on each of them.
>> Now I have a query that involves all the 12 columns. However, oracle
>> only makes use of one of the indexes. Why is it? What should I do to
>> make oracle uses more of my indexes?
>>
>> Thank you.
>
>As I understand it Oracle can only use one index to access a table and must
>choose (or be told in a HINT) which one to use. However, an index can be
>based on multiple columns which may allow a more efficient query.
>
>Hth,
>
>Ian Brown
>
>

more then one index can be used, just depends on what question you use. In addition to this simple example, bitmap indexes are frequently used in great numbers (and/or them together). Additionally, indexes can be joined together to produce a result. we can definitely make use of more then one index when is make sense...

ops$tkyte_at_ORA8I.WORLD> create table t ( x int, y int, z int );

Table created.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> create index t_idx1 on t(x);

Index created.

ops$tkyte_at_ORA8I.WORLD> create index t_idx2 on t(y);

Index created.

ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> set autotrace on
ops$tkyte_at_ORA8I.WORLD> select * from t where x < 5 or y < 5;

no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 CONCATENATION

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   3    2       INDEX (RANGE SCAN) OF 'T_IDX2' (NON-UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   5    4       INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE)

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/

Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Mon Jul 23 2001 - 20:28:49 CDT