Re: Insert /* +append */ always ?

"Marc Blum" <marc_at_marcblum.de> wrote in message news:9gvhfu86e9s1o74l6s4u5di0bc0c21dedq_at_4ax.com...
> On 1 Jun 2002 04:37:07 -0700, lauferb_at_adit-tec.com (Boaz Laufer) wrote:
>
> >"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
> >
> >the thing is, INSERT with APPEND only inserts the rows AFTER THE HIGH
> >WATER MARK!
> >This means that it doesn't use blocks that has data or had data in the
> >past that was deleted! (even if there's enough space to add more rows)
> >and this can cause a problem, especially if you delete the rows
> >afterwards and than insert rows again. the old blocks wont be used and
> >the table will get bigger and bigger...
> >(I had this exact problem at one of our customers!!!!)
> >
> >Regards,
> >Boaz
> >
> >____________________
> >Boaz Laufer.
> >Oracle DBA and Consultant.
> >Adit-Information Technologies.
> >Adanet business group.
>
> Hi,
>
> a furthermore, your INSERTs won't make their way into the redo logs,
making
> recovery unpossible.
>

True, somewhat to my surprise: I had assumed that the 'nologging' attribute needed to make an appearance for this to be the case, but the following demonstrates that not to be the case:

SQL> create table blah as select * from emp; Table created.

SQL> insert into blah select * from blah; 14 rows created.

Statistics

       1200 redo size

SQL> insert /*+ APPEND */ into blah select * from blah; 28 rows created.

Statistics

         52 redo size

(non-key statistics have been removed, obviously).

Odd then that the Oracle training material always demonstrates the direct load insert with the nologging attribute being used -when clearly it's utterly redundant.

Incidentally, it occurs to me that having performed a nice, fast direct load you could do an 'alter table blah move;' to make the freshly inserted records recoverable, since that does a fresh batch of inserts all of which would be logged. Just a thought.

Regards
HJR
> Your quesion shoud be:
>
> When to use the APPEND-hint?
>
> - when recovery is not requiered or
> - when moving real big datasets around AND backup takes place immediately
> afterwards
>
> You should use it as a tool to perform mass data tasks in a timely manner.
For
> OLTP it's a nono.
>
>
> Marc Blum
> mailto:marc_at_marcblum.de
> http://www.marcblum.de
Received on Sun Jun 02 2002 - 14:25:44 CDT