Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: bypass rollback for large insert statements
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.
"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
news:39534B37.66B3_at_yahoo.com...
> ddf_dba_at_my-deja.com wrote:
> >
> > In article <39535DE8.AFFE842C_at_byteme.com>,
> > jim shorts <jim.short_at_byteme.com> 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.
> > >
> > >
> >
> > As far as I am aware there is no INSERT command parameter to shut off
> > creation of rollback during a transaction. There IS a method of
> > avoiding creation of redo log entries and that is to create the table as
> > UNRECOVERABLE (NOLOGGING in Oracle 8). The table creation is not logged
> > nor are any transactions to the table. This makes the table impossible
> > to recover after a database crash if the table has not been exported
> > prior to the incident since no record of the transactions exist in the
> > archived redo logs.
> >
> > A possible, but in my mind dangerous, solution would be to set
> > 'autocommit' on for the transactions in question; this would possible
> > reduce the amount of rollback generated since each insert would be
> > committed upon completion. This also eliminates any possibility of
> > transaction recovery (rollback) and could inadvertently invalidate data
> > if an incorrect value was inserted into a table by mistake.
> >
> > --
> > David Fitzjarrell
> > Oracle Certified DBA
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
>