How to quickly insert a large amount of data into the database

This article will explain in detail how to achieve a large number of data in the database fast insertion method, Xiaobian feel quite practical, so share to everyone for a reference, I hope you can read this article after harvest.

1 Environmental construction

Build a source table of tens of millions and insert operations into an empty table.

Reference metric: actual time to insert action completion.

SQL> drop table test_emp cascadeconstraints purge;Table dropped.SQL> create table test_emp as select *from emp;Table created.SQL> begin 2 for i in 1.. 10 loop 3 insert into test_emp select *from test_emp; --batch quantity dml, Suggestion forall 4 end loop; 5 end; 6 /PL/SQL procedure successfully completed.SQL> select count(*) from test_emp; COUNT(*)------- 14336SQL> begin 2 for i in 1.. 10 loop 3 insert into test_emp select *from test_emp; 4 end loop 5 ; 6 end; 7 /PL/SQL procedure successfully completed.SQL> select count(*) from test_emp; COUNT(*)---------- 14680064 2 only appendSQL> set timing onSQL> show timing ONSQL> insert /*+ append */ into test_goalselect * from test_emp;14680064 rows created.

Elapsed: 00:00:20.72

There is no closed log, so the time is the longest.

3 append+nologgingSQL> truncate table test_goal;Table truncated.Elapsed: 00:00:00.11SQL> insert /*+ append */ into test_goalselect * from test_emp nologging;14680064 rows created.

Elapsed: 00:00:04.82

It is found that the log has a great impact on insertion, and the time of adding nlogging is significantly shortened; of course, this table has no index, constraint, etc., so it is not studied here.

4 append+nologging+parallelSQL> truncate table test_goal;Table truncated. Elapsed: 00:00:00.09SQL> insert /*+ parallel(2) append */into test_goal select * from test_emp nologging;14680064 rows created.

Elapsed: 00:00:02.86

Here on the basis of 3 plus parallel, performance basically reached the limit, 150 million data insertion time control in about 3S. Parallel In the case of server performance support, you can increase the parallel parameter.

About "how to achieve a large amount of data in the database rapid insertion method" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.