Re: bypass rollback for large insert statements

ddf_dba_at_my-deja.com wrote:
>
> In article <39534BBA.7E8_at_yahoo.com>,
> connor_mcdonald_at_yahoo.com wrote:
> > 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
> >
>
> My question is: are you running 7.3.4.4 or 8.x? NOLOGGING is not
> available in 7.3.4.4 and I don't believe that the UNRECOVERABLE clause
> is available in 7.3.4.4 with respect to the INSERT statement.
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Agreed.

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

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