批量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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12180666/viewspace-1040368/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12180666/viewspace-1040368/