Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jaganath.

Asked: March 12, 2020 - 4:08 pm UTC

Last updated: March 16, 2020 - 10:19 am UTC

Version: 12

Viewed 1000+ times

You Asked

Hi Tom,

Hope you are doing good!

I have been assigned a task.

I have to load 170 million rows from one table to another, the structure of both the tables are same.

I have tried DBMS_PARALLEL_EXECUTE: Chunks by ROWID, it is taking 2 hours to complete the load. Can you give some thoughts to insert it in a faster way.

Thanks,
Jaganath K

and Chris said...

First thing I'd try is a straightforward parallel insert:

alter session enable parallel dml;

insert /*+ parallel */into t 
  select * from t;


If that's not fast enough, there are many answers on this site discussing how to speed up inserts, for example:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6145433600346650438
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9523961800346590171
https://asktom.oracle.com/pls/apex/asktom.search?tag=insert-performance-201310

Rating

  (1 rating)

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

Comments

MIssing information!

Stew Ashton, March 14, 2020 - 8:07 am UTC

Are the two tables in the same database ??? If not, parallel execution is a nono.

Does the target table already have data in it, or can it be a new copy?

What is the point of copying all that data from one table to another? Are we talking data warehouse?

Is the source data being modified during the copy?

Are there any LOB columns, LONG columns, nested tables or VARRAYs?

I'm probably missing some questions too...

Best regards,
Stew Ashton
Connor McDonald
March 16, 2020 - 10:19 am UTC

welcome to our world
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

SQL

The Oracle documentation contains a complete SQL reference.