Re: bypass rollback for large insert statements

In article <39536CB2.A4B7B25_at_italdata.it>,   Gennaro Napolitano <gennaro.napolitano_at_italdata.it> wrote:
> Hy
>
> I think you are talking about using the direct path loading,

Direct path loading is available only via SQL*Loader. The 'append' hint is different.

> if this is
> true you need to use the append HINT as shown into the following code
> block:
>
> INSERT /*+append */ INTO table_name (....)
> SELECT (...) FROM table_name
>

This will only avoid searching the freelist for available entries in the table by starting the inserts into the first empty data block. This does NOT eliminate creation of rollback entries, although it will perform more quickly than a garden-variety insert.

> or furthermore you can use the clause NOLOGGING to avoid writing redo
> information to the redo log buffer:
>
> INSERT /*+append */ INTO table_name (....) NOLOGGING
> SELECT (...) FROM table_name
>

NOLOGGING is an option to INSERT in Oracle 8.x but not for Oracle 7.3.4 which is the version running against the application. The only way to disable redo logging in 7.3.4 is to create the object being inserted into as UNRECOVERABLE. The object has to have been created as an unrecoverable object to bypass the redo log entries for all transactions against that object. That STILL does NOT eliminate rollback entries for the transaction.

> Hope this helps
>
> Ciao Gennaro
>
> jim shorts wrote:
>
> > I have an application that is updating a very large Oracle 7.3.4
> > database. at the end of the application are some insert statements
 that
> > affect about 100K rows of data. i've tested the statements and I'm
 sure
> > they complete successfully. I'm trying to find out if their is a
 clause
> > I can add to the insert statements that will bypass the rollback
> > segments and just update the database. For example:
> > insert into tablename (col1, col2, col3) values (data, data+1, data)
> > norollback;
> >
> > any input would be greatly appreciated. thanks.
>
>

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.