loader vs. db links

kiiz
kiiz used Ask the Experts™
on
I need some advice from experienced Oracle users -  what is generally the quicker way to load data into database - loader with direct path or using insert and database link (remote server)?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Loader with direct path should be much faster that insert (especially over a db link). The loader itself uses insert statement when using conventional path, but the direct path is much faster. Inserts over a db link are also slower than inserts within the same db because of the network trafic. So overall the result should be that direct path load is much faster.
Hope that helps!

Commented:



Hi

pennn is right, I like to say something more about it. Creating db links could be very fast and using it is good for small amount of data. Application is pulling some data througn link from remote database. You can use simple sql interface

insert into my_table select * from remote_table@remote_db.

Today's hardware could be fast enough for using links



SQL loader is powerfull and fast, but you had to learn some new syntax, prepare data to format readable by sql loader and so on. This tool is fast for huge amounts of data tens of gigs and so on. But it is really fast.



Or you can just use SQLPlus command COPY to copy data from one db to another. You will not need to bee granted with create db link and similiar....

Or use some special software for fast load and unload as Quest does, I hope quest offers similiar product.



Starous


If your data is already in an Oracle database then it may be safer to transfer across a database link. We had problems exporting/importing and writing out and then loading with characterset translation.

If you use a database link then you won't get any characterset translation problems. We regularly transfer large (several G) across database links and it works fine.

Remember that DB links are not very secure (you can see the remote password in dba_database_links (sp?) so best to make the link bewteen two users with the same username and password and then you don't need to store it in the database.
Helena Markováprogrammer-analyst

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ/No Refund.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer
PAQed - no points refunded (of 45)

SpazMODic
EE Moderator

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial