RE: 10gR2 - direct path load with enabled indexes - lots of undo generated from indexes
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <kutrovsky.oracle@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 May 2006 16:02:44 -0400
Basically, index DML operations always log no matter what the
documentation says.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Christo Kutrovsky
Sent: Tuesday, May 30, 2006 3:49 PM
To: oracle-l
Subject: 10gR2 - direct path load with enabled indexes - lots of undo
generated from indexes
Hello All,
We're doing a direct path to an empty table via:
insert /*+ APPEND*/ into TABLE as select * from other_schema.SAME_TABLE;
The table has a primary key and a few extra indexes. The goal is to
perform the entire operation in nologging mode. The table and all
indexes are "nologging".
The problem we have is that there's undo (rollback) generated for the
indexes, which I dont understand. Why is there undo generated for an
index built, equivalent to the size of all indexes ? Is this normal ?
The process goes as follow
- start writing into table segment
- while writing, have 1 sort area for each index on the table, and
populate it with data been written
- At this point only 1 undo record is been used
- after the table load is complete, write up the index segments (from
sort area/temp tablespace) 1 by 1, creating as much undo as the size of
the indexes.
Any ideas? It doesn't make sense to use that much undo (rollback) ?
When you do a normal index build you dont use that much undo.
--
Christo Kutrovsky
Senior Database/System Administrator
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
