Copy Table As

[pflakes]

At the end of each month, I have a procedures which copies all the data from one table to another - I am currently doing this by using the following:

DROP TABLE last_monthrecords

CREATE TABLE last_monthrecords
as select .... from this_monthrecords

It works but because of the number of records it is taking about 3 hours. February had 16 million records - and we do need to keep all of the data.

Is there a more efficient way I can copy this table?

 

[jayjaybigs]

The time will be shorter if you use sqlloader.

1)Use this to spool all data to flat file:
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool thismonth.txt
select col1 || ',' || col2 || ',' || col3
from lastmonth
where col2 = 'XYZ';
spool off

2)Use this to load into table thismonth
sqlldr scott/tiger control=loader.ctl

3)and here is your control file:
load data
infile 'c:\data\lastmonth.dat'
into table thismonth
fields terminated by ","
optionally enclosed by '"'
(empno,
empname,
sal,
deptno )

 

[SantaMufasa]

I believe I have an infinitely faster method:

1) drop table last_monthrecords;
2) rename this_monthrecords to last_monthrecords;

Let me know if this satisfactorily resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[pflakes]

By renaming this_monthrecords to last_monthrecords, won't the table this_monthrecords be gone? I do need to have that table. Data is loaded into it every 5 minutes.

 

[SantaMufasa]

Yes, and to resolve that issue, add an other step to the first two:

3) CREATE TABLE this_monthrecords AS SELECT * FROM last_monthrecords WHERE ROWNUM = 0;

This creates the table structure but brings across none of the rows.

(I must leave now for a User-group conference, but will try to check Tek-Tips during the day if you have additional questions.)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[pflakes]

Thank you for all the help - this works great. In our dev enviroment (which has much fewer records), the runtime dropped from 2+ minutes to 6 seconds. On prod this usually takes about 2 hours - I expect to see a dramatic difference there as well.