Re: Insert w/o redo log , Oracle 8i

Hi!

Few important issues are missing Roelof's post:

1. The hint actually is /*+ APPEND */
2. Direct path insert (APPEND) only works for insert ... select, not for a conventional insert with valueset.
3. You need to include nologging hint to insert command (in 9i) or alter table/tablespace to nologging to prevent the changes from logging.
4. When using APPEND, Oracle will not allocate new extents for insert, it just uses blocks that have never been used - blocks above HWM. Of course when extent is full, then next one is used or allocated.
5. create indexes after you have loaded data, otherwise redo for index entries will be generated anyway and nologging doesn't help here.

Tanel.

"rs" <info_at_bytelife.com> wrote in message news:3ec3e666$0$45382$1b62eedf_at_news.wanadoo.nl...
> No, but .... try the
> insert /*APPEND */
> hint and you'll be surprised how much faster it is.
>
> And "appends" data at the end of your table- no problem because
> you just truncated it so you'll start filling it at the start.
>
> On a filled table it will allocate new extents instead of
> reusing free space from deleted row... so don't do a
> "delete" followed by insert /*APPEND */
>
> Read up on it - works great.
>
> cheers
> Roelof Schierbeek
>
> R.Schierbeek, DBA
>
> Markus Kunze <markus.kunze_at_sdm.de> schreef in
> > Hi all,
> > is it possible to do an insert-statement without writing a redo log?
> > that means to switch logging off during a transaction
> >
> > I use "truncate table" instead of "delete * from table".
> > But when it comes to refill the truncated tabel i use the insert
statement
> > and by doing this, i write a redo log, that i actaully don't need.
> > cheers and thanks
> >
> > Markus
>
>
Received on Fri May 16 2003 - 03:28:31 CDT