Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: December 14, 2022 - 12:50 am UTC

Last updated: December 14, 2022 - 8:11 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

I have load 1000 million records to be inserted from one table in migration project. Can you please suggest how to write the code in effective way . I tried by putting append hint. It was taking 3 mins to complete 100 million records.. Can you please suggest for 1000 million

Thanks

and Connor said...

For an unindexed table, you should be getting around (or more) than 1,000,000 rows per second for each session doing work, eg this from my laptop

SQL> create table t 
  2  as select * from dba_objects where 1=0;

Table created.

SQL>
SQL> create table t1 
  2  as select d.* from dba_objects d,
  3    ( select 1 from dual connect by level <= 50 );

Table created.

SQL>
SQL> set timing on
SQL> insert /*+ APPEND */ into t
  2  select * from t1;

4048450 rows created.

Elapsed: 00:00:03.02
SQL> set timing off


My laptop runs in noarchivelog mode, so that's equivalent to setting nologging.

If you're not getting that throughput, then look at whats holding it back - indexes/constraints/triggers? or hardware?

Anyway parallel insert is your friend here. Assuming your *hardware* can cope with it, "n" parallel threads should yield approx. "n" millions of rows per second.

And obviously, a billion row table is a candidate for partitioning here.


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

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.