Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle 8.1.7 can only use the first 15th indexes?

Re: Oracle 8.1.7 can only use the first 15th indexes?

From: Kaing, Leng <>
Date: Wed, 21 Jan 2004 22:34:32 -0800
Message-ID: <>

Hi Jonathan,

Brilliant example. Thanks very much for this. (And yes, I meant the first 15th for each index, not the first 15th for the entire database!)

And yes, I do think that 15 indexes is a bit excessive but I can't help it at the moment (3rd party, packaged application...)

Does this mean that I'm reading another myth? Couldn't confirm it on metalink.



 From: "Jonathan Lewis" <>  Date: Wed, 21 Jan 2004 07:20:30 -0000
 Subject: Re: Oracle 8.1.7 can only use the first 15th indexes?

drop table t1;

create table t1
pctfree 50
pctused 50
as select

 1 n01,
 1 n02,
 1 n03,
 1 n04,
 1 n05,
 1 n06,
 1 n07,
 1 n08,
 1 n09,
 1 n10,
 1 n11,
 1 n12,
 1 n13,
 1 n14,
 1 n15,

 rownum n16,
 lpad(rownum,10) v1
from all_objects
create index i01 on t1(n01);
create index i02 on t1(n02);
create index i03 on t1(n03);
create index i04 on t1(n04);
create index i05 on t1(n05);
create index i06 on t1(n06);
create index i07 on t1(n07);
create index i08 on t1(n08);
create index i09 on t1(n09);
create index i10 on t1(n10);
create index i11 on t1(n11);
create index i12 on t1(n12);
create index i13 on t1(n13);
create index i14 on t1(n14);
create index i15 on t1(n15);
create index i16 on t1(n16);

analyze table t1 estimate statistics;

set autotrace traceonly explain;

select v1 from t1 where n16 = 99;

set autotrace off

The execution path uses I16 on my system.

It would be possible to produced test cases that failed to use the 16th index, of course, and some of them could look quite convincingly as if the 16th index should be used. But it only takes one counter-example ...

(I assume the report intended to say the first 15
indexes on a specific table, 'cos the data dictionary alone has rather more than 15 indexes).


Jonathan Lewis

Leng Kaing
Phone: +61-3-9203-7589
Mobile: +61-417-371-348
Please see the official ORACLE-L FAQ:
Author: Kaing, Leng

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Jan 22 2004 - 00:34:32 CST

Original text of this message