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

There’s actually more to it than that. For every block in Oracle that changes, there’s:

 

1. Generation of redo on the upcoming undo block change

2. An undo (i.e., rollback segment) block change

3. Generation of redo on the upcoming data block change

4. A data block change

 

These four steps happen for every index and data block change. So, inserting into a table with n indexes will motivate on the order of (1+n)*2 units of redo to be written to your redo stream, and (1+n)*(1+1) blocks of undo and data to be written through your DBWR (actually more, because index leaf node insertions sometimes generate recursive SQL that propagates changes upward in the B*-tree, each block of which also generates redo and undo).

 

-----Original Message-----
From: root@fatcity.com [mailto:root@fatcity.com] On Behalf Of Steve Perry
Sent: Thursday, September 05, 2002 1:53 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Anyone seen any independent performance .....

 

----- Original Message -----

From: Jamadagni, Rajendra

To: Multiple recipients of list ORACLE-L

Sent: Thursday, September 05, 2002 1:21 PM

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

 

I don't remember where but the results of the study were like following ...

if the cost of inserting one row to a table is 1 unit and if you have 5 indexes on the table than total cost of inserting 1 row to the table is

     1   (cost of inserting a row)
 + 3*5 (5 indexes)
---------
   16  

So total cost is about 16 units.

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!

 

-----Original Message-----
From: Johnson, Michael [mailto:Michael.Johnson@oln-afmc.af.mil]
Sent: Thursday, September 05, 2002 2:08 PM
To: Multiple recipients of list ORACLE-L
Subject: Anyone seen any independent performance .....

 

studies on what happens to performance as indexes keep getting added to tables while inserting data ??

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.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).