Re: Insert /* +append */ always ?

From: Stephen C Ashmore <sashmore_at_neonramp.com>
Date: 6 May 2002 04:52:59 -0700
Message-ID: <4ef331c3.0205060352.97d262b@posting.google.com>

As usual Mr. Kyte is correct! (Would we have it any other way?)

I must apologize for my mis-information. I was assuming that the table was set to nologging when I stated that redo is not generated. I did not realize direct path insert would work when the table was set to logging (The default). My Bad!

This is my lesson for the day. Thanks Tom!

Per the 8i concepts manual:

---

Logging Mode
Direct-load INSERT operations can be done with or without logging of redo
information. You can specify no-logging mode for the table, partition, or index into
which data will be inserted by using an ALTER TABLE, ALTER INDEX, or ALTER
TABLESPACE command.
In Direct-load INSERT with logging. This mode does full redo logging for
instance and media recovery. Logging is the default mode. In Direct-load INSERT with no-logging. In this mode, data is inserted without
redo or undo logging. (Some minimal logging is still done for marking new
extents invalid, and dictionary changes are always fully logged.) When applied
during media recovery, the extent invalidation records mark a range of blocks as
logically corrupt, since the redo data is not logged. The no-logging mode improves performance because it generates much less log
data. The user is responsible for backing up the data after a no-logging insert
operation in order to be able to perform media recovery. There is no interaction between no-logging mode and discrete transactions, which
always generate redo information. (See "Discrete Transaction Management" on

---

Also I admit my wording was poor when I was trying to explain that blocks on the freelist would not be reused when using direct path load.

Once again from the 8i concepts manual:

---

Direct-load INSERT requires more space than conventional path INSERT, because
direct-load INSERT ignores existing space in the free lists of the segment. For
parallel direct-load INSERT into nonpartitioned tables, free blocks above the high
water mark of the table segment are also ignored. Additional space requirements
must be considered before using direct-load INSERT.

---

So to answer the original posted question: you need to be aware of the direct load features abilites and restrictions before you use it. But it
is defintely a performance booster. I use it all the time.

Steve

Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<ab1phu01fpa_at_drn.newsguy.com>...
> In article <ud8onhkf0meg1d_at_corp.supernews.com>, "Steve says...
> >
> >No.
> >The append hint does not generate redo log information.
>
> Sure it does! It is 100% safe. It is "undo" that it skips. It'll skip REDO if
>
> o you are no archivelog mode (doesn't matter in this case, no media recovery
> anyhow)
>
> o your table is nologging
>
> consider (in an archivelog mode database)
>
> ops$tkyte_at_ORA8I.WORLD> create table t as select * from all_objects where 1=0;
>
> Table created.
>
> ops$tkyte_at_ORA8I.WORLD>
> ops$tkyte_at_ORA8I.WORLD> set autotrace on statistics
> ops$tkyte_at_ORA8I.WORLD> insert /*+ append */ into t select * from all_objects;
>
> 34950 rows created.
>
>
> Statistics
> ----------------------------------------------------------
> 643 recursive calls
> 119 db block gets
> 476174 consistent gets
> 270 physical reads
> 3895776 redo size <<<<<<<<<<<<<<<<=========================
> 991 bytes sent via SQL*Net to client
> 827 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 15 sorts (memory)
> 0 sorts (disk)
> 34950 rows processed
>
> ops$tkyte_at_ORA8I.WORLD> drop table t;
>
> Table dropped.
>
> ops$tkyte_at_ORA8I.WORLD> create table t NOLOGGING as select * from all_objects
> where 1=0;
>
> Table created.
>
> ops$tkyte_at_ORA8I.WORLD> insert /*+ append */ into t select * from all_objects;
>
> 34950 rows created.
>
>
> Statistics
> ----------------------------------------------------------
> 151 recursive calls
> 116 db block gets
> 476062 consistent gets
> 24 physical reads
> 10208 redo size <<<<<<<<<<<<<==============================
> 994 bytes sent via SQL*Net to client
> 827 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 3 sorts (memory)
> 0 sorts (disk)
> 34950 rows processed
>
>
>
> >So the only time you should use it is when you can afford
> >to loose the data in the table.
>
> AND the table is NOLOGGING needs be added here. APPEND does not by itself
> bypass redo -- undo, and the related redo for the undo, YES. REDO -- no.
>
> In NOarchivelog -- append bypasses redo, but that doesn't matter.
>
> > Once your next hot or cold
> >is taken than you off course are no longer at risk.
> >
> >So if you are loading right before a backup the risk is minimal. The longer
> >you wait the bigger the risk.
> >
> >Also you need to be aware that when you use the append hint the data is
> >inserted beginning at the high water mark of the table. So you have just
> >deleted a bunch of data those blocks will not be reused. If the tables was
> >just truncated than it will start loading at the beginning.
> >
>
> I don't understand the "so you have just deleted a bunch of data those blocks
> will not be reused". I think you were trying to say "the direct path insert
> will not reuse blocks that are on the freelist"
>
>
>
> >Hope this helps.
> >
> >Stephen C. Ashmore
> >Brainbench MVP for Oracle Administration
> >http://www.brainbench.com
> >
> >Author of 'So You Want to be an Oracle DBA?'
> >
> >"R Chin" <rchin_at_panix.com> wrote in message
> >news:ab1l09$94b$1_at_reader1.panix.com...
> >> Should I ALWAYS do
> >> Insert /* +append */ into select .....
> >> as opposed to
> >> Insert into select...... ???
> >>
> >> Under what circumstances should I NOT use the hint ?
> >>
> >> Thanks
> >>
> >> Rob
> >>
> >>
> >>
> >
> >
Received on Mon May 06 2002 - 06:52:59 CDT