Move records from one database to the other 1

[evgeniosc]

I want to move records from one database to another for archiving purposes.

So I created a db link and a synonym on archiving server pointing to the production server like this:

CREATE Public Database Link Prod_Link Connect to myuser IDENTIFIED BY "mypass" Using 'MyTnsName';

CREATE Public SYNONYM DocsProd FOR myuser.Docs@Prod_Link;

I want to run the following sql statements on archiving server to move the records:

INSERT INTO myuser.Docs
SELECT * FROM DocsProd WHERE ID<1000000;

DELETE FROM DocsProd WHERE ID<1000000;
COMMIT;


I have two questions:
1. Would it be faster to do it the other way around? What I mean is create db link on Production Server to point to the archive and run the sql on production server.
2. How can I be sure that the transaction is rollbacked if an error occurs during the insert or delete statement?

 

[carp]

1. I'm not sure it will make much difference; you will probably just need to try it both ways to see.
2. If you set this up with both statements in the same transaction and do a rollback if there is an error in either statement, it will roll back.

I would caution against basing your query on a record count - there is no guarantee you will delete the same records you copied off (unless, of course, you have less than 1000000 rows in the table!). A better gauge would be a date or primary key value.

Finally, if you are going to be taking out up to 1000000 rows at a time, you might also want to consider rebuilding your indexes when you are done.

 

[Beilstwh]

It will make a big difference. If you are running from the archive machine, use the following queries. if you are running from the production site you do not need the hint.


INSERT INTO myuser.Docs
SELECT * /*+ DRIVING_SITE(DocsProd) */ FROM DocsProd WHERE ID<1000000;

DELETE /*+ DRIVING_SITE(DocsProd) */ FROM DocsProd WHERE ID<1000000;
COMMIT;

Bill
Lead Application Developer
New York State, USA

 

[evgeniosc]

I also added the following line in top of the sql script and run it using sql plus (to make sure that the whole process is a transaction):

whenever sqlerror exit failure rollback

 

[carp]

Bill -
If the transactions do not involve joins or sorting, how will the performance differ? Please understand that I am not questioning your answer, but trying to learn something here.

As I understand it, if you are pulling the data, the local database spawns off a session on the remote database. The remote session then transmits the data back to the local session, which inserts it into a table. When pushing, the local database spawns a session on the remote database and sends it data, which the remote database session inserts into a table. So in either case, we have a session on each database, one of which is running a query and sending data to the other, which is inserting into tables. The machines are the same in either case and we are using the same network in either case. So where does the performance difference come from?

I could understand differences if there were joins or sorts involved, but for a simple query on one table I do not see where the variable is. But my knowledge in this area is rudimentary and I'm always anxious to learn from somebody with more experience!

Thanks.

- Dave Bridges
Black Forest, CO

 

[Beilstwh]

your right, it matters if you have joins not just a simple where clause. However with joins it makes a tremendous difference. I had a select going to a remote database with joins on the remote tables that was taking 2 hours to run. I added the driving site hint and it went down to 5 seconds.

Bill
Lead Application Developer
New York State, USA

 

[carp]

Bill -
Thanks - that's a good trick to know. We've had similar issues (several years ago) and found it was easier to set up landing zones on the local database consisting of temporary tables that only contained the columns we needed, dragging great bloody hunks of the remote tables over to the local side and then doing the joins locally. But the hint seems like a much cleaner method!