Re: Insert from temporary table

From your description, your use of the
temporary tables sounds perfectly correct. Any COMMIT will clear all data from both of the temporary tables at the same time, but your code uses just one table, then
commit, then uses the other, then commits, so you should NOT be losing data.

Since you have to use permanent tables, and have the luxury of being able to do a TRUNCATE, you can actually make things go faster than using a temporary table.

Create the tables as NOLOGGING, then do
the insert with the /*+ APPEND */ hint. In this way you will get a nologging insert on the table, the truncate will give you a nologging elimination of the data, and you won't have the overhead of the seg$ fet$ uet$ (or bitmap if you are using locally managed tablespaces) allocation that occurs with temporary tables.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Tom Vanstraelen wrote in message <8o0gbn$a2t$1_at_nnrp1.deja.com>...


>In article <966675625.11839.0.nnrp-07.9e984b29_at_news.demon.co.uk>,

>  "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>>

>> Can you clarify a couple of points -

>>

>> Is this using 8.1 with 'create global temporary table' ?

>> Are the GTTs created with 'on commit preserve' or

>> 'on commit delete' rows ?

>

>Yes, it is Oracle 8.1.6 and the tables are created with 'on commit

>delete'.

>

>> Your two descriptions are slightly different - with

>> GTTs you appear to do just one commit at the end

>> of all the processing; with the normal tables you

>> 'truncate after every commit' - i.e. implying you

>> do more that one commit - excess commits

>> with GTTs could be why you 'lose' rows.

>

>Sorry for the confusion. Yes, I do more than 1 commit but ...

>

>

>I have two different global temporary tables. So I fill up the first

>temporary table, select from that first temporary table and insert

>those rows in the first destination table. Then I do a commit which

>should remove all the records from the temporary tables. After the

>commit the second temporary table is filled up, select on that second

>temporary table and insert the results into second destination table.

>So here I do a second commit.

>

>I changed the temporary tables to 'normal' tables and placed a TRUNCATE

>TABLE before the two commits. I think this approach is identical to the

>first one. Here I get the desired results.

>

>Maybe I should only use 1 commit with temporary tables ?

>

>Greetings,

>

>Tom at Vanstraelen . net

>

>> --

>>

>> Jonathan Lewis

>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>

>> straelet_at_my-deja.com wrote in message <8ngvju$vr0$1_at_nnrp1.deja.com>...

>> >Hi all,

>> >

>> >I'm working on a DWH project and I have to move data from table A to

>> >table B while doing some lookups on the data. I use a temporary table

>> >to put all the records in from A and insert data into table B by

 doing


>> >a SELECT GROUP BY on the temporary table so that all the data is kind

>> >of compressed (many records have the same keys). Finally I commit so

>> >that the data in the temporary table is deleted.

>> >

>> >This proces is repeated a few times, every time another set of

 records


>> >is moved from table A to table B.

>> >

>> >The problem is that the data inserted in table B is wrong, the amount

>> >of records is almost right, but a few records are missing.

>> >

>> >If I change the temporary tables to normal tables and I add a

 TRUNCATE


>> >TABLE in my code whenever I COMMIT, the numbers of records inserted

 in


>> >table B are right.

>> >

>> >Can it be that the data in the temporary table is still being deleted

>> >while I already insert new data into it ? Or is there something else

 I


>> >should know when using temporary tables ?

>> >

>> >Kind Regards,

>> >

>> >Tom at Vanstraelen . net

>> >

>> >

>> >Sent via Deja.com http://www.deja.com/

>> >Before you buy.

>>

>>

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.