Re: bypass rollback for large insert statements

From: <ddf_dba_at_my-deja.com>
Date: 2000/06/23
Message-ID: <8j0dqu$fvh$1@nnrp1.deja.com>#1/1

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.

Received on Fri Jun 23 2000 - 00:00:00 CDT