Skip to Main Content
  • Questions

  • Best way to copy From Global TEMP Table to an empty Standard Table

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Tom.

Asked: October 18, 2002 - 1:42 pm UTC

Last updated: November 28, 2007 - 10:39 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Q1. I have a Global Temp (GT) (ON commit Preserve Rows).
I do lots of processsing (mostly inserts, some updates)
After processing and committing, I trucate a Permanent Table (P) and want to copy GT ->P, say a million rows.
What is the quickest way of achieving that
- Drop P, CTAS P as select from GT
- Some other ?

Q2. Curiosity - there does not seem a compelling reason why TEMP tables should not persist across sessions. In which case I would not need to do the above operation (i.e GT -P). That is, I want to exploit the minimal redo generation aspect of GT but want more persistance. Is that a reasonable demand from Oracle?

thanks
Tom

and Tom said...

q1) insert /*+ APPEND */ into P select * from GT;

see
</code> http://docs.oracle.com/cd/B10501_01/server.920/a96524/c21dlins.htm#11358 <code>

for detalis on that -- it is known as a direct path insert

q2) No, we give you the tools to do minimal redo generation on a permanent table via non-logged bulk operations.

You do understand that even a GTT will generate GOBS of redo when you use UPDATE and DELETE on it (generates MINIMAL with INSERT).

So, against a permanent table you can do many of the things you are asking with bulk operations (CTAS -- doesn't have to be logged, INSERT /*+ APPEND */ -- does not have to be logged, truncate -- no log)

But just remember that with GTTs and permanent tables -- UPDATES and DELETES -- whole lotta logging going on ...

ops$tkyte@ORA920.US.ORACLE.COM> create global temporary table t1
2 on commit preserve rows
3 as
4 select * from all_objects
5 where 1=0;

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table t2
2 as
3 select * from all_objects
4 where 1=0;

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set autotrace on statistics
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into t1 select * from all_objects;

29303 rows created.


Statistics
----------------------------------------------------------
97 recursive calls
31377 db block gets
138393 consistent gets
0 physical reads
3767208 redo size
789 bytes sent via SQL*Net to client
809 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
29303 rows processed

ops$tkyte@ORA920.US.ORACLE.COM> insert /*+ APPEND */ into t2 select * from all_o bjects;

29303 rows created.


Statistics
----------------------------------------------------------
387 recursive calls
483 db block gets
138450 consistent gets
0 physical reads
3390208 redo size
773 bytes sent via SQL*Net to client
823 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
29303 rows processed

ops$tkyte@ORA920.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> update t1 set object_id = object_id+1;

29303 rows updated.


Statistics
----------------------------------------------------------
0 recursive calls
30079 db block gets
799 consistent gets
0 physical reads
4472720 redo size
789 bytes sent via SQL*Net to client
806 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
29303 rows processed

ops$tkyte@ORA920.US.ORACLE.COM> update t2 set object_id = object_id+1;

29303 rows updated.


Statistics
----------------------------------------------------------
0 recursive calls
29986 db block gets
407 consistent gets
400 physical reads
7171100 redo size
789 bytes sent via SQL*Net to client
806 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
29303 rows processed

ops$tkyte@ORA920.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> delete from t1;

29303 rows deleted.


Statistics
----------------------------------------------------------
128 recursive calls
31547 db block gets
863 consistent gets
0 physical reads
8449052 redo size
790 bytes sent via SQL*Net to client
783 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
29303 rows processed

ops$tkyte@ORA920.US.ORACLE.COM> delete from t2;

29303 rows deleted.


Statistics
----------------------------------------------------------
56 recursive calls
32713 db block gets
810 consistent gets
221 physical reads
10464456 redo size
790 bytes sent via SQL*Net to client
783 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
29303 rows processed

ops$tkyte@ORA920.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set autotrace off


so no, I would say "not a reasonable 'demand'" as we already supply it for the most part with non-logged operations






Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Direct Path Load into GTT ?

Robert C, October 19, 2002 - 1:41 pm UTC

>>insert /*+ APPEND */ into P select * from GT;

Tom , does it do any good to do a "Insert /*+ APPEND */" into a GTT ?

thanks

Tom Kyte
October 19, 2002 - 2:24 pm UTC 

yes it can.

What happens (if you have my book "Expert one on one Oracle" i go into things about temp tables...) is that 

a) no REDO is generated for the GTT
b) however UNDO is generated for the GTT
c) and REDO for the UNDO *must* be generated


since an insert /*+ append */ can skip UNDO generation for the table (but not index) segment -- it'll generate that much less REDO.


ops$tkyte@ORA920.US.ORACLE.COM> create global temporary table gtt on commit preserve rows
  2  as
  3  select * from all_objects where 1=0;

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set autotrace traceonly statistics;
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into gtt select * from all_objects;

29317 rows created.


Statistics
----------------------------------------------------------
         25  recursive calls
      31307  db block gets
     138355  consistent gets
          0  physical reads<b>
    3765980  redo size</b>
        782  bytes sent via SQL*Net to client
        810  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      29317  rows processed

ops$tkyte@ORA920.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA920.US.ORACLE.COM> insert /*+ APPEND */ into gtt select * from all_objects;

29317 rows created.


Statistics
----------------------------------------------------------
          7  recursive calls
          8  db block gets
     138276  consistent gets
          0  physical reads<b>
        224  redo size</b>
        768  bytes sent via SQL*Net to client
        824  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      29317  rows processed

Space above HWM usage...

pasko, October 21, 2002 - 4:27 am UTC

HI Tom,
As usual, thanks for a Great Discussion above!


I have this doubt:
I know that :

with : "insert /*+ APPEND */ into some_table
select * from some_other_table " ,

we use space only above the HWM and less REDO generated.

But does the same hold true for insert without the APPEND HINT:

"insert into some_table
select * from some_other_table " ?


What are other major differences between the two ?


Thanks in advance.












Tom Kyte
October 21, 2002 - 7:39 am UTC

it'll generate LESS UNDO (which leads to less REDO) but in archive log mode, it'll generate gobs of REDO as well unless the table is nologging.

without the append hint, it'll work as normal -- all UNDO and REDO is fully generated and there is no way to reduce the amount.

If you click on that first link above -- you'll learn lots about what this direct path insert does.

Sagi, October 21, 2002 - 8:37 am UTC

Hi !

Tom, Your solutions are the BEST. But for this question the user was :

1) Creating/Has a Global Temporary Table (GTT)
2) Does some operations on GTT.
3) Truncates the table P (Permanent Table)
4) CTAS or any other Solution which he asked...

You told that he can use

INSERT /*+ APPEND */ INTO P
AS SELECT * FROM GTT

But I was thinking he can as well do this, which is more faster:

1) Anyway doing TRUNCATE TABLE P
2) Now DROP TABLE P
3) RENAME GTT_TBL TO P
4) Create the TEMPORARY TABLE agin.

This is much more faster than the Direct Load. What do you say?

Regards,
Sagi

Tom Kyte
October 21, 2002 - 9:06 am UTC

renaming a gtt would be sort of useless.

a) no one else can ever see the data in it
b) the data would disappear right after you logged out.


so, while it might be "fast", it would not be "useful"

Sagi, October 21, 2002 - 9:05 am UTC

Hi Tom,

To see whether a table is GLOBAL TEMPORARY TABLE (GTT) or not we can execute the below query:

SELECT * from USER_OBJECTS
where temporary='Y'

But i wanted to know if the GTT was a Session Based or Transaction Based i.e. ON COMMIT PRESERVER ROWS or ON COMMIT DELETE ROWS.

Which Datadictionary (DD) table or columns in DD tells us this info?

Thanx in advance.

Regards,
Sagi

Tom Kyte
October 21, 2002 - 9:18 am UTC

select duration from user_tables;


Why the Redo?

A reader, October 21, 2002 - 4:55 pm UTC

Ok, so I give up. How come GTTs need redo? In the event of a server failure both the transaction and session will end, so what value is redo?

Tom Kyte
October 21, 2002 - 7:03 pm UTC

GTTs don't

UNDO does

GTTs generate undo (they have to -- you can rollback, rollback to savepoint and have statements that fail halfway thru -- we roll those back as well). In order to rollback, we need UNDO.

For an INSERT, minimal undo.
For an UPDATE/DELETE -- lots more.

if all you do is INSERT and SELECT from GTT's -- little redo. If you UPDATE DELETE them -- correspondinly more.

Sagi, October 22, 2002 - 6:09 am UTC

Hi Tom,

I have one silly doubt. You said

"an insert /*+ append */ can skip UNDO generation"

q1) It can skip or it skips. If you say "can skik" can you give us example. Because i was under assumption that always it skips.

q2) Why does REDO log get generated using "INSERT /*+ APPEND */. Because anyway we it does not generate UNDO, and recovery is not possible, So why?

Thank you in Advance.

Regards,
Sagi


Tom Kyte
October 22, 2002 - 7:36 am UTC

q1) insert /*+ append */ into table_with_indexes select ....


that'll skip UNDO on the table
it cannot skip UNDO on the indexes


q2) see #1

GTT vs. permanent?

Ilya, February 18, 2004 - 9:27 pm UTC

Hi Tom,

This is a very useful discussion, but I still have questions on global temporary tables:

If a stored procedure is accessed in single-user mode (that is, it's a job that runs on the server and not user-initiated), does it make sense to use Global Temporary Tables or use Permanent tables with /*+append*/ hint? From what I read above, there should be no difference in performance?

Also, is there any disadvantage to using global temporary table as opposed to a permanent one in that situation? My DBA is insisting (rather vehemently) that I change all global temporary tables into permanent, because they take up temporary space. What is a reasonable size for temporary space in general? Right now I have global temporary tables in all stored procedures (that need them), and use permanet tables only for data I want to preserve.

Last one -- I prefer to have 'on commit preserve rows' so that when I debug a stored procedure I can verify the data after it finished. Another reason is I don't want to rely on where I commit inside procedure and rather truncate the table myself (I truncate it at the start). What is the penalty in this case -- versus changing it to 'on commit delete rows.'

thanks,
ilya


Tom Kyte
February 19, 2004 - 8:40 am UTC

if you are doing something that requires a scratch table for the duration of the transaction/session -- a GTT is appropriate, regardless of single user, multi-user.

so what if "temporary stuff" takes up "temporary space"? what the HECK is wrong with that?

temp is temp. They would rather you use real space that needs to be backed up, allocated? I mean -- you either have the space in tempfiles or you have the space in datafiles. At the end of the day, you have the space someone - no?

Your dba would have to give some scientific reason for wanting this in real datafiles in order for me to comment -- I cannot even dream of a reason I'd want temporary stuff in permanent datafiles?


there is no "penalty" -- if you want the data to persist for the session, you don't have a choice.

GTT and temp space

A reader, February 19, 2004 - 1:01 pm UTC

Hi Tom,

thanks so much for a fast response.

The main reason I'm given is that if my stored procedure using a GTT hangs, it will screw up the whole system, while if it uses permanent tables, only my schema will be affected. Is it possible to somehow isolate the temp space used by my stored procedure from the rest?

Also, it seems the dba on the client side is reluctant to allocate even 1 GB for temp space (I think they want to keep it at 300 Mb). I don't really understand why (I'm hearing that the hard disk space used for database is very expensive). So, presumably by using permanent tables there'll be more space for stored procedure?

thanks,
ilya


Tom Kyte
February 19, 2004 - 2:34 pm UTC

1gig of temp
or
1gig of permanent.

hmm, sounds like "6 one way", "1/2 dozen the other" doesn't it.

(that and you've already spent more of your companies money talking about this issue then the disk would cost, really).


You have to have "X bytes of storage".

You can either "always have X bytes of storage" permanently allocated or in temp. I don't get it.

Redo In GTTs

VKOUL, February 20, 2004 - 3:17 pm UTC 

Hi Tom

Pls. see the output below :

*************************************************
First Session (Same USER)
*************************************************
SQL> ed
Wrote file afiedt.buf

  1  create global temporary table gtt on commit
  2  preserve rows
  3  as
  4* select * from all_objects where 1=0
SQL> 
SQL> 
SQL> 
SQL> /

Table created.

SQL> set autotrace traceonly statistics;
SQL> 
SQL> 
SQL> insert into gtt select * from all_objects;

24582 rows created.


Statistics
----------------------------------------------------------
         18  recursive calls
       1916  db block gets
      95778  consistent gets
        845  physical reads
     130208  redo size
        444  bytes sent via SQL*Net to client
        320  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      24582  rows processed

SQL> disconnect
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.3.0 - 64bit Production
SQL> 
*************************************************
Second Session (Same USER)
*************************************************
SQL> create global temporary table gtt on commit
  2  preserve rows
  3  as
  4  select * from all_objects where 1=0;

Table created.

SQL> set autotrace traceonly statistics;
SQL> 
SQL> insert /*+ APPEND */ into gtt select * from 
  2  all_objects;

24582 rows created.


Statistics
----------------------------------------------------------
         20  recursive calls
         18  db block gets
      95448  consistent gets
          0  physical reads
        184  redo size
        419  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      24582  rows processed

SQL> disc
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.3.0 - 64bit Production
SQL>
*************************************************

Apart from REDO, why there is a huge difference between PHYSICAL READS, DB BLOCK GETS between the two queries ?

Tom Kyte
February 20, 2004 - 3:26 pm UTC

physical reads -- easy, the first query did them, the second benefited from the first one doing them for it. (it cached the blocks)

the db block gets are due to the way APPEND works vs conventional SQL.

In order to insert into a block using conventional SQL, the block is retrieved from the freelist in "current mode" -- we can only modify the "current version" of the block.

In order to insert using APPEND, we just do direct writes to "new'ed" blocks, we don't have to get them in current mode, they are "blank" to begin with.

VKOUL, February 22, 2004 - 5:34 pm UTC

Thanks a lot

Pls clarify

A reader, September 23, 2005 - 12:05 pm UTC

Tom,

On reading your book 'Expert One-On-One Oracle' Redo and Rollback and this discussion thread, this is what my understanding on GTT and regular tables. Please confirm and help me if my understanding is not clear

1. Regular INSERTS (non-direct path operations) and DELETES/UPDATES cannot be done in NOLOGGING mode and will generate tons of redo-log based on the amount of data changed. Only direct-path operations such as INSERT /*+ APPEND */ and direct-path SQL*Loader generate "minimal" redo
2. UNDO is always protected by REDO.
3. GTT does not generate any REDO at all. It generates only UNDO and since UNDO is protected by REDO, it will generate a minimal REDO for the UNDO
4. Since GTT does not generate REDO and only UNDO and also that /*+ APPEND */ hint does not generate UNDO (and consequently no REDO to protect the UNDO), by using an /*+ APPEND */ hint against GTT will generate the least amount of REDO.
5. Since INSERTs and SELECT generates minimal UNDO (and consequently minimal REDO), GTT will be best suited when most of your operations against GTT are either INSERTs and SELECTs
6. Since UPDATEs and DELETEs generate the most amount of UNDO (and consequently most REDO to protect the UNDO), using GTT for these DML operations will almost generate the same amount of REDO as for a normal table.
a. An UPDATE against a GTT will generate 1/2 of REDO as it generated for normal table (since REDO for the UNDO is logged)
b. A DELETE against a GTT will generate same amount of REDO as it generated for normal table (since UNDO for DELETE is more) and the REDO for the UNDO is logged

Thanks much

Tom Kyte
September 23, 2005 - 8:28 pm UTC

1) tons is a "strange way" to put it, they generate what they must.

GTT's generate undo
UNDO generates redo.

direct path operations bypass redo on the TABLE (but not indexes) and can reduce the amount of redo generated.

nologging can bypass redo no the TABLE with direct path (but not indexes)


2) correct

3) but undo can be large - so it is better to say "gtt generates undo and the undo it generates is logged"

4) until you have indexes, yes...

5) correct

6) "less than" would be better, rather than "almost the same"

it comes back to indexes again.....



instead of GTT, directly using Persistent tables

uday, November 27, 2007 - 7:28 pm UTC

Hi Tom,

Since the original poster wanted to persist data, can he not directly work with persistent tables?

We now have a requirement where we store intermediate transformations in the persistent tables to keep the audit trial. This will help the customer and us to understand the how the a particular result has been achieved. Customers can later purge the data.

What is the best option in terms of performance and why:
1. GTT to Persistent table?
2. Only persistent tables?

Thanks in advance
Tom Kyte
November 28, 2007 - 10:39 pm UTC

there is no single answer.

I can come up with cases where gtt -> permanent tables (Pt) is best
or just do the pt directly is best
or either or is equally OK.



say you have a complex "refresh" of a table you are trying to accomplish. It cannot be done in a single sql statement and takes a long time

further, the amount of time the persistent table is offline must be MINIMIZED, but does not have to be ZERO.

further, raw performance is desirable, the best performance query wise is what you want.

then, gtt->pt might make sense. If you did the PT directly, you would have to use a long, large transaction. Indexes would likely double or more in size (you will be deleting everything - without committing afterwards in order to minimize down time since it takes a long time to rebuild). Doubling size of index might make index "not as good", slower...

It really depends.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions