Re: bypass rollback for large insert statements

ddf_dba_at_my-deja.com wrote:
>
> 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.

I'll think you'll find that it does achieve (almost) zero rollback since the only thing needed to reset is the hwm if things go wrong.

You can prove this relatively easily - turn all your rollbacks offline and create a tiny one (say 16k maxextents 2) - your insert will still work.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse