背景：

根據(jù)甲方要求，需要對(duì)大數(shù)據(jù)平臺(tái)指定表(hive、impala表)的歷史數(shù)據(jù)[2021-01-01至2023-03-29]指定字段進(jìn)行批量更新，然后把表同步到Oracle。先更新大數(shù)據(jù)平臺(tái)上的表，再把更新完成的表同步到Oracle。hive有8張表更新，其中4張大表【分區(qū)表】（數(shù)據(jù)量分別為：1038738976、260958144、25860509、2867005），另外4張小表(幾萬(wàn)、二十幾萬(wàn)的樣子)。4張小表使用kettle直接全量同步到Oracle，另外4張大表數(shù)據(jù)量很大，使用kettle同步的話，時(shí)間也會(huì)很久而且不一定能成功，所以我決定在Oracle上直接更新。查看Oracle更新（牽涉7張表，其中4張表數(shù)據(jù)量少，幾萬(wàn)二十幾萬(wàn)的樣子；3張數(shù)據(jù)量大），3張生產(chǎn)環(huán)境表（表1：136327470；表2：32311563；表3：2757935）高達(dá)億級(jí)的數(shù)據(jù)量，需要更新的數(shù)據(jù)也有上億、千萬(wàn)、百萬(wàn)，還需要連表查詢。

開始使用update 語(yǔ)句直接更新的時(shí)候發(fā)現(xiàn)，50分鐘都未能更新完成，使用了merge 后，速度有很大提升。

第一種情況：全刪全插

1、備份數(shù)據(jù)

先備份表數(shù)據(jù)，以免刪錯(cuò)數(shù)據(jù)：

create table 表名_bak_日期 as select * from 表名;

2、刪除數(shù)據(jù)

三種方法：刪除表（記錄和結(jié)構(gòu)）的語(yǔ)句delete、truncate、drop

drop命令

drop table 表名；

例如：刪除學(xué)生表(student)
drop table student；

注意：用drop刪除表數(shù)據(jù)，不但會(huì)刪除表中的數(shù)據(jù)，連結(jié)構(gòu)也會(huì)被刪除?。?！

truncate命令

truncate  table  表名；

例如：刪除學(xué)生表（student）
truncate table student；

注意：
        1、用truncate刪除表數(shù)據(jù)，只是刪除表中的數(shù)據(jù)，表結(jié)構(gòu)不會(huì)被刪除！
        2、刪除整個(gè)表的數(shù)據(jù)時(shí)，過程是系統(tǒng)一次性刪除數(shù)據(jù)，效率比較高
        3、truncate刪除釋放空間

delete命令

delete from 表名；

例如：刪除學(xué)生表（student）
delete from student；

注意：       
        1、用delete刪除表數(shù)據(jù)，只是刪除表中的數(shù)據(jù)，表結(jié)構(gòu)不會(huì)被刪除！
        2、雖然也是刪除整個(gè)表的數(shù)據(jù)，但是過程是系統(tǒng)一行一行的刪，效率比truncate低
        3、delete刪除是不釋放空間的

Truncate總結(jié)

• truncate table在功能上與不帶where子句的delete語(yǔ)句相同：二者均刪除表中的全部行。
• 但truncate比delete速度快，且使用的系統(tǒng)和事務(wù)日志資源少。
• delete語(yǔ)句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。所以可以對(duì)delete操作進(jìn)行rollback。

1、truncate在各種表上無論是大的還是小的都非?？臁Ｈ绻衦ollback命令delte將被撤銷，而truncate則不會(huì)被撤銷。

2、truncate是一個(gè)DDL語(yǔ)言，向其他所有的DDL語(yǔ)言一樣，他將被隱式提交，不能對(duì)truncate使用rollback命令。

3、truncate將重新設(shè)置高水平線和所有的索引。在對(duì)整個(gè)表和索引進(jìn)行完全瀏覽時(shí)，經(jīng)過truncate操作后的表比delete操作后的表要快得多。

4、truncate不能觸發(fā)任何delete觸發(fā)器。

5、當(dāng)表被清空后表和表的索引將重新設(shè)置成初始大小，而delete則不能。

6、不能清空父表

3、插入數(shù)據(jù)

insert into 表名A select * from 表名B;

第二種情況：不刪除數(shù)據(jù)直接更新表

方法一：update

update 表1 b set b.PROJECTBELONG = (select distinct a.PROJECTBELONG from 表2 a 
where b.ROOT_ITEM_CODE = a.DESC1 
) 
where b.ROOT_ITEM_CODE in (select distinct a.DESC1 from 表2 a 
);

 上面的表1數(shù)據(jù)量275萬(wàn)條左右、表2數(shù)據(jù)量5萬(wàn)左右，說起來也不是特別大，但是這個(gè)語(yǔ)句執(zhí)行起來特別的慢，我等了1個(gè)小時(shí)都沒執(zhí)行完，后來取消掉了更新。

建議：建索引稍微快一點(diǎn)。但我覺得不如merge into高效。

方法二：merge into

merge into 表1 t
using (select DESC1,PROJECTBELONG from 表2) s
on (t.ROOT_ITEM_CODE = s.DESC1 
--如果表數(shù)據(jù)量大，可以按照某個(gè)特定字段更新，我這里是按月更新
AND t.DATE_MONTH = '2022-04'
)
when matched then
update set t.PROJECTBELONG = s.PROJECTBELONG;

4百萬(wàn)的數(shù)據(jù)更新用時(shí)：1m31s

補(bǔ)充：when matched then  還支持insert ，可以通過此sql 實(shí)現(xiàn)“存在即更新，不存在則插入”批量操作，可以大大減少數(shù)據(jù)庫(kù)鏈接操作。

總結(jié)

到此這篇關(guān)于Oracle關(guān)聯(lián)表更新操作的文章就介紹到這了,更多相關(guān)Oracle關(guān)聯(lián)表更新內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論