Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: bypass rollback for large insert statements
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