oraclehintsquery-hints

Oracle database hints for bulk Insert from DBLink

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?

Solution

  • 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