Oracle Insert massive data experience

In many cases, we will need to insert a large amount of data into a table, and hope to complete the work in as short a time as possible, here, and share with you some of my usual experience in doing a lot of data insert.

Premise: Before making the insert data, if it is a non-production environment, remove the indexes and constraints for the table, and then build the indexes and constraints when the insert is complete.

1.

INSERT INTO TAB1 select * from TAB2; Commit

This is the most basic INSERT statement, and we insert the data from the TAB2 table into the TAB1 table. Based on experience, tens data can be completed within 1 hours. However, this method will generate a very fast arch, need to focus on the production of the archive, timely start backup software, to avoid the arch directory explosion.

2.

ALTER TABLE TAB1 nologging; Insert/*+ Append */into TAB1 select * from TAB2; Commit ALTER TABLE TAB1 logging;

This method will greatly reduce the resulting arch, and to a certain extent, improve the time, according to experience, tens data can be completed in 45 minutes. Note, however, that this method is suitable for a single-process serial approach, and if multiple processes are running concurrently, the post-initiated process will have enqueue waiting. Note This method must not dataguard on the use (but if the database has force logging that is not afraid, hehe)!!

3.

Insert INTO TAB1 Select/*+ Parallel */* from TAB2; Commit

For the statement after Select is full table scan, we can add parallel hint to increase its concurrency, it is important to note that the maximum concurrency is limited by the initialization parameters Parallel_max_servers, the concurrent process can be v$px_ Session view, or Ps-ef |grep ora_p view.

4.

Alter session enable parallel DML; Insert/*+ parallel */into TAB1 select * from TAB2; Commit

In contrast to Method 2, concurrent insert, which has not yet been compared and Method 2 which is more efficient (I estimate is Method 2 fast), has been tested by a friend welcome supplement.

5.

INSERT INTO TAB1 select * from TAB2 partition (P1); INSERT INTO TAB1 select * from TAB2 partition (P2); INSERT INTO TAB1 select * from TAB2 partition (p3); INSERT INTO TAB1 select * from TAB2 partition (P4);

For partitioned tables you can take advantage of TAB1 for concurrent inserts of multiple processes, the more partitions you have, the more processes you can start. I have tried the insert 260 million row record of a table, 8 partitions, 8 processes, if using method 2, a single process completion may take 40 minutes, but because there are 8 partitions 8 processes, the post process has enqueue, so the time required is 40 minutes x8; But if you use Method 5, Although a single process takes 110 minutes, the total time required is approximately 110 minutes due to the ability to execute concurrently with the process.

6.

DECLARE TYPE Dtarray is TABLE of VARCHAR2 (a) INDEX by Binary_integer; V_col1 Dtarray; V_col2 Dtarray; V_col3 Dtarray; BEGIN SELECT col1, col2, col3 BULK COLLECT Into V_col1, V_col2, v_col3 from TAB2; FORALL i in 1.. V_col1. COUNT Insert INTO tab1 WHERE tab1.col1 = v_col1; END;

The way to use bulk binding (bulk binding). When looping through SQL statements that execute a bound variable, a large number of context switches occur in PL/SQL and in the engines (switches). With bulk binding, data can be batched from the Plsql engine to the SQL engine, reducing the context switching process and improving efficiency. This method is more suitable for online processing without downtime.

7.

Sqlplus-s user/pwd< Runlog.txt Set Copycommit 2; Set ArraySize 5000; Copy from User/[email protected]- To User/[email protected]- Insert TAB1 using SELECT * from TAB2; Exit Eof

Insert with Copy, note that insert does not have an into keyword here. The advantage of this method is that Copycommit and arrarysize can be set to control the frequency of commit together, and the above method is one commit per 10000 rows.

Oracle Insert massive data experience (GO)