Home » SQL & PL/SQL » SQL & PL/SQL » Copy Command in a procedure (Oracle 10g)
Copy Command in a procedure [message #444497] Mon, 22 February 2010 05:01 Go to next message
venkat_bollu
Messages: 15
Registered: June 2009
Junior Member
Hi All,

I got to pull 30M data into local DB from a remote DB. As of now we have procedure to perform this task using an

Insert /*+append*/ table1
select * 
from rdb_view;


During my exercise I observed using the copy command in sql plus has taken very less time than the procedure.

Can we use the Copy command inside the PL/SQL block? I tried with dynamic SQL but It is not allowed I guess.

Thanks
Venkat.
Re: Copy Command in a procedure [message #444502 is a reply to message #444497] Mon, 22 February 2010 05:07 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

Can we use the Copy command inside the PL/SQL block? I tried with dynamic SQL but It is not allowed I guess.

No

The COPY command is a SQL *PLUS command and not SQL or PL/SQL. It is facilitated by the fact that SQL *PLUS can connect to different databases simultaneously.

[Updated on: Mon, 22 February 2010 05:09]

Report message to a moderator

Re: Copy Command in a procedure [message #444505 is a reply to message #444497] Mon, 22 February 2010 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
COPY is a SQL*Plus command, not a PL/SQL statement, you can't use it inside a PL/SQL block.

Regards
Michel
Re: Copy Command in a procedure [message #444506 is a reply to message #444502] Mon, 22 February 2010 05:19 Go to previous messageGo to next message
venkat_bollu
Messages: 15
Registered: June 2009
Junior Member
Hi Ved,

Thanks for the quick reply..

But is there any alternative way of performing this in a PL/SQL block.

I prepared a sql file containing the Copy command and a PL/SQL block as follows

begin
Post_Message(1234,'Started', sysdate);
commit;
end;

TRUNCATE TABLE table1;

COPY FROM  rempte_db -
TO local_db -
INSERT table1 -
USING SELECT * -
   FROM rdb_view;

begin
Post_Message(1234,'Completed', sysdate);
commit;
end;


when I execute this file in sqlplus, It was asking for user input after executing the copy statement.

This data pull is a batch job and has a dependency, so after the successful copy another process will start as the next step.


Thanks
Venkat.
Re: Copy Command in a procedure [message #444510 is a reply to message #444506] Mon, 22 February 2010 05:26 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Insert /*+append*/ table1
select * 
from rdb_view@db_link1;
Re: Copy Command in a procedure [message #444519 is a reply to message #444510] Mon, 22 February 2010 05:47 Go to previous messageGo to next message
venkat_bollu
Messages: 15
Registered: June 2009
Junior Member
Quote:
Insert /*+append*/ table1
select *
from rdb_view@db_link1;


This is the same insert statement I provided with my first query. I am not getting any error message. rbd_view is the synonym referring the view of the Remote DB.

There was some performance issue with this statement and I also tried using append, nologging and parallel as with the following script, but didn't improve any performance.

Quote:
Insert /*+append parallel(table1,4)*/ table1
select /*+parallel(a,4)*/*
from rdb_view a;


so I thought of using Copy command, by creating an sql file as mentioned in previous reply.

Unfortunately, after inserting the rows into the local DB table this copy command is waiting for the user's response. Could you please suggest any necessary modifications the following code.

-- This is the script inside my script.sql file.
begin
Post_Message(1234,'Started', sysdate);
commit;
end;

TRUNCATE TABLE table1;

COPY FROM  rempte_db -
TO local_db -
INSERT table1 -
USING SELECT * -
        FROM rdb_view;

begin
Post_Message(1234,'Completed', sysdate);
commit;
end;
-- End of my script.sql file


Thanks
Venkat.
Re: Copy Command in a procedure [message #444520 is a reply to message #444519] Mon, 22 February 2010 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is the same insert statement I provided with my first query.

Don't expect something really useful from him.

Have you:
- indexes?
- trigger?

Regards
Michel
Re: Copy Command in a procedure [message #444522 is a reply to message #444520] Mon, 22 February 2010 05:54 Go to previous messageGo to next message
venkat_bollu
Messages: 15
Registered: June 2009
Junior Member
We have indexes on the tables of Remote DB. There are no indexes on local table.

No triggers.
Re: Copy Command in a procedure [message #444531 is a reply to message #444520] Mon, 22 February 2010 06:57 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Michel is going to provide you the alternative way to insert data into local table other than dblink in a stored procedure.


Venkat,
The other option I can think of is using Materialized view.
Wait for Michel because he gonna give you the right solution I believe!

Thanks
Ved

[Updated on: Mon, 22 February 2010 07:02]

Report message to a moderator

Re: Copy Command in a procedure [message #444533 is a reply to message #444531] Mon, 22 February 2010 07:04 Go to previous messageGo to next message
venkat_bollu
Messages: 15
Registered: June 2009
Junior Member
yes Ved, MView is not possible as it occupies memory for 30M data in the database. At present I view have on the remote DB which doesn't consume any storage space.

Thanks
Venkat.
Re: Copy Command in a procedure [message #444534 is a reply to message #444522] Mon, 22 February 2010 07:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*Plus copy command uses some internal tricks you can't use, so you can never be as fast as this command.
Do not use PARALLEL hint unless you are pretty you have network and io subsystem free bandwidth allthe more since I doubt Oracle parallelizes the network access.
Is your source view complex?
How often do you make this load?

Regards
Michel


Re: Copy Command in a procedure [message #444535 is a reply to message #444533] Mon, 22 February 2010 07:07 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Please wait...Michel is gonna provide you the solution soon

Regards,
Ved
Re: Copy Command in a procedure [message #444539 is a reply to message #444534] Mon, 22 February 2010 07:23 Go to previous messageGo to next message
venkat_bollu
Messages: 15
Registered: June 2009
Junior Member
Yes, the source view is very complex and we do this load everyday. It takes about 6 hours in dev and qa environments and takes just upto 30mins in Production. I know it depends on the resources available to our environments.

But I suprised, when I used the Bulk collect to this Remote DB view and then forall ..insert into local table, I was able to complete the load in just 1hr and 10mins in dev itself. But I was not able reproduce the same timelines in QA. I confirmed with my DBA here that both dev and QA will have the same kind of environmental settings.

So If I can reproduce the same timelines, I think I may complete the load even less than 10mins in production and hence save the download timeline for the application as well.
Re: Copy Command in a procedure [message #444542 is a reply to message #444497] Mon, 22 February 2010 07:31 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are dev and QA processing the same volume of data?
Re: Copy Command in a procedure [message #444543 is a reply to message #444542] Mon, 22 February 2010 07:34 Go to previous messageGo to next message
venkat_bollu
Messages: 15
Registered: June 2009
Junior Member
yes they do process the same data.
Re: Copy Command in a procedure [message #444544 is a reply to message #444497] Mon, 22 February 2010 07:36 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Might be an idea to trace both sessions and compare the tkprof output of each to see what the difference was.
Re: Copy Command in a procedure [message #444548 is a reply to message #444543] Mon, 22 February 2010 08:08 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
A link may be helpful:


http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1101164500346436784
Previous Topic: Not getting output for other schemas
Next Topic: Bulk inserts get EXTREMELY slow after 10,000 queries
Goto Forum:
  


Current Time: Mon Sep 26 00:51:22 CDT 2016

Total time taken to generate the page: 0.22297 seconds