Michael 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 never 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 Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- Sent: Thursday, September 05, 2002 1:08 PM To: Multiple recipients of list ORACLE-L studies on what happens to performance as indexes keep getting added to tables while inserting data ?? Thank You in advance for your time. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
