From DWILLIAMS@LIFETOUCH.COM Wed, 12 Dec 2001 14:06:33 -0800 From: DENNIS WILLIAMS Date: Wed, 12 Dec 2001 14:06:33 -0800 Subject: RE: select data using trigger Message-ID: MIME-Version: 1.0 Content-Type: text/plain Kevin Loney, the noted Oracle author recently presented some studies he did on this issue at our TCOUG meeting. In a nutshell, his results were: - If the table has no indexes and you create one, the insert rate will drop to about 30% of initial. - As each successive index is added, the insert rate will decrease. However the amount of decrease will be smaller. - Bottom line: If your table has one index, adding another will slightly decrease performance. If your table already has 15 indexes, one more index ain't gonna matter much. - There is no "threshold" where performance suddenly drops off. My apologies to Kevin if I have misrepresented his findings. He presented much more analysis and I really appreciated his work. I look forward to their inclusion in a future book. Dennis Williams DBA Lifetouch, Inc. dwilliams@lifetouch.com -----Original Message----- Sent: Tuesday, December 11, 2001 9:25 AM To: Multiple recipients of list ORACLE-L Well ... According to a document on Metalink .. it is pretty safe to assume the cost of a DML action on an index as 3 times of that of on the table. So, if inserting one record costs you one unit, on your table with 15 indexes it would be (1 * 1 table) + (3 * 15 indexes) = 46 units. And this is for each row ... HTH Raj ______________________________________________________ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: DWILLIAMS@LIFETOUCH.COM 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@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).