批量載入效能案例

客戶在大量載入資料是遇到效能問題，檢查後發現客戶採用的是單條插入單條提交這種最緩慢的方式，為了給客戶說明優化效果，現場做了幾個程式碼。

最簡單的優化方式莫過於減少COMMIT頻度，而最優化的方式是採用批量插入的方式，簡單的測試程式碼如下：

SQL> CREATE TABLE T_INSERT (ID NUMBER, NAME VARCHAR2(30));

Table created.

SQL> SET TIMING ON
SQL> BEGIN
2 FOR I IN 1..100000 LOOP
3 INSERT INTO T_INSERT VALUES (I, 'A' || I);
4 COMMIT;
5 END LOOP;
6 END;
7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.22
SQL> BEGIN
2 FOR I IN 1..100000 LOOP
3 INSERT INTO T_INSERT VALUES (I, 'A' || I);
4 COMMIT;
5 END LOOP;
6 END;
7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.51
SQL> BEGIN
2 FOR I IN 1..100000 LOOP
3 INSERT INTO T_INSERT VALUES (I, 'A' || I);
4 IF MOD(I, 1000) = 0 THEN
5 COMMIT;
6 END IF;
7 END LOOP;
8 COMMIT;
9 END;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.01
SQL> BEGIN
2 FOR I IN 1..100000 LOOP
3 INSERT INTO T_INSERT VALUES (I, 'A' || I);
4 IF MOD(I, 1000) = 0 THEN
5 COMMIT;
6 END IF;
7 END LOOP;
8 COMMIT;
9 END;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.64
SQL> DECLARE
2 TYPE T_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 TYPE T_VAR IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
4 V_NUM T_NUM;
5 V_VAR T_VAR;
6 BEGIN
7 FOR I IN 1..100000 LOOP
8 V_NUM(I) := I;
9 V_VAR(I) := 'A' || I;
10 END LOOP;
11 FORALL I IN 1..100000
12 INSERT INTO T_INSERT VALUES (V_NUM(I), V_VAR(I));
13 COMMIT;
14 END;
15 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.37
SQL> DECLARE
2 TYPE T_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 TYPE T_VAR IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
4 V_NUM T_NUM;
5 V_VAR T_VAR;
6 BEGIN
7 FOR I IN 1..100000 LOOP
8 V_NUM(I) := I;
9 V_VAR(I) := 'A' || I;
10 END LOOP;
11 FORALL I IN 1..100000
12 INSERT INTO T_INSERT VALUES (V_NUM(I), V_VAR(I));
13 COMMIT;
14 END;
15 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.50

這個例子明確說明了單條提交、批量提交以及數值插入的效能差異，很多時候只是口頭上的描述，客戶不會有太深的印象，而如果通過這種例子來展示效能的差別，結果一目瞭然，比再多的描述都管用得多。