0

I need to insert 10 million records via Dblink into my database.

Do the normal insert statement insert into select * from would be the right or optimal way?

What other option could there be to insert data of this volume?

I am using Oracle 11g.

Imran Hemani
  • 599
  • 3
  • 12
  • 27
  • 1
    What about expdp/impdp with append? (If both databases are Oracle of course) – Jon Tofte-Hansen Oct 30 '15 at 10:13
  • I'd say the fastest way to import 10 mln records is use sqlldr, but right solution is really depends on your environment – are Oct 30 '15 at 10:59
  • Assuming both databases are tuned to do such a thing (appropriate redo space, etc), I'd say this certainly be the easiest way to do it, and it should be pretty fast and server-friendly. – Hambone Oct 30 '15 at 12:02

2 Answers2

0

For 10 million Records we can use EXPDP/IMPDP also be cautious that we cannot copy data for partitioned tables across dblinks ( we need to create temp non partition table load data locally and then copy over database link)

Thanks, Kalyan

Kalyan Banala
  • 1
0

To find optimal way (using DBLINK) you must define your optimization criteria...

Simple first estimate the required time of the insert. Test with 10.000 rows and multiply the time with 1.000 - you'll get approximation of the whole statement duration. If the time is acceptable, you're done.

If not, calculate the reached transfer rate MB/sec and compare it with your network througput. You will see if there is improvement possible.

Next option is direct path insert - use APPEND hint (on the target side) and select via link from the remote side.

If this imporvement isn't enough (and you have still theoretical improvement), the last resort is parallel insert. Unfortunatelly this not possible ti do via PARALLEL hint and you must open N inserts to N temporary tables or N partitions of an temporary table.

You see that getting speed cost some effort, so you must find your own optimum...

I do not go much in detail as for each option its easy to find additional information.

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53