drop table t1; create table t1 nologging 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). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 21, 2004 6:19 AM > Hello again, > > I've just been reading a report for one of our systems and it says that Oracle 8.1.7 will only use the first 15 indexes created. Any index created after the 15th will be ignored unless specified via a hint. Is this correct? I haven't heard of this before. > > > TIA, > > Leng. > > ---------------------------------------------------------- > Leng Kaing > Email: [EMAIL PROTECTED] > Phone: +61-3-9203-7589 > Mobile: +61-417-371-348 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Kaing, Leng > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
