在itpub论坛中看到有人归纳整理游标数据几种提取方式及提交方式的性能分析，收集的很全面，于是在本机也测试了一把，附上我的测试代码及测试数据。
先看游标数据读取提交的五种方式，如下：
1. 使用游标 逐条提取、逐条处理、逐条插入、逐条提交。
2. 使用游标 逐条提取、逐条处理、逐条插入、利用自定义计数变量分批提交 。
3. 同方案2,但是使用了ROWCOUNT变量分批提交。
4. 使用bulk collect limit方式提取数据,逐条处理,逐条插入,然后提交本次数据 。
5. 使用bulk collect limit方式提取数据,逐条处理,然后使用forall提交本次数据。
测试结果如下：
| COMMIT_COUNT | bulk_forall | bulk_for | cursor_rowconut | cursor_vcount | cursor_every_record |
| 500 | 2.000 | 3.000 | 7.000 | 6.000 | 9.000 |
| 1000 | 1.000 | 7.000 | 7.000 | 4.000 | 9.000 |
| 1500 | 3.000 | 4.000 | 6.000 | 5.000 | 10.000 |
| 2000 | 1.000 | 5.000 | 5.000 | 5.000 | 10.000 |
| 2500 | 1.000 | 5.000 | 6.000 | 5.000 | 10.000 |
| 3000 | 1.000 | 7.000 | 5.000 | 8.000 | 10.000 |
| 3500 | 2.000 | 3.000 | 6.000 | 5.000 | 11.000 |
| 4000 | 2.000 | 4.000 | 6.000 | 5.000 | 11.000 |
| 4500 | 2.000 | 4.000 | 5.000 | 5.000 | 12.000 |
| 5000 | 2.000 | 3.000 | 6.000 | 6.000 | 12.000 |
附测试代码如下：
1、表初始化代码：
SQL> create table BULK_LOG_BY_PENGWZH
2 (
3 id NUMBER(12),
4 type VARCHAR2(200),
5 start_time DATE,
6 end_time DATE,
7 used_time_in_seconds NUMBER(12,3)
8 );
表已创建。
SQL> create table BULK_RESULT_BY_PENGWZH
2 (
3 id NUMBER(12),
4 type VARCHAR2(200),
5 nresult NUMBER(12),
6 vresult VARCHAR2(600)
7 );
表已创建。
SQL> create table BULK_TEST_BY_PENGWZH
2 (
3 id NUMBER(12),
4 n1 NUMBER(12),
5 n2 NUMBER(12),
6 n3 NUMBER(12),
7 v1 VARCHAR2(200),
8 v2 VARCHAR2(200),
9 v3 VARCHAR2(200)
10 );
表已创建。
2、测试包及包体代码如下：
----------------------------------------------------
-- Export file for user TEST --
-- Created by Administrator on 2014/3/6, 15:56:01 --
----------------------------------------------------
prompt
prompt Creating view QUERY_RESULT_BY_PENGWZH
prompt =====================================
prompt
create or replace view test.query_result_by_pengwzh as
select id,
regexp_substr(type, '[^0-9]+') type,
to_number(regexp_substr(type, '[0-9]+')) commit_count,
t.used_time_in_seconds
from BULK_LOG_BY_PENGWZH t
order by 2, 3, 4;
prompt
prompt Creating package P_BULK_TEST
prompt ============================
prompt
create or replace package test.p_bulk_test is
-- Author : ADMINISTRATOR
-- Created : 2014/3/6 13:54:34
-- Purpose :
procedure p_bulk_test_1_BY_PENGWZH(v_count number);
procedure p_bulk_test_2_BY_PENGWZH(v_count number);
procedure p_cursor_test_1_BY_PENGWZH;
procedure p_cursor_test_2_BY_PENGWZH(v_commit number);
procedure p_cursor_test_3_BY_PENGWZH(v_commit number);
procedure p_init_data_BY_PENGWZH(v_count number);
procedure p_test_BY_PENGWZH;
end p_bulk_test;
/
prompt
prompt Creating package body P_BULK_TEST
prompt =================================
prompt
create or replace package body test.p_bulk_test is
procedure p_bulk_test_1_BY_PENGWZH(v_count number) as
/*
*bulk collect
*每次从游标中提取 v_count 条数据
*for 循环逐条处理insert
*for 循环之后提交当前数据
*此模式下 exit 不能紧跟fetch,否则会缺失数据
*/
v_id number(12);
v_name varchar2(200) := 'BULK_TEST_COMMIT_BY_AFTER_FOR_EVERY_' ||
to_char(v_count) || '_RECORD';
cursor mycur is
select s.id, s.n1, s.n2, s.n3, s.v1, s.v2, s.v3
from bulk_test_BY_PENGWZH s;
type v_line_table is table of bulk_test_BY_PENGWZH%rowtype index by binary_integer;
v_lines v_line_table;
v_line bulk_test_BY_PENGWZH%rowtype;
v_n_result number(12);
v_v_result varchar2(600);
begin
execute immediate 'truncate table bulk_result_BY_PENGWZH'; --不计入时间
--取下一日志编号
select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
--记录日志
insert into bulk_log_BY_PENGWZH
(id, type, start_time)
values
(v_id, v_name, sysdate);
commit;
/*****************************************/
open mycur;
loop
fetch mycur bulk collect
into v_lines limit v_count;
for i in 1 .. v_lines.count loop
v_line := v_lines(i);
v_n_result := v_line.n1 + v_line.n2 + v_line.n3;
v_v_result := v_line.v1 || v_line.v2 || v_line.v3;
insert into bulk_result_BY_PENGWZH
(id, nresult, vresult)
values
(v_line.id, v_n_result, v_v_result);
end loop;
commit;
exit when mycur%notfound;
end loop;
/*****************************************/
--更新日志
update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
update bulk_log_BY_PENGWZH
set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
3)
where id = v_id;
commit;
end;
procedure p_bulk_test_2_BY_PENGWZH(v_count number) as
/*
*bulk collect
*每次从游标中提取 v_count 条数据
*for 循环逐条处理
*for 循环之后使用forall提交当前数据
*此模式下 exit 不能紧跟fetch,否则会缺失数据
*/
v_id number(12);
v_name varchar2(200) := 'BULK_TEST_COMMIT_BY_AFTER_FORALL_EVERY_' ||
to_char(v_count) || '_RECORD';
cursor mycur is
select s.id, s.n1, s.n2, s.n3, s.v1, s.v2, s.v3
from bulk_test_BY_PENGWZH s;
type v_line_table is table of bulk_test_BY_PENGWZH%rowtype index by binary_integer;
v_lines v_line_table;
v_line bulk_test_BY_PENGWZH%rowtype;
v_n_result number(12);
v_v_result varchar2(600);
type v_id_table is table of number(12) index by binary_integer;
type v_n_table is table of number(12) index by binary_integer;
type v_r_table is table of varchar2(600) index by binary_integer;
v_ids v_id_table;
v_ns v_n_table;
v_vs v_r_table;
begin
execute immediate 'truncate table bulk_result_BY_PENGWZH'; --不计入时间
--取下一日志编号
select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
--记录日志
insert into bulk_log_BY_PENGWZH
(id, type, start_time)
values
(v_id, v_name, sysdate);
commit;
/*****************************************/
open mycur;
loop
fetch mycur bulk collect
into v_lines limit v_count;
for i in 1 .. v_lines.count loop
v_line := v_lines(i);
v_ids(i) := v_line.id;
v_n_result := v_line.n1 + v_line.n2 + v_line.n3;
v_ns(i) := v_n_result;
v_v_result := v_line.v1 || v_line.v2 || v_line.v3;
v_vs(i) := v_v_result;
end loop;
forall i in 1 .. v_lines.count
insert into bulk_result_BY_PENGWZH
(id, nresult, vresult)
values
(v_ids(i), v_ns(i), v_vs(i));
commit;
exit when mycur%notfound;
end loop;
/*****************************************/
--更新日志
update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
update bulk_log_BY_PENGWZH
set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
3)
where id = v_id;
commit;
end;
procedure p_cursor_test_1_BY_PENGWZH as
/*
*每条提交
*逐条fetch的游标在fetch后必须紧跟exit
*/
v_id number(12);
v_name varchar2(200) := 'CURSOR_TEST_COMMIT_EVERY_RECORD';
cursor mycur is
select s.id, s.n1, s.n2, s.n3, s.v1, s.v2, s.v3
from bulk_test_BY_PENGWZH s;
v_line bulk_test_BY_PENGWZH%rowtype;
v_n_result number(12);
v_v_result varchar2(600);
begin
execute immediate 'truncate table bulk_result_BY_PENGWZH'; --不计入时间
--取下一日志编号
select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
--记录日志
insert into bulk_log_BY_PENGWZH
(id, type, start_time)
values
(v_id, v_name, sysdate);
commit;
/*****************************************/
open mycur;
loop
fetch mycur
into v_line;
exit when mycur%notfound;
v_n_result := v_line.n1 + v_line.n2 + v_line.n3;
v_v_result := v_line.v1 || v_line.v2 || v_line.v3;
insert into bulk_result_BY_PENGWZH
(id, nresult, vresult)
values
(v_line.id, v_n_result, v_v_result);
commit;
end loop;
close mycur;
/*****************************************/
--更新日志
update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
update bulk_log_BY_PENGWZH
set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
3)
where id = v_id;
commit;
end;
procedure p_cursor_test_2_BY_PENGWZH(v_commit number) as
/*
*每N条提交
*利用 mycur%rowcount 进行每 v_commit 条进行提交
*逐条fetch的游标在fetch后必须紧跟exit
*/
v_id number(12);
v_name varchar2(200) := 'CURSOR_TEST_COMMIT_BY_ROWCOUNT_EVERY_' ||
to_char(v_commit) || '_RECORD';
cursor mycur is
select s.id, s.n1, s.n2, s.n3, s.v1, s.v2, s.v3
from bulk_test_BY_PENGWZH s;
v_line bulk_test_BY_PENGWZH%rowtype;
v_n_result number(12);
v_v_result varchar2(600);
begin
execute immediate 'truncate table bulk_result_BY_PENGWZH'; --不计入时间
--取下一日志编号
select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
--记录日志
insert into bulk_log_BY_PENGWZH
(id, type, start_time)
values
(v_id, v_name, sysdate);
commit;
/*****************************************/
open mycur;
loop
fetch mycur
into v_line;
exit when mycur%notfound;
v_n_result := v_line.n1 + v_line.n2 + v_line.n3;
v_v_result := v_line.v1 || v_line.v2 || v_line.v3;
insert into bulk_result_BY_PENGWZH
(id, nresult, vresult)
values
(v_line.id, v_n_result, v_v_result);
if mod(mycur%rowcount, v_commit) = 0 then
commit;
end if;
end loop;
commit;
close mycur;
/*****************************************/
--更新日志
update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
update bulk_log_BY_PENGWZH
set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
3)
where id = v_id;
commit;
end;
procedure p_cursor_test_3_BY_PENGWZH(v_commit number) as
/*
*每N条提交
*利用 计数变量 进行每 v_commit 条进行提交
*逐条fetch的游标在fetch后必须紧跟exit
*/
v_count number(12) := 0;
v_id number(12);
v_name varchar2(200) := 'CURSOR_TEST_COMMIT_BY_VCOUNT_EVERY_' ||
to_char(v_commit) || '_RECORD';
cursor mycur is
select s.id, s.n1, s.n2, s.n3, s.v1, s.v2, s.v3
from bulk_test_BY_PENGWZH s;
v_line bulk_test_BY_PENGWZH%rowtype;
v_n_result number(12);
v_v_result varchar2(600);
begin
execute immediate 'truncate table bulk_result_BY_PENGWZH'; --不计入时间
--取下一日志编号
select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
--记录日志
insert into bulk_log_BY_PENGWZH
(id, type, start_time)
values
(v_id, v_name, sysdate);
commit;
/*****************************************/
open mycur;
loop
fetch mycur
into v_line;
exit when mycur%notfound;
v_n_result := v_line.n1 + v_line.n2 + v_line.n3;
v_v_result := v_line.v1 || v_line.v2 || v_line.v3;
insert into bulk_result_BY_PENGWZH
(id, nresult, vresult)
values
(v_line.id, v_n_result, v_v_result);
v_count := v_count + 1;
if mod(v_count, v_commit) = 0 then
commit;
end if;
end loop;
commit;
close mycur;
/*****************************************/
--更新日志
update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
update bulk_log_BY_PENGWZH
set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
3)
where id = v_id;
commit;
end;
procedure p_init_data_BY_PENGWZH(v_count number) as
v_id number(12);
begin
--清空表
execute immediate 'truncate table bulk_test_BY_PENGWZH';
--取下一日志编号
select nvl(max(id), 0) + 1 into v_id from bulk_log_BY_PENGWZH;
--记录日志
insert into bulk_log_BY_PENGWZH
(id, type, start_time)
values
(v_id, 'init_data_' || to_char(v_count), sysdate);
--插入数据
for i in 1 .. v_count loop
insert into bulk_test_BY_PENGWZH
(id, n1, n2, n3, v1, v2, v3)
values
(i,
round(dbms_random.value * 1000000),
round(dbms_random.value * 1000000),
round(dbms_random.value * 1000000),
dbms_random.string('a', round(dbms_random.value * 200)),
dbms_random.string('a', round(dbms_random.value * 200)),
dbms_random.string('a', round(dbms_random.value * 200)));
end loop;
--提交
commit;
--更新日志
update bulk_log_BY_PENGWZH set end_time = sysdate where id = v_id;
update bulk_log_BY_PENGWZH
set used_time_in_seconds = round((end_time - start_time) * 24 * 60 * 60,
3)
where id = v_id;
commit;
end;
procedure p_test_BY_PENGWZH as
v_init_count number(12) := 100000; --测试数据量
v_commit_count number(12);
v_start_size number(12) := 500; --初始值
v_test_count number(12) := 10; --测试次数
v_step_size number(12) := 500; --步进
begin
execute immediate 'truncate table bulk_log_BY_PENGWZH';
--初始化测试数据
p_init_data_BY_PENGWZH(v_init_count);
for i in 1 .. v_test_count loop
--500, 1000, 1500 ... 10000
v_commit_count := v_start_size + v_step_size * (i - 1);
p_cursor_test_1_BY_PENGWZH;
p_cursor_test_2_BY_PENGWZH(v_commit_count);
p_cursor_test_3_BY_PENGWZH(v_commit_count);
p_bulk_test_1_BY_PENGWZH(v_commit_count);
p_bulk_test_2_BY_PENGWZH(v_commit_count);
end loop;
end;
end p_bulk_test;
/
3、测试方法说明
执行exec p_bulk_test.p_test_BY_PENGWZH;
查看结果可直接查询视图QUERY_RESULT_BY_PENGWZH即可。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21251711/viewspace-1102679/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
