Re: Oracle 8.1.7 can only use the first 15th indexes?
Ah, we've discussed this system a couple times in the past. Jared PS. Ok, ya'll move to the new list now, ya hear! Tim Gorman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/22/2004 08:24 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Oracle 8.1.7 can only use the first 15th indexes? Amen to that. I had a table with about 40 indexes on v7.0.16. I don't think that it was possible that any of them could have been ignored, because all of them were used. I can't verify that, because this system was born and died (subsequently cremated) over 10 years ago and I never thought to check while it was breathing, but like I said, all 40 or so indexes were absolutely necessary... Redesign? Well, according to the architect, this was the perfect design. Over 150 logical entities were encapsulated within this single table, which also happened to be the only table in the entire application (at least in the beginning). Appropriately enough, its name was DATA... on 1/21/04 2:44 AM, Nuno Souto at [EMAIL PROTECTED] wrote: Let's be realistic: any table with 15 indexes PROBABLY needs a little bit of a re-design exercise? ;) Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - snip (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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).
Re: Oracle 8.1.7 can only use the first 15th indexes?
Notes in-line 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 - From: Kaing, Leng [EMAIL PROTECTED] 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!) I had to ask, as once upon a time people used to say that Oracle was limited to using a maximum of 5 indexes in any one query. (Misunderstanding the manuals comments about the maximum number of indexes that could be used in the AND-EQUAL path of a single table, I believe). 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...) It's not necessarily wrong - just something to be suspicious of when you start from cold at a site. Does this mean that I'm reading another myth? Couldn't confirm it on metalink. I've never seen anything like it claimed before. Maybe it's something to do with the fact that the manuals list 15 rankings in the RBO ;) -- 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).
Re: Oracle 8.1.7 can only use the first 15th indexes?
Amen to that. I had a table with about 40 indexes on v7.0.16. I don't think that it was possible that any of them could have been ignored, because all of them were used. I can't verify that, because this system was born and died (subsequently cremated) over 10 years ago and I never thought to check while it was breathing, but like I said, all 40 or so indexes were absolutely necessary... Redesign? Well, according to the architect, this was the perfect design. Over 150 logical entities were encapsulated within this single table, which also happened to be the only table in the entire application (at least in the beginning). Appropriately enough, its name was DATA... on 1/21/04 2:44 AM, Nuno Souto at [EMAIL PROTECTED] wrote: Let's be realistic: any table with 15 indexes PROBABLY needs a little bit of a re-design exercise? ;) Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - snip (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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).
Re: Oracle 8.1.7 can only use the first 15th indexes?
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).
Re: Oracle 8.1.7 can only use the first 15th indexes?
Let's be realistic: any table with 15 indexes PROBABLY needs a little bit of a re-design exercise? ;) Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - snip (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
Re: Oracle 8.1.7 can only use the first 15th indexes?
The first 15 indexes CREATED? Joking, are they? Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
Re: Oracle 8.1.7 can only use the first 15th indexes?
Data warehouse with bitmap indexes ? But in OLTP I would assume guilty until proven innocent. 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 9:44 AM Let's be realistic: any table with 15 indexes PROBABLY needs a little bit of a re-design exercise? ;) -- 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).
Re: Oracle 8.1.7 can only use the first 15th indexes?
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. Ta, Leng. --- From: Jonathan Lewis [EMAIL PROTECTED] Date: Wed, 21 Jan 2004 07:20:30 - Subject: Re: Oracle 8.1.7 can only use the first 15th indexes? 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 -- 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).
Oracle 8.1.7 can only use the first 15th indexes?
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).
