A study of rapid insertion method for large numbers of data [nologging+parallel+append]

Research on rapid insertion method of large amount of data

fast insertion of TENS data is nothing more than Nologging+parallel+append .

1     Environment Construction

build a Tens source table and insert operations into an empty table .

Reference Indicator: The actual time that the Insert action was completed.

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;   --bulk DML, recommended 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&nbsP;test_emp;  count (*)---------- 14680064             --1.5 Tens, please.

2     Only Append

Sql> Set Timing onsql> show timingtiming onsql> Insert/*+ Append * to Test_goalselect * from test_emp;1468006 4 rows created.

elapsed:00:00:20.72

The log is not closed, so the time is the longest.

3     Append+nologging

sql> 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 effect on inserting, and the time of adding nologging is significantly shortened; the table is not indexed, constrained, etc.

4     Append+nologging+parallel

sql> 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,1.5 Tens of thousands of data insertion time control around 3S. Parallel in the case of server performance support, you can increase the parallel parameters.

This article is from the "90SirDB" blog, be sure to keep this source http://90sirdb.blog.51cto.com/8713279/1794367

A study of rapid insertion method for large numbers of data [nologging+parallel+append]