RE: select data using trigger

From: Henry Poras <Henry.Poras_at_ctp.com>
Date: Thu, 13 Dec 2001 08:43:42 -0800
Message-ID: <F001.003DBE4D.20011213075032@fatcity.com>

I also heard this from Kevin and I believe he was talking about large data loads, not one or two rows. I think you need to get to the point of recursive SQL and internal index maintenance for the pain to be felt.

Henry

-----Original Message-----
Sent: Wednesday, December 12, 2001 4:56 PM To: Multiple recipients of list ORACLE-L

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_at_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_at_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_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: Henry Poras
INET: Henry.Poras_at_ctp.com

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