Oracle數(shù)據(jù)庫大表更新優(yōu)化記錄
業(yè)務環(huán)境：為相應根據(jù)國家脫貧攻堅政策兵扬，年底達到完全脫貧的艱巨任務麻裳，全國上下齊心協(xié)力，為這一歷史性艱巨任務砥礪前行器钟。目前以接近尾聲津坑，陜西精準扶貧大數(shù)據(jù)平臺做數(shù)據(jù)抽取和各種統(tǒng)計任務，平臺所具有的特點是數(shù)據(jù)量非常大傲霸，因此在做相關數(shù)據(jù)抽取加上多表關聯(lián)就會導致執(zhí)行速度很慢国瓮，而且非常消耗系統(tǒng)資源。
業(yè)務場景：本次業(yè)務是有關大數(shù)據(jù)平臺將幫扶措施中的9項措施反推更新大戶表中狞谱，著兩個表的數(shù)據(jù)量都特別大乃摹，措施大概有1900萬條數(shù)據(jù)，戶表當前年有140萬條數(shù)據(jù)跟衅，歷史大概有800萬條數(shù)據(jù)孵睬。
從2020-12-03下午15:00開始執(zhí)行相關查詢并更新
--更新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)
結果到第二天早晨來執(zhí)行了1100多分鐘還是沒有執(zhí)行完畢掰读，于是拋棄這個方式。分析原因因為這個更新在查詢有用字段的時候使用兩個表id關聯(lián)叭莫，這就會導致在oracle掃描次數(shù)多達140萬*1900萬次蹈集，100億次之多，執(zhí)行到入土了也可能執(zhí)行不完雇初，于是開始優(yōu)化拢肆。
昨天更新的執(zhí)行報告，what fuck,查看執(zhí)行報告如下：
優(yōu)化
通過在網(wǎng)上查找資料發(fā)現(xiàn)在更新的時候可以換一種書寫方式少一次對庫的掃描靖诗，
如下：
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
但是我沒有使用這個例子郭怪。
我這里采用的是數(shù)據(jù)庫開啟并行執(zhí)行
概念：
<html>
一、oracle 并行執(zhí)行
優(yōu)勢：強制啟動并行進程刊橘、分配任務與系統(tǒng)資源鄙才、合并結果集。大大縮短計算時間促绵。在大表查詢等操作中能夠起到良好的效果攒庵。在ODS系統(tǒng)中報表統(tǒng)計等方面更有使用意義。
劣勢：比較消耗資源败晴，不建議在系統(tǒng)超負荷運行的情況下使用浓冒。
注意事項：/+parallel(t,n)/中，t代表表別名或者表明（沒有起別名情況）位衩；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ù)庫主機是多個CPU;
3.系統(tǒng)的當前負載較低;
</html>
merge into使用模板
merge into 目標表 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í)行報告
select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
執(zhí)行時間如下圖：
開啟并行執(zhí)行后執(zhí)行報告：
<html>
結果對比：update和merge into 都更新100億條記錄糖驴，update耗時999:59:59僚祷，邏輯讀消耗2282027；merge into 耗時04.38s贮缕，消耗邏輯讀964.相差太大了辙谜。
其實看著執(zhí)行計劃，這個結果也很容易理解：update采用的類似nested loop的方式感昼，對更新的每一行装哆，都會對查詢的表掃描一次；merge into這里選擇的是hash join定嗓，
則針對每張表都是做了一次 full table scan蜕琴，對每張表都只是掃描一次。
</html>
快速游標方式：
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;
快速游標方式：
但是這種方式提示wm_concat(distinct fpxm_type_id)連接函數(shù)中不能使用distinct關鍵字宵溅，如圖
于是修改查詢語句
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ù)太慢凌简，可以通過游標實現(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
--去除重復
(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í)行速度是相當可觀雏搂，但是如果同時去掉并行執(zhí)行速度更快3s不知道為啥。
如下圖：
案例
這今天在研究kettle工具寇损，是一款國外純java開發(fā)的開源ETL工具凸郑，抽取數(shù)據(jù)確實非常方便，大家有空可以去下載下來試試看矛市，方便之處在于它不用安裝芙沥，解壓完了就能直接用了（必須提前配置jdk和jre環(huán)境到系統(tǒng)環(huán)境中）。今天要說的不是這款軟件浊吏，問題是由使用這個軟件引起的憨愉，我在抽取數(shù)據(jù)后需要完成一個更新操作語句如下：
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萬條左右配紫，說起來也不是特別大，但是這個語句執(zhí)行起來特別的慢午阵，我等了半個多小時都沒執(zhí)行完躺孝，后來建索引稍微快一點，在網(wǎng)上找到一種更快捷的更新語句（因為我數(shù)據(jù)庫基礎不好很多語句不熟悉底桂，呵呵植袍！大神來看到了別笑話我就行！）如下：
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;
記言：如果你的才華不能匹配你的野心籽懦，請努力于个！
參考文獻：
一、
<html>
執(zhí)行報告分析：
explain plan FOR select 1 from dual;
select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
</html>
二暮顺、快速游標法
方式二
三厅篓、merge into 和單個update 執(zhí)行效率分析
