水煮oracle29----oracle中對資料的批量操作測試
--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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 109.全文檢索-ElasticSearch-入門-刪除資料&bulk批量操作匯入樣本測試資料Elasticsearch
- Redis -對大資料量的 key 進行批量操作的一個記錄Redis大資料
- 通過shell指令碼批量操作mysql資料庫指令碼MySql資料庫
- 分享一個批量產生隨機測試資料的MySQL儲存過程隨機MySql儲存過程
- SQLAIchemy對資料基本操作SQLAI
- NodeJs批量require資料夾中的所有檔案NodeJSUI
- R語言批量提取excel當中的資料R語言Excel
- mysql的DDL操作對業務產生影響測試MySql
- 測試資料
- 生產資料庫、開發資料庫、測試資料庫中的資料的區分資料庫
- 大資料測試與 傳統資料庫測試大資料資料庫
- 提高對大批量資料時MV的重新整理效率
- 6.12php對資料庫的刪除和批量刪除PHP資料庫
- SQL—對資料表內容的基本操作SQL
- 對圖資料庫（Nebula）進行單元測試時的坑資料庫
- 功能測試之存量資料新與增資料測試
- Java8中的流操作-基本使用&效能測試Java
- 批量新建資料夾並命名的辦法 如何批量新建很多資料夾
- JB的測試之旅-測試資料的準備/構造
- teprunner測試平臺測試計劃批量執行用例
- 一個測試用例裡面有多套測試資料，如何用 beautifureport 分別對各組測試資料進行統計測試通過與否
- MyBatis中批量插入資料，多重forEach迴圈MyBatis
- 把多個資料夾中的檔案批量放到一個資料夾
- TestComplete資料驅動測試教程（二）——記錄測試資料
- MySQL 資料庫的對庫的操作及其資料型別悔鋒MySql資料庫資料型別
- Sovit2D對接MQTT資料來源的方法及測試MQQT
- Mybatis批量操作demoMyBatis
- mybatis插入資料、批量插入資料MyBatis
- indexedDB 批量新增資料Index
- 資料庫-批量更新資料庫
- 如何批量更新資料
- pymysql批量更新資料MySql
- 深圳軟體測試培訓：java中陣列的操作Java陣列
- MySQL製作具有千萬條測試資料的測試庫MySql
- 如何批量複製多個檔案到多個目錄中（批量複製檔案，多對多檔案高效操作的方法）
- 在Django中，多資料操作，你可以編寫測試來查詢另一個資料庫伺服器中的資料，並將結果匯入當前Django專案的資料庫表中Django資料庫伺服器
- CYQ.Data 操作 Redis 效能測試：對比 StackExchange.RedisRedis
- CYQ.Data 操作 Json 效能測試：對比 Newtonsoft.JsonJSON
- MySQL：JDBC批量插入資料的效率MySqlJDBC