批次Insert匯入資料實驗

換了家新公司，在新的電腦上需要架設新的虛擬機器，同時也需要重新匯入資料。

在受了Robinson的影響下，開始多做實驗，多記錄，多思考。

1.老的指令碼只是實現了業務需求，並沒有考慮過執行效率問題：

DECLARE

I NUMBER; H NUMBER; R VARCHAR2(100); S VARCHAR2(100); T VARCHAR2(100);

U VARCHAR2(100); d varchar2(12); c varchar2(8); z number;

SEED NUMBER := 1; start_time date; end_time date;

BEGIN

DBMS_RANDOM.initialize(SEED);

z := 1;

c := 100005;

start_time :=sysdate;

FOR I IN 1 .. 60000 LOOP

select to_char(trunc(sysdate) - z, 'YYYYMMDD') into d from dual;

R := d || ABS(DBMS_RANDOM.random() / SEED);

S := d || ABS(DBMS_RANDOM.random() / SEED);

R := substr(R, 1, 14);

S := substr(S, 1, 14);

c := c + 1;

H := d || c;

if c = '100205' then

z := z + 1;

c := 100005;

end if;

INSERT INTO MONEY_IO_LIST t

(t.SERIAL_NO, t.voucherno, t.FLOWNO, t.CURRENTLYTIME, t.MEMBERID, t.ACCOUNTTYPE, t.BANKID,

t.MONEYTYPE, t.APPLYMONEY, t.SPONSOR, t.CURRENCYTYPE, t.REVERSEFLAG, t.IOFLAG,

t.STATECODE, t.applyoperid_e, t.applytime_e, t.approve1opid, t.approve1optime, t.approve2opid,

t.approve2optime, t.applyinputopid, t.applyinputoptime, t.exchangeid, t.exchangeaccount, t.JIZHANGOPID, t.JIZHANGOPTIME,

t.businesscompleteopid, t.businesscompleteoptime)

VALUES

(H, H, H, R, '000' || trunc(dbms_random.value(1, 10)), '1', '0' || trunc(dbms_random.value(1, 5)), '07',

FUN_NUM_CONVERSION(trunc(dbms_random.value(1, 100000)), 'N'), 'M', 'RMB', '0', 'I', dbms_random.string('U', 1),

'系統處理', S, '系統處理', S, '系統處理2', S, '系統處理', S, 'CFFEX', '31001559100059008883', '系統處理',

S, '中金所PSIS系統', 'S');

COMMIT;

END LOOP;

end_time :=sysdate;

dbms_output.put_line((end_time-start_time)*24*60*60);

END;

執行花費時間:75秒

2.將表設定成nologging模式後

執行花費時間：69秒

3.在最佳化了程式碼，分為每500筆提交一次，而不是每一筆提交一次。同時使用了/*+Append*/之後

if c = '100505' then

z := z + 1;

c := 100005;

COMMIT;

end if;

執行花費時間:42秒

4.最後我們對指令碼進行大概，使用forall批次繫結：

DECLARE

I NUMBER; H NUMBER; R VARCHAR2(100); S VARCHAR2(100);

T VARCHAR2(100); U VARCHAR2(100); d varchar2(12);

c varchar2(8); z number; SEED NUMBER := 1; start_time date; end_time date;

TYPE type_serial IS TABLE OF money_io_list.serial_no%TYPE;

t_serial type_serial := type_serial();

TYPE type_current IS TABLE OF money_io_list.currentlytime%TYPE;

t_current type_current := type_current();

TYPE type_member IS TABLE OF money_io_list.memberid%TYPE;

t_member type_member := type_member();

TYPE type_time IS TABLE OF money_io_list.approve1optime%TYPE;

t_time type_time := type_time();

BEGIN

DBMS_RANDOM.initialize(SEED);

z := 1;

c := 100005;

start_time := sysdate;

FOR I IN 1 .. 60000 LOOP

select to_char(trunc(sysdate) - z, 'YYYYMMDD') into d from dual;

R := d || ABS(DBMS_RANDOM.random() / SEED);

S := d || ABS(DBMS_RANDOM.random() / SEED);

R := substr(R, 1, 14);

S := substr(S, 1, 14);

c := c + 1;

H := d || c;

if c = '100205' then

z := z + 1;

c := 100005;

end if;

t_serial.extend;

t_serial(i) := H;

t_current.extend;

t_current(i) := R;

t_member.extend;

t_member(i) := '000' || trunc(dbms_random.value(1, 10));

t_time.extend;

t_time(i) := S;

END LOOP;

forall i in t_serial.first .. t_serial.last

INSERT INTO MONEY_IO_LIST t

(t.SERIAL_NO, t.voucherno, t.FLOWNO, t.CURRENTLYTIME, t.MEMBERID, t.ACCOUNTTYPE, t.BANKID,

t.MONEYTYPE, t.APPLYMONEY, t.SPONSOR, t.CURRENCYTYPE, t.REVERSEFLAG, t.IOFLAG, t.STATECODE,

t.applyoperid_e, t.applytime_e, t.approve1opid, t.approve1optime, t.approve2opid, t.approve2optime, t.applyinputopid,

t.applyinputoptime, t.exchangeid, t.exchangeaccount, t.JIZHANGOPID, t.JIZHANGOPTIME, t.businesscompleteopid, t.businesscompleteoptime)

VALUES

(t_serial(i), t_serial(i), t_serial(i), t_current(i), t_member(i), '1', '0' || trunc(dbms_random.value(1, 5)), '07',

FUN_NUM_CONVERSION(trunc(dbms_random.value(1, 100000)), 'N'), 'M', 'RMB', '0', 'I', dbms_random.string('U', 1),

'系統處理', t_time(i), '系統處理', t_time(i), '系統處理2', t_time(i), '系統處理', t_time(i), 'CFFEX', '31001559100059008883',

'系統處理', t_time(i), '中金所PSIS系統', t_time(i));

Commit;

end_time := sysdate;

dbms_output.put_line((end_time - start_time) * 24 * 60 * 60);

END;

執行花費時間:25秒

Summary:

從75秒降低到25秒，只花了原來1/3的時間。看來批次繫結雖然寫法比較繁瑣但是效率還是很強大的。可能在更大的資料量上nologging會發揮更強大的效率。限於條件限制，這次沒有使用parallel來插入資料。

附:寫Forall時遇到的小問題 Ora-06531：Reference to uninitialized collection

今天除錯儲存過程出現“ORA-06531: 引用未初始化的收集”錯誤，仔細查詢了一下metalink，發現是需要初始化，而以前採用的表結構的都沒有作過初始化這個步驟，後來看了一下，是因為宣告的方式沒有按照表的方式宣告，而是自己手工寫的欄位。先寒一個！ 從metalink上面看到，這種型別應該像下面那樣使用：

–Create EMPLOYEE_TYPE
CREATE OR REPLACE type employee_type as object(
empid number,
empname varchar2(20),
job varchar2(20));
/
–Create EMPLOYEE_TAB_TYPE
CREATE OR REPLACE type employee_tab_type as table of employee_type ;
/
–Create DEPARTMENT_TYPE — using employee_tab_type
CREATE OR REPLACE type department_type as object(
deptid number,
deptname varchar2(20),
deptlocation varchar2(20),
emp_tab employee_tab_type);
/
DECLARE

/* Initialize the collection , else you will get ORA-06531: Reference to uninitialized collection */

my_emp employee_tab_type:=employee_tab_type();
my_dept department_type;
Begin
my_emp.extend; — 必須指定，否則會報指標越界
my_emp(1):=employee_type(2,’Savitha’,'mgr’); — 如果是record也可以直接賦值employee_type
my_dept :=department_type(1,’RESEARCH’,'India’,my_emp);
END;

[@more@]