Home » SQL & PL/SQL » SQL & PL/SQL » How to copy records from one table to another? (Oracle 10g)
How to copy records from one table to another? [message #382841] Sun, 25 January 2009 15:45 Go to next message
ranger
Messages: 3
Registered: January 2009
Junior Member
Hello,

i am wondering if there are some efficient possibilities to "copy" a lot of records (over 1 billiard) from one table to another. Currently i am using PL/SQL to do this. This is the code:

begin

    insert into customer2
    select * from customer;
    commit;

end;


I have tested this with 500000 records. It takes over 25 seconds.
If You have better idea, how to "copy" many records from one table to another, than please let me know.

Kind regards,

Ranger


P.S.

I'm using Oracle 10g. Gives Oracle any possibility to perform this action? (e.g. i know that oracle gives the possibilities to perform partitioning).

[Updated on: Sun, 25 January 2009 15:52]

Report message to a moderator

Re: How to copy records from one table to another? [message #382842 is a reply to message #382841] Sun, 25 January 2009 15:57 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>insert into customer2 select * from customer;
Actually you are using vanilla SQL & that is about as good as it gets.
The "delay" is like due to hardware limitations.
Wrapping the SQL inside of a PL/SQL procedure gains you nothing meaningful.



Re: How to copy records from one table to another? [message #382843 is a reply to message #382842] Sun, 25 January 2009 16:01 Go to previous messageGo to next message
ranger
Messages: 3
Registered: January 2009
Junior Member
So, is there another way to copy records faster?
Re: How to copy records from one table to another? [message #382844 is a reply to message #382841] Sun, 25 January 2009 16:22 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>So, is there another way to copy records faster?
Not that I can think of now.
It does a Full Table Scan of the source table & generates redo & undo like crazy; plus writing to new table.
So for each row you could be doing 1 read & 3 writes.
If the system has a single disk, the disk is staying busy, busy, busy.

Perhaps others will come along with better ideas.
Re: How to copy records from one table to another? [message #382888 is a reply to message #382844] Mon, 26 January 2009 01:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
insert /*+ append */ would help
Re: How to copy records from one table to another? [message #382890 is a reply to message #382888] Mon, 26 January 2009 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...As well as parallel hint on source (and maybe target) table but ONLY if you have the CPU and IO resources to support it.

Regards
Michel
Re: How to copy records from one table to another? [message #382891 is a reply to message #382890] Mon, 26 January 2009 02:14 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
You may have a look at this example for INSERT via EXCHANGE PARTITION. It ist a "homework example" and untested in production environment, You should certain read the documentation.

drop table temp_t1;

drop table temp_t2;

create table temp_t1 (col number, val varchar2(20));

create table temp_t2 (col number, val varchar2(20))
  partition by list (col)
 (partition px values (default));

truncate table temp_t1;

truncate table temp_t2;
  
insert into temp_t1 
  select 1, level from dual  connect by level <= 10000000;  
  
set TIMING ON;

--INSERT with SELECT
insert into temp_t2 select * from temp_t1;

set TIMING OFF;

select count(*) from temp_t2;

truncate table temp_t2;

set TIMING ON;

--INSERT with EXCHANGE PARTITION
alter table temp_t2 exchange partition px with table temp_t1;

set TIMING OFF;

select count(*) from temp_t2;

Re: How to copy records from one table to another? [message #382893 is a reply to message #382891] Mon, 26 January 2009 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can only exchange a table with a partition, so all data must come from or go to only ONE partition.

Regards
Michel
Re: How to copy records from one table to another? [message #383147 is a reply to message #382890] Tue, 27 January 2009 07:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do you actually mean a billiard (1*10^12), or a billion (1*10^9)

If you are allowed to create the table at this point, rather than having to insert into an existing table, then you could do a CREATE TABLE<tname> NOLOGGING AS SELECT...
This would have the advantage of not generating Redo for the operation, and reducing disk I/O accordingly
Re: How to copy records from one table to another? [message #383211 is a reply to message #383147] Tue, 27 January 2009 15:08 Go to previous message
ranger
Messages: 3
Registered: January 2009
Junior Member
Hello,

first of all thanks for Your advices.

@JRowbottom: i meant billion (1*10^9) - my mistake Smile

The situation has changed. I don't have to copy 1,4 billion records. I need just a few records (about 5 millions).

I will try to perform this operation on the faster (than my notebook) machine.



Kind regards,

Ranger
Previous Topic: Select Statement after deletion
Next Topic: Issue with UTL_FILE
Goto Forum:
  


Current Time: Mon Dec 05 04:32:23 CST 2016

Total time taken to generate the page: 0.07731 seconds