Re: Temporary table use and the redo log

From: Randolf Geist <mahrah_at_web.de>
Date: Wed, 9 Sep 2009 01:30:08 -0700 (PDT)
Message-ID: <8f7154be-0b5b-41ed-a773-0ad38cf06583_at_j39g2000yqh.googlegroups.com>

On Sep 8, 3:32 pm, "codefragm..._at_googlemail.com" <codefragm..._at_googlemail.com> wrote:
> I've included an example below, yes I know the sql sucks but I've just
> copied a template file to
> get something working, no need for a package, etc.

As discussed above, if your main interest is to avoid REDO generation, you have two options:

• Use the temporary table and add the "/*+ APPEND */" hint to the insert:

insert /*+ append */ into ...

• Use a conventional table, mark it with NOLOGGING either at create time or later with ALTER TABLE ... NOLOGGING, and again additionally use the "/*+ APPEND */" hint when running the insert

Both approaches should minimize your REDO generation.

As already mentioned, you need to be aware of that:

• Enabled indexes on either form of tables will still generate REDO, UNDO, and REDO for the UNDO
• Direct-path inserts have restrictions. You can check for a successful direct-path operation by querying the modified object within the same transaction; you'll get a "ORA-12838: cannot read/ modify an object after modifying it in parallel" if you was a successful direct-path operation. If you can still access the object after the insert within the same transaction, it was a conventional DML operation.
• This also means that you can't access an object modified by directpath inserts within the same transaction which needs to be considered for your process logic. You need to commit to access the inserted the rows which means a "on commit delete rows" temporary table doesn't make sense with a direct-path insert operation, since after the commit the data will be gone.
• Direct-Path inserts always allocate new blocks above the current High Water Mark (HWM) of the segment and don't reuse any free space in already allocated blocks. So a repeated "delete + insert /*+ append */" operation will let the segment grow every time because the empty blocks won't get re-used. You would need to truncate instead of delete to reset the HWM of the segment, if your process logic allows for it due to the implicit commit performed by truncate.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ Received on Wed Sep 09 2009 - 03:30:08 CDT