Oracle數(shù)據(jù)庫大表更新優(yōu)化記錄
業(yè)務(wù)環(huán)境：為相應(yīng)根據(jù)國家脫貧攻堅(jiān)政策，年底達(dá)到完全脫貧的艱巨任務(wù)，全國上下齊心協(xié)力，為這一歷史性艱巨任務(wù)砥礪前行。目前以接近尾聲，陜西精準(zhǔn)扶貧大數(shù)據(jù)平臺(tái)做數(shù)據(jù)抽取和各種統(tǒng)計(jì)任務(wù)，平臺(tái)所具有的特點(diǎn)是數(shù)據(jù)量非常大，因此在做相關(guān)數(shù)據(jù)抽取加上多表關(guān)聯(lián)就會(huì)導(dǎo)致執(zhí)行速度很慢，而且非常消耗系統(tǒng)資源。
業(yè)務(wù)場景：本次業(yè)務(wù)是有關(guān)大數(shù)據(jù)平臺(tái)將幫扶措施中的9項(xiàng)措施反推更新大戶表中，著兩個(gè)表的數(shù)據(jù)量都特別大，措施大概有1900萬條數(shù)據(jù)，戶表當(dāng)前年有140萬條數(shù)據(jù)，歷史大概有800萬條數(shù)據(jù)。
從2020-12-03下午15:00開始執(zhí)行相關(guān)查詢并更新
--更新fpxm_type_id值至aa01_2014表中屬性
update aa01_2014
set fcfs = (select wm_concat(distinct fpxm_type_id) || ','as fpxm_type_id from tbl_fpxm_poor_cs_temp
where aa01_2014.aaa001 = tbl_fpxm_poor_cs_temp.poor_id
and tbl_fpxm_poor_cs_temp.data_year = to_char(sysdate,'yyyy')
group by poor_id,data_year) --現(xiàn)根據(jù)戶ID分組，在根據(jù)年拼接出這一戶在不同年份享受的政策編碼，
where exists (select 1 from tbl_fpxm_poor_cs_temp where aa01_2014.aaa001 = tbl_fpxm_poor_cs_temp.poor_id)
結(jié)果到第二天早晨來執(zhí)行了1100多分鐘還是沒有執(zhí)行完畢，于是拋棄這個(gè)方式。分析原因因?yàn)檫@個(gè)更新在查詢有用字段的時(shí)候使用兩個(gè)表id關(guān)聯(lián)，這就會(huì)導(dǎo)致在oracle掃描次數(shù)多達(dá)140萬*1900萬次，100億次之多，執(zhí)行到入土了也可能執(zhí)行不完，于是開始優(yōu)化。
昨天更新的執(zhí)行報(bào)告，what fuck,查看執(zhí)行報(bào)告如下：
優(yōu)化
通過在網(wǎng)上查找資料發(fā)現(xiàn)在更新的時(shí)候可以換一種書寫方式少一次對(duì)庫的掃描，
如下：
update customers a
set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
where exists (select 1
from tmp_cust_city b
where b.customer_id=a.customer_id
)
update (select a.city_name,b.city_name as new_name
from customers a,
tmp_cust_city b
where b.customer_id=a.customer_id
)
set city_name=new_name
但是我沒有使用這個(gè)例子。
我這里采用的是數(shù)據(jù)庫開啟并行執(zhí)行
概念：
<html>
一、oracle 并行執(zhí)行
優(yōu)勢：強(qiáng)制啟動(dòng)并行進(jìn)程、分配任務(wù)與系統(tǒng)資源、合并結(jié)果集。大大縮短計(jì)算時(shí)間。在大表查詢等操作中能夠起到良好的效果。在ODS系統(tǒng)中報(bào)表統(tǒng)計(jì)等方面更有使用意義。
劣勢：比較消耗資源，不建議在系統(tǒng)超負(fù)荷運(yùn)行的情況下使用。
注意事項(xiàng)：/+parallel(t,n)/中，t代表表別名或者表明（沒有起別名情況）；n代表進(jìn)程數(shù)量，一般值為：cpu數(shù)量-1。
例如：SELECT /+parallel(a,16)/ distinct a.comcode FROM statcmain a where a.underwriteenddate BETWEEN DATE'2011-1-1' AND DATE'2014-1-31';
一般而言主要在如下情況使用parallel HINT：
1.表的數(shù)據(jù)量很大,超過一千萬;
2.數(shù)據(jù)庫主機(jī)是多個(gè)CPU;
3.系統(tǒng)的當(dāng)前負(fù)載較低;
</html>
merge into使用模板
merge into 目標(biāo)表 a
using 源表 b
on(a.條件字段1=b.條件字段1 and a.條件字段2=b.條件字段2 ……)
when matched then update set a.更新字段=b.字段
when not matched then insert into a(字段1,字段2……)values(值1,值2……)
explain plan FOR merge into aa01_2014 a
using (select /*+parallel(tbl_fpxm_poor_cs_temp,16)*/ poor_id,wm_concat(distinct fpxm_type_id) || ','as fpxm_type_id
from tbl_fpxm_poor_cs_temp left join aa01_2014 on aa01_2014.aaa001 =tbl_fpxm_poor_cs_temp.poor_id
where tbl_fpxm_poor_cs_temp.data_year = to_char(sysdate,'yyyy')
group by poor_id,data_year) temp
on (a.aaa001 = temp.poor_id)
when matched then update set a.fcfs = temp.fpxm_type_id;
--查看執(zhí)行報(bào)告
select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
執(zhí)行時(shí)間如下圖：
開啟并行執(zhí)行后執(zhí)行報(bào)告：
<html>
結(jié)果對(duì)比：update和merge into 都更新100億條記錄，update耗時(shí)999:59:59，邏輯讀消耗2282027；merge into 耗時(shí)04.38s，消耗邏輯讀964.相差太大了。
其實(shí)看著執(zhí)行計(jì)劃，這個(gè)結(jié)果也很容易理解：update采用的類似nested loop的方式，對(duì)更新的每一行，都會(huì)對(duì)查詢的表掃描一次；merge into這里選擇的是hash join，
則針對(duì)每張表都是做了一次 full table scan，對(duì)每張表都只是掃描一次。
</html>
快速游標(biāo)方式：
begin
for tbl_fpxm_poor_cs_temp in (select poor_id,wm_concat(distinct fpxm_type_id) || ','as fpxm_type_id
from tbl_fpxm_poor_cs_temp left join aa01_2014 on aa01_2014.aaa001=tbl_fpxm_poor_cs_temp.poor_id
where tbl_fpxm_poor_cs_temp.data_year = to_char(sysdate,'yyyy')
group by poor_id,data_year) loop
update aa01_2014 set aa01_2014.fcfs = tbl_fpxm_poor_cs_temp.fpxm_type_id where aa01_2014.aaa001=tbl_fpxm_poor_cs_temp.poor_id;
end loop;
end;
快速游標(biāo)方式：
但是這種方式提示wm_concat(distinct fpxm_type_id)連接函數(shù)中不能使用distinct關(guān)鍵字，如圖
于是修改查詢語句
begin
for tbl_fpxm_poor_cs_temp in (select poor_id,listagg(fpxm_type_id,',') within group( order by fpxm_type_id) || ',' as fpxm_type_id,data_year from
(select DISTINCT fpxm_type_id,data_year,poor_id from tbl_fpxm_poor_cs_temp where tbl_fpxm_poor_cs_temp.data_year = to_char(sysdate,'yyyy'))
group by poor_id,data_year) loop
update aa01_2014 set aa01_2014.fcfs = tbl_fpxm_poor_cs_temp.fpxm_type_id where aa01_2014.aaa001=tbl_fpxm_poor_cs_temp.poor_id;
end loop;
end;
oracle更新大量數(shù)據(jù)太慢，可以通過游標(biāo)實(shí)現(xiàn)的例子
declare cursor city_cur is
select t.new_customer_id,t.old_customer_id from
citsonline.crm_customer_tmp6 t
where t.new_customer_id!=t.old_customer_id
order by new_customer_id;
begin
for my_cur in city_cur loop
update platform.crm_service_customer_bak s
set s.customer_id=my_cur.new_customer_id
where s.customer_id=my_cur.old_customer_id;
/** 此處也可以單條/分批次提交，避免鎖表情況 **/
if mod(city_cur%rowcount,1000)=0 then
dbms_output.put_line('----');
commit;
end if;
end loop;
commit;
end;
根據(jù)案例修改后的語句
declare cursor fpxm_cur is
select /*+parallel(tbl_fpxm_poor_cs_temp,16)*/ poor_id,
listagg(fpxm_type_id,',') within group( order by fpxm_type_id) || ',' as fpxm_type_id,
data_year
from
--去除重復(fù)
(select DISTINCT fpxm_type_id,data_year,poor_id from tbl_fpxm_poor_cs_temp where tbl_fpxm_poor_cs_temp.data_year = to_char(sysdate,'yyyy'))
group by poor_id,data_year;
begin for tbl_fpxm_poor_cs_temp in fpxm_cur loop
update aa01_2014 set aa01_2014.fcfs = tbl_fpxm_poor_cs_temp.fpxm_type_id where aa01_2014.aaa001=tbl_fpxm_poor_cs_temp.poor_id;
/** 此處也可以單條/分批次提交，避免鎖表情況 **/
if mod(fpxm_cur%rowcount,1000)=0 then
dbms_output.put_line('----');
commit;
end if;
end loop;
commit;
end;
可以看出執(zhí)行速度是相當(dāng)可觀，但是如果同時(shí)去掉并行執(zhí)行速度更快3s不知道為啥。
如下圖：
案例
這今天在研究kettle工具，是一款國外純java開發(fā)的開源ETL工具，抽取數(shù)據(jù)確實(shí)非常方便，大家有空可以去下載下來試試看，方便之處在于它不用安裝，解壓完了就能直接用了（必須提前配置jdk和jre環(huán)境到系統(tǒng)環(huán)境中）。今天要說的不是這款軟件，問題是由使用這個(gè)軟件引起的，我在抽取數(shù)據(jù)后需要完成一個(gè)更新操作語句如下：
update case_person_saxx a set a.case_id=(select id from case_xzcf b where b.app_id = a.app_id) ；
update invole_case_unit_saxx a set a.case_id=(select id from case_xzcf b where b.app_id = a.app_id)；
上面的語句中case_person_saxx表和case_xzcf 表中數(shù)據(jù)量大概在16萬條左右，說起來也不是特別大，但是這個(gè)語句執(zhí)行起來特別的慢，我等了半個(gè)多小時(shí)都沒執(zhí)行完，后來建索引稍微快一點(diǎn)，在網(wǎng)上找到一種更快捷的更新語句（因?yàn)槲覕?shù)據(jù)庫基礎(chǔ)不好很多語句不熟悉，呵呵！大神來看到了別笑話我就行?。┤缦拢?/p>
merge into case_person_saxx t
using (select max(id) as id, app_id from case_xzcf group by app_id) s
on (t.app_id = s.app_id)
when matched then
update set t.case_id = s.id;
merge into invole_case_unit_saxx t
using (select max(id) as id, app_id from case_xzcf group by app_id) s
on (t.app_id = s.app_id)
when matched then
update set t.case_id = s.id;
記言：如果你的才華不能匹配你的野心，請(qǐng)努力！
參考文獻(xiàn)：
一、
<html>
執(zhí)行報(bào)告分析：
explain plan FOR select 1 from dual;
select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
</html>
二、快速游標(biāo)法
方式二
三、merge into 和單個(gè)update 執(zhí)行效率分析
