Improved performance with database links

If your database is distributed across a few servers, and you need to access the database across these servers, one way to improve performance is through the use of database links.

Here is a PL/SQL program that inserts records into a number of tables on one server from multiple different servers. Without using database links, this took hours to run the job -- now it only takes less than 5 minutes!

insert into table_a 
select * from remote_site1_table_a@db_link_site1.world; 

insert into table_b 
select * from remote_site1_table_b@db_link_site1.world; 

insert into table_a 
select * from remote_site2_table_a@db_link_site2.world; 

insert into table_b 
select * from remote_site2_table_b@db_link_site2.world; 

For More Information

• What do you think about this tip? E-mail the Edtior at tdichiara@techtarget.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.