Re: Temporary table use and the redo log

"Randolf Geist" <mahrah_at_web.de> wrote in message news:62314fc4-8800-40e8-888a-78d8aa4fa7bb_at_x37g2000yqj.googlegroups.com... On Sep 11, 7:05 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> 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;

Jonathan,

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?

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/

Randolf:

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
select
 trunc(rownum/20),
 rownum,
 to_char(rownum),
 object_name
from
 all_objects
where
 rownum <=1000
;

Running on 9.2.0.8 (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 9.2.0.4, 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 9.2.0.6 (so my comment about "most" versions of 9i was wrong) and 10.1.0.4. (The optimisation was in 8i).

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html