RE: Anyone seen any independent performance .....

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Thu, 05 Sep 2002 12:23:29 -0800
Message-ID: <F001.004C9042.20020905122329@fatcity.com>

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.
dwilliams_at_lifetouch.com

-----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: Michael.Johnson_at_oln-afmc.af.mil

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: ListGuru_at_fatcity.com (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: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------