Re: 10gR2 - direct path load with enabled indexes - lots of undo generated from indexes

  • From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
  • To: "Christo Kutrovsky" <kutrovsky.oracle@xxxxxxxxx>
  • Date: Wed, 31 May 2006 16:41:16 +0200

Thanks Dimitre I read the who whole thread.

Still the following two items puzzle me from a technology point of view.
1. why is there no special case where the index is empty at the start
of the transaction
2. why does it take as much undo space as the whole index, to undo a
merge of something big into something non-existant.


I don't know.
Found this explanation on asktom.


From Tom Kyte (http://tinyurl.com/rnoco):

<quote>

Think about INSERT /*+ APPEND */. Since it writes to the table
solely above the HIGH WATER mark, we do not need to generate UNDO for each and
every row added. The reason? In order to UNDO the insert, all we need to do is
"forget" we loaded the data in the first place, it was creating ONLY new blocks
-- blocks that were not yet really part of the table. To undo that -- we just
"forget" we did it in the first place -- blocks never become part of the table,
data structure is 100% intact.

But, consider an index on that same table -- if it is enabled during the load -- well, UNDO MUST be generated as we merge the data into it -- why? because we
are modifying the existing blocks of a complex data structure, we MUST be able
to recover that structure in the event of failure.

So -- you have the ability to disable the index, do the load, rebuild the index
(no undo for that, skip redo if you like).

</quote>


Regards
Dimitre 

--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2006