Oracle: Efficient insertion of large amounts of data experience ____oracle

Prerequisite: Before making the insert data, if it is not a production environment, please remove the index and constraint of the table, and then build the index and constraints after the insert completes.

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. According to experience, the Tens data can be completed within 1 hours. However, the method produced by the arch will be very fast, need to focus on the production of the archive, the timely launch of backup software to avoid the Arch directory explosion.

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

This method will greatly reduce the production of arch, and to some extent improve time, according to experience, tens data can be completed in 45 minutes. Note, however, that this method is appropriate for the sequential way of a single process, and that if multiple processes are running at the same time, the process initiated will have enqueue waiting. Note that this method must not be dataguard, but if the database has been force logging that is not a problem.

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

For the statement after the select is a full table scan, we can add parallel hint to increase its concurrency, it should be noted that the maximum concurrency is limited by initialization parameters parallel_max_servers, concurrent processes can pass V$PX_ Session view, or Ps-ef |grep ora_p view.

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

In contrast to Method 2, concurrent inserts, which have not yet been compared and method 2 are more efficient (even if the method is 2 fast), have tested friends welcome to add.

INSERT INTO TAB1 SELECT * TAB2 partition (p1);　　
INSERT INTO TAB1 select * TAB2 partition (p2);
INSERT INTO TAB1 SELECT * TAB2 partition (p3);
INSERT INTO TAB1 select * TAB2 partition (p4);

For partitioned tables, you can use TAB1 to make concurrent inserts with multiple processes, and the more partitions, the more processes you can start. I have tried a table that inserts 260 million rows of records, 8 partitions, 8 processes, and if you use Method 2, a single process 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 about 110 minutes because it can be executed by concurrent processes.

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;

In the way of bulk binding (bulk binding). When iterating through the SQL statements of a bound variable, a large number of context switches (contexts switches) occur in the Pl/sql and SQL engines (engines). With bulk binding, the data can be transferred from the Plsql engine to the SQL engine, which reduces the context switching process and improves efficiency. This method is more suitable for on-line processing without downtime.

Sqlplus-s user/pwd< Runlog.txt set Copycommit 2;　　
set ArraySize 5000;
Copy from User/pwd@sid-to user/pwd@sid-insert tab1 using
select * from TAB2; exit EOF

Insert with the Copy method, note that insert does not have the INTO keyword here. The advantage of this approach is that you can set Copycommit and arrarysize to control the frequency of a commit, which is done every 10000 rows at a time.