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

Home -> Community -> Usenet -> c.d.o.server -> Re: bypass rollback for large insert statements

Re: bypass rollback for large insert statements

From: Nuno Souto <nsouto_at_nsw.bigpond.net.au.nospam>
Date: 2000/06/29
Message-ID: <395b2af8.4434423@news-server>#1/1

On Wed, 28 Jun 2000 12:58:06 -0700, "Steve McDaniels" <steve.mcdaniels_at_sierra.com> wrote:

>Where is this "APPEND" hint documented?
>
>I can't find it in any of my manuals, at least not in the HINTS sections.
>
>This would be way too cool: INSERT /*+ APPEND */ into table...
>if it used the "direct" mechanisms.
>
>

Let's clear something here:

An INSERT does NOT use up rollback segments like an update unless one or both of:

1- The table needs another segment, in which case the dictionary spacemap table(s) update will be in rollback segment.

2- The table has indexes and you didn't drop them before the big insert, in which case the index blocks changed will be sent to rollback.

Case 1 you can't do anything about, even with direct loads.

Case 2, you can use DIRECT loads with the APPEND stuff. However, there is a snag: in order for APPEND to work, the table has to be in direct load mode. What happens here is that the indexes are marked disabled and the INSERT /*+ APPEND */ goes through. Then at the end, you still have to re-build your indexes before they can be used again.

So you have a problem anyway if table has indexes. Up to you to choose between a drop index/insert/re-create index or a disable index/insert/rebuild index.

Cheers
Nuno Souto
nsouto_at_nsw.bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Thu Jun 29 2000 - 00:00:00 CDT

Original text of this message

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