Skip to Main Content
  • Questions

  • What's the quickest way to insert 43 million rows from one table into another that has triggers on it

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, chasey.

Asked: February 26, 2013 - 3:48 pm UTC

Last updated: February 27, 2013 - 10:42 am UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I used an external table to load 43 million rows of data into the db then created a staging table where I performed some checks on the data. Its now verified and needs inserting into another table.

However, the target table contains triggers and so I have tried the append and parallel hint and as you say in previous posts it gets ignored when you have a table that has triggers on it.

Have you any recommendations?

and Tom said...

disable the triggers

load the table and incorporate the logic behind the triggers into the select statement - that is, do whatever you need to do in SQL to transform the data.

enable triggers (and consider removing triggers from your system when you can - and you almost always can)


If you have triggers on there that must fire, that you cannot avoid firing - then you shall be slow by slow processing, there is NO escaping that.


You can use dbms_parallel_execute to break the source table up into chunks and then run a few load threads at a time against it.


this introduces dbms_parallel_execute
http://www.oracle.com/technetwork/issue-archive/2009/09-nov/o69asktom-089919.html

this one demonstrates it:
http://www.oracle.com/technetwork/issue-archive/2010/10-mar/o20asktom-098897.html

that can give you some parallelism - but it will be CONVENTIONAL path parallel - not direct path.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library