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

Brian Peasland <oracle_dba_at_qwest.net> wrote in message news:<3B84196A.355F302D_at_qwest.net>...
> > AFAIK, Oracle will neever use more than one index on one table in one
> > query.
>
> Correct!
>

Wrong! (In some cases :)

> > It is much better to fetchs the rows via one index scan and then
> > filter those who do not satisfy the other clauses.
>
> That's exactly why you want to use the index that has the most distinct
> values.
>
> HTH,
> Brian

Try this to test it out:
CREATE TABLE t (a NUMBER, b BUMBER, c NUMBER); INSERT INTO t values (1, 2, 3);
INSERT INTO t VALUES (2, 3, 4);
commit;
CREATE INDEX deleteme1_idx ON t (a);
CREATE INDEX deleteme2_idx ON t (b);
set autotrace on
SELECT * FROM t WHERE a = 1 AND b = 2;

Here is the actual result:

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'

   2    1     AND-EQUAL                  
   3    2       INDEX (RANGE SCAN) OF 'DELETEME1_IDX' (NON-UNIQUE)
   4    2       INDEX (RANGE SCAN) OF 'DELETEME2_IDX' (NON-UNIQUE)

*******************

Now: My comment on the original question: The reason why Oracle is not merging indexes is that the CBO has determined that single index lookup will be cheaper than "multiple index lookup and merge" for the users query. It can be because of many reasons:
Unique index lookups are always preferred. So if I make (for above example) a unique index on "a" then oracle won't do an index merge. Also if I analyze the table then based on the statistics gathered Oracle might not do an index merge. There can be many more reasons.

Anurag

... corrections always welcome Received on Wed Aug 22 2001 - 22:11:07 CDT