I need to insert around 50 tables that have bulk data in them via DBLink. I generally use the statement
insert into <table_name>
select * from <table_name>@DBLink
In some cases, it takes too long to insert. What useful database hints can be used in here to speed up the process?
You can use ORACLE's APPEND hint
The APPEND hint tells the optimizer to perform a direct-path insert, which improves the performance of INSERT .. SELECT operations for a number of reasons:
Data is appended to the end of the table, rather than attempting to use existing free space within the table.
Data is written directly to the data files, by-passing the buffer cache.
Referential integrity constraints are not considered. *
No trigger processing is performed. *
Syntax:
INSERT /* + APPEND */ INTO YourTable
SELECT * FROM YourTable@DBLink