Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: 10gR2 - direct path load with enabled indexes - lots of undo generated from indexes

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

From: goran bogdanovic <goran00_at_gmail.com>
Date: Thu, 1 Jun 2006 17:15:54 +0200
Message-ID: <6d0a3ba80606010815k2933f4b1rde15cd55cd7dfb28@mail.gmail.com>


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

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.

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 01 2006 - 10:15:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US