Hi Guys,
I need to insert millions of records on to remote database using db link, what is the best way to do that for gaining the maximum performance.
Currently I am inserting it directly in to the remote database which is working fine but I don't have control over result sets. If the result set is very huge in millions or trillions then it might create issue while inserting that much data in one go over the db link.
-- FA_STG is a table available on remote database, a synonym is created in local database for the same.
My approach
(1) Directly insert into the remote table -- but no control on data.
insert /*+ append */ into FAH_STG
select * from APP_STG;
(2) do the sequential inserts( row by row) either by limiting the result set by limit clause -- which is very slow
insert into FAH_STG
select * from APP_STG;
(3) cant use FORALL caluse over db link.
(4) create GTT or intermediate table and then insert it using append hints - we dont want to go for intermediate or GTT
Any suggestion would be appreciated.
Thanks