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

Christo,
 
DML against an index always logs.  It *must* log, cause you're modifying
the index structure.  Consider that it's impossible to update an index
without updating the *existing* tree structure.  Since the *existing*
tree structure must be updated, it MUST be protected by redo.  (Note
that the preceding is not true for a heap table (no underlying index
structure to maintain) or when rebuilding an index.)
 
To make the load (nearly) completely nologging, try this:
you mention you're starting with an empty table, so, truncate table
load_it;
alter table load_it and disable the primary key.  (This will drop the
unique index, if an index was implicitly created on the table.)
Create a non-unique index for the primary key.
for each index on the table (including the new non-unique pk index) do:
   alter index index_name unusable;
Now, set: alter session set skip_unusable_indexes = true;
Now, load that data:
insert /*+ append */ into load_it select * from other_schema.load_it;
finally, for each index on the table, do:
   alter index index_name rebuild nologging;
and last, alter table load_it and enable the primary key.
 
That should get you a clean load free of (most) logging.
 
Hope that helps,
 
-Mark
 

-- 
Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 

For a successful technology, reality must take precedence over public
relations, for Nature cannot be fooled.  --Richard P. Feynman, 1918-1988

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Arul Ramachandran
Sent: Tuesday, May 30, 2006 4:07 PM
To: kutrovsky.oracle@xxxxxxxxx
Cc: oracle-l
Subject: Re: 10gR2 - direct path load with enabled indexes - lots of
undo generated from indexes


Hi Christo,
 
What Oracle version?
What is the top wait event?
Are you seeing high "log file sync" and "transaction rollback" in your
statspack report?
 


 
On 5/30/06, Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx> wrote: 

        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/ 
        --
        http://www.freelists.org/webpage/oracle-l
        
        
        




-- 
Arul

Other related posts: