--1、如果数据量不大,尽量少使用复杂语句
--2、可以使用/*+append*/和nologging方式,对块操作和不产生redo log
--3、如果数据量较大,可以考虑分批提交方式,来减轻undo和temp压力
--update
--1、如果数量不大的,尽量只是用forall批量操作(需要确保memory足够,以及undo足够)
--2、在用update大批量更新时,一定要对where条件中的列,做索引处理
--------------------------------
declare
start_time number;
end_time number;
begin
start_time := dbms_utility.get_time;
insert into test
select level id, 'good' || level, level
from dual
connect by level <= 2000000;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
commit;
end;
1、普通插入
time:6.77
PL/SQL procedure successfully completed
Executed in 6.782 seconds
---------------------------------
declare
start_time number;
end_time number;
begin
start_time := dbms_utility.get_time;
insert /*+append*/ into test nologging
select level id, 'good' || level, level
from dual
connect by level <= 2000000;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
commit;
end;
2、带有/*+append*/和nologging的插入
time:4.06
PL/SQL procedure successfully completed
Executed in 4.125 seconds
---------------------------------------------------
declare
type test_table_type is table of test%rowtype index by binary_integer;
test_table test_table_type;
start_time number;
end_time number;
begin
select * bulk collect into test_table from
(select level id, 'good' || level, level
from dual
connect by level <= 2000000);
start_time := dbms_utility.get_time;
forall i in 1..test_table.count
insert /*+append*/ into test nologging values(test_table(i).id,test_table(i).name,test_table(i).salary);
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
commit;
end;
3、批量插入(使用bulk collect、forall)
time:4.94
PL/SQL procedure successfully completed
Executed in 11.032 seconds
------------------------------------------------------
---update操作
-----------------------------------------------------
declare
start_time number;
end_time number;
begin
start_time := dbms_utility.get_time;
update test set salary=salary+3;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
commit;
end;
1、普通的update
time:100.92
PL/SQL procedure successfully completed
Executed in 100.953 seconds
-------------------------------------------------------
declare
type test_table_type is table of test%rowtype index by binary_integer;
test_table test_table_type;
start_time number;
end_time number;
begin
start_time := dbms_utility.get_time;
select * bulk collect into test_table from test;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
start_time := dbms_utility.get_time;
dbms_output.put_line(test_table.count);
forall i in 1..test_table.count
update test set salary=test_table(i).salary+3 where id=test_table(i).id;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
commit;
end;
2、批量update(带有forall和bulk collect)
time:2.75
2000000
time:72.15
PL/SQL procedure successfully completed
Executed in 75.062 seconds
--------------------------------------------------------------------------
declare
type test_table_type is table of test%rowtype index by binary_integer;
test_table test_table_type;
start_time number;
end_time number;
a number;
begin
start_time := dbms_utility.get_time;
select * bulk collect into test_table from test;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
start_time := dbms_utility.get_time;
dbms_output.put_line(test_table.count);
for a in 1..20 loop
forall i in (a-1)*100000+1..a*100000
update test set salary=test_table(i).salary+3 where id=test_table(i).id;
end loop;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
commit;
end;
3、对大批量1000w级,可以使用
time:2.49
2000000
time:84.9
PL/SQL procedure successfully completed
Executed in 87.531 seconds
--------------------------------------------------------------------------
declare
type test_table_type is table of test%rowtype index by binary_integer;
test_table test_table_type;
start_time number;
end_time number;
a number;
begin
start_time := dbms_utility.get_time;
select * bulk collect into test_table from test;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
start_time := dbms_utility.get_time;
dbms_output.put_line(test_table.count);
for a in 1..20 loop
forall i in (a-1)*100000+1..a*100000
update test set salary=test_table(i).salary+3 where id=test_table(i).id;
commit;
end loop;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
commit;
end;
-----------------------------------------------------------------4、中间增加了commit,在undo空间不够用时使用
time:2.48
2000000
time:91.06
PL/SQL procedure successfully completed
Executed in 93.703 seconds
------------------------------------------------------------------
declare
type test_table_type is table of test%rowtype index by binary_integer;
test_table test_table_type;
start_time number;
end_time number;
a number;
begin
start_time := dbms_utility.get_time;
select * bulk collect into test_table from test;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
start_time := dbms_utility.get_time;
dbms_output.put_line(test_table.count);
for a in 1..200 loop
forall i in (a-1)*10000+1..a*10000
update test set salary=test_table(i).salary+3 where id=test_table(i).id;
commit;
end loop;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
commit;
end;
5、测试循环对其影响
time:2.5
2000000
time:93.09
PL/SQL procedure successfully completed
Executed in 95.734 seconds
-------------------------------------------------------------
declare
type test_table_type is table of test%rowtype index by binary_integer;
test_table test_table_type;
start_time number;
end_time number;
a number;
begin
select * bulk collect into test_table from test;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
start_time := dbms_utility.get_time;
dbms_output.put_line(test_table.count);
for i in 1 .. test_table.count loop
update test
set salary = test_table(i).salary + 3
where id = test_table(i).id;
a := a + 1;
if mod(a, 500000) = 0 then
commit;
end if;
end loop;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
end;
6、不使用forall的批量更新(主要是批量提交)
time:221.83
PL/SQL procedure successfully completed
Executed in 224.532 seconds
-----------------------------------------------------
-------------------------------------------------------
declare
type test_table_type is table of test%rowtype index by binary_integer;
test_table test_table_type;
start_time number;
end_time number;
a number;
begin
start_time := dbms_utility.get_time;
select * bulk collect into test_table from test;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
start_time := dbms_utility.get_time;
dbms_output.put_line(test_table.count);
for b in 1..20 loop
for i in (b-1)*100000+1..b*100000 loop
update test set salary=test_table(i).salary+3 where id=test_table(i).id;
a:=a+1;
if mod(a,500000)=0 then
commit;
end if;
end loop;
end loop;
end_time := dbms_utility.get_time;
dbms_output.put_line('time:' || (end_time - start_time) / 100);
commit;
end;
7、测试循环影响
time:210.06
PL/SQL procedure successfully completed
Executed in 213.094 seconds
------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23890223/viewspace-773489/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23890223/viewspace-773489/