Re: 10gR2 - direct path load with enabled indexes - lots of undo generated from indexes
- From: "goran bogdanovic" <goran00@xxxxxxxxx>
- To: kutrovsky.oracle@xxxxxxxxx
- Date: Thu, 1 Jun 2006 17:15:54 +0200
Hi Christo,
Every undo is protected by redo.
Index can be created with NOLOGGING i.e the initial creation of index
data will not be logged but any reqursive SQL performed during
creation will be.
HTH.
Goran
================================================
All: Just to clarify, we are talking about undo (rollback data - for
transaction recovery). Not about redo (online redo logs for crash
recovery).
Mark,
Tried setting indexes unusable, it errors out with "Index is
unusable". The session parameter skip_unusable_indexes is by default
true in 10g, but I tried "enforcing it" - same thing.
I dont agree that it needs to protect existing tree structure as there
is none. Now that I explain this, i can see why they've done it like
that. In the case where there is an existing structure, it has to
merge my new data with that existing structure, and thus needs the
undo. But since I have no existing structure, there is nothing to
save. I guess they didn't have time to include that special case
otimization. Too bad. One would hope that a change vector for an empty
block is not that big. I wonder what they are doing. Why do they need
as much undo as the whole index, when the existing structure is empty.
Looking at the docs, it does say that you have to drop the indexes
(not set them unusable).
To summarize:
- No special case (empty structure) optimization for index undo generation
- No way to "disable" indexes instead of droping
If the special case was there, then I would've build the indexes on
the fly instead of re-reading the whole table for each index to be
rebuild.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
