Re: Temporary table use and the redo log

From: <codefragment_at_googlemail.com>
Date: Tue, 8 Sep 2009 06:32:42 -0700 (PDT)
Message-ID: <e8197303-059f-49cd-92b6-7b971628ca6a_at_v36g2000yqv.googlegroups.com>

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.

It assumes a table called DeleteMe, e.g.

create table DeleteMe(
someColumn int);

and a table called OneToTen, same structure as the above, which holds 10 values. Just a quick way of building a result set of 1 million rows.

I truncate DeleteMe, drop the temporary table after a single use. Again just for expediency, I'm assuming it won;t affect the result.

What I find is looking in the archive directory I get 130M of files created for the temporary tables rows and 242M of files created for the real table rows. So not too much difference. What I've read suggests that that the redo logs should grow through the inserts into the temporary table by a much lesser degree than those into the real table. So what am I doing wrong here (no sarcastic answers please)

CREATE OR REPLACE PACKAGE MYTEST AS

	PROCEDURE MYTEST (
		pIgnore IN NUMBER);

END ;
/

CREATE GLOBAL TEMPORARY TABLE TP_MYTEST
(

        SOMECOLUMN INT
) ON COMMIT DELETE ROWS;
--) ON COMMIT PRESERVE ROWS; CREATE OR REPLACE PACKAGE BODY MyTest AS

	PROCEDURE MyTest (
		pIgnore IN NUMBER) is

BEGIN

• insert into TP_MYTEST insert into DeleteMe select 1 from onetoten t1 join onetoten t2 on 1=1 join onetoten t3 on 1=1 join onetoten t4 on 1=1 join onetoten t5 on 1=1 join onetoten t6 on 1=1;

		END MyTest;
	END MyTest;

/
Received on Tue Sep 08 2009 - 08:32:42 CDT