I believe Kevin gave that presentation at OpenWorld -- either last year or the year before. His paper is available for download on the TUSC site, as he works for TUSC.
--- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Naveen - > He provided figures, and they are on the handout that is somewhere > in my > office. Maybe I'll run across it someday, or even better, maybe he'll > publish his results. Here are a few more details that I posted to > this list > earlier. > Kevin Loney (author of Oracle DBA Handbook) has performed index > performance tests and presented a paper at our Twin Cities Oracle > User's > Group (http://www.tcoug.org). I don't know if his paper is on that > site or > if Kevin has posted it somewhere or if he will included his findings > in a > future book. > His results (from memory) was that there weren't any big > surprises. Say > it takes 1 hour to load a table with no indexes on it. If you put 1 > index on > that table, load time will increase about 20% to maybe 1hr 12 minutes > (depending on how many columns are indexed, etc.). If we add a second > index, > load time will again increase, but by a smaller amount than for the > first, > maybe to 1 hr. 23 minutes. And so it goes. By the time we reach 20 > indexes, > adding a 21st index may add only 3 or 4 minutes to our load time. > There > didn't seem to be any point where adding one more index would throw > load > times into a black hole and double load times or something like that. > Kevin also tested whether the size of the index mattered. There > were > points where say, the 100,000th row caused index performance to > suddenly > drop, probably due to factors like adding a newer blevel. However it > was > almost impossible to predict this point ahead of time. > My conclusions: > - Dropping indexes speeds inserts. > - If you have a single index on a table, adding a second index > is > costly. > - If the table already has 20 indexes, one more isn't going to > have a > noticeable effect. > > > Dennis Williams > DBA, 40%OCP > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -----Original Message----- > Sent: Saturday, October 26, 2002 3:43 AM > To: Multiple recipients of list ORACLE-L > > > Dennis, > > Did he publish any figures? I mean, it seems common-sense that adding > the > first index will hurt but adding 11th index to a table won't hurt > that much. > > As stephane pointed out, cost of an index is 2.5 times more than the > cost of > insert in a non-indexed table. > > So assuming cost is 1, than cost with 1 index will be 3.5 as 250% > increase. > > Cost with 5 indexes should be 13.5 and cost with 6 indexes will be 16 > less > than twenty percent increase. > > Since the addition cost is constant for every index added, the > percentage > increase in cost (and also maybe time) will be lower and lower. > > Am I right or missing something? > > Regards > Naveen > > -----Original Message----- > Sent: Friday, October 25, 2002 11:45 PM > To: Multiple recipients of list ORACLE-L > > > Stephane > You mentioned "each additional index costs about 2.5 times the > cost of > inserting into a non-indexed table". I just wanted to point out that > Kevin > Loney has done some performance tests involving the number of > indexes. I > don't know if he has published these anywhere. In a nutshell, the > results > were that a single index really hurts insert performance, and each > additional index increases the hurt, but by a decreasing amount. The > conclusions were: > - If you can drop all indexes, that will really help inserts. > - If you have one index, adding a second index will really hurt, > but not > as bad. > - If the table already has 15 indexes, adding one more index > probably > won't be noticed. > > Dennis Williams > DBA, 40%OCP > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -----Original Message----- > Sent: Friday, October 25, 2002 8:04 AM > To: Multiple recipients of list ORACLE-L > > > Rahul wrote: > > > > List, > > i have two heavily inserted tables, the structures are same. > > currently these tables reside on separate disks, can i increase the > > performance > > of inserts if i create these tables in a cluster ? as a cluster > would > force > > the rows of both the tables > > to be physically close on the disk ! > > > > regards > > -rahul > > > > Ora 7.3 on AIX > > > > You would also increase contention ... I'd rather try to augment the > number of free lists, and, if you are lucky enough not to access your > indexes in RANGE SCAN mode, to create them as REVERSE. > Beware of indexes, by the way, each additional index costs about 2.5 > times the cost of inserting into a non-indexed table (in terms of > logical blocks). > > -- > Regards, > > Stephane Faroult > Oriole Software > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Stephane Faroult > 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.com > -- > Author: DENNIS WILLIAMS > 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.com > -- > Author: Naveen Nahata > 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.com > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > === message truncated === __________________________________________________ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
