Re: Strange Performance Behavior on INSERT INTO SELECT statement...

Dave,

These are thoughts and not backed up with solid proven experience.

NOLOGGING simply means that the database inserts are not recorded in the Redo Logs and therefore the operation is not recoverable in the event that you need to perform Media Recovery. I believe that the dictionary modifications are still logged though.

ROLLBACK logging is required with a simple insert as select because if the user fails or gets logged off, the PMON (or SMON) is still going to tidy up the connection and abort the transaction. Maybe as suggested, the amout of Rollback generated is reduced when using CREATE as SELECT.

Rgds
Andrew

Dave Cowden wrote:

> Thanks for replying.
>
> I though about that, and before I had tried the insert into, I had
> altered the table into NOLOGGING mode(see original "things I tried"):
> shouldnt that have
> eliminated the ROLLBACK logging?
>
> Reinhard Wahl wrote in message <3726DC3F.B1D952A8_at_zv.fhg.de>...
> >Hi Dave,
> >
> >it's because a CREATE TABLE AS-Statement does not write any
> >ROLLBACK-Information.
> >If it works the rows are commited, if it doesn't the table does not
> >exist.
> >So it's pretty fast.
> >But if you need to recover the database afterwards,
> >I think the table is not recoverable, but you can repeat the statement.
> >
> >Hope this helps
> >
> >Reinhard
> >
> >----------------------------------------------------
> >Please do not use my E-Mail address for advertising!
> >----------------------------------------------------
> > Hiermit widerspreche ich der Nutzung oder
> > Uebermittlung meiner Daten fuer Werbezwecke
> > oder fuer die Markt- oder Meinungsforschung
> > gemaess Par. 28 Abs. 3 Bundesdatenschutzgesetz.
> >----------------------------------------------------
Received on Wed Apr 28 1999 - 22:13:28 CDT