Re: Temporary table use and the redo log

From: Jonathan Lewis <>
Date: Sun, 13 Sep 2009 12:15:44 +0100
Message-ID: <>

"Randolf Geist" <> wrote in message On Sep 11, 7:05 pm, "Jonathan Lewis" <> wrote:
> But there are various bugs in different versions of Oracle that
> cause problems. Most versions of 9i, for example, lose the
> bulk processing optimisation of insert as select;


interesting, can you elaborate a bit about the "bulk processing optimisation" bug in 9i? Is this specific to Global Temporary Tables? What does "lose" the bulk processing optimisation mean? Fall back to conventional DML or reduced performance comparable to single row/PL/ SQL bulk processing, similar to the DML error logging effect?


Oracle related stuff blog:

SQLTools++ for Oracle (Open source Oracle GUI for Windows):


create global temporary table t1 (
 n1 number,
 n2 number,
 v1 varchar2(30),
 v2 varchar2(30),
 constraint t1_pk primary key (n1,n2)
on commit preserve rows

insert into t1
 rownum <=1000

Running on (where the bug is fixed) the insert reports about 38 redo records and 32KB of redo size on a global temporary table (compared with about 65 and 100KB on a normal table). This is due to the way that Oracle can pack multiple changes to a single block into a single undo record and redo change vector).

On, this test reported 2,000 redo records and about 350KB or redo generated because that packing optimisation was missing. According to my notes, this bug was fixed in (so my comment about "most" versions of 9i was wrong) and (The optimisation was in 8i).


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Sun Sep 13 2009 - 06:15:44 CDT

Original text of this message