背景：

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

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

第一種情況：全刪全插

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

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

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

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

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

drop命令

drop table 表名；

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

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

truncate命令

truncate  table  表名；

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

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

delete命令

delete from 表名；

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

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

Truncate總結(jié)

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

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

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

3、truncate將重新設(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萬條左右、表2數(shù)據(jù)量5萬左右，說起來也不是特別大，但是這個語句執(zhí)行起來特別的慢，我等了1個小時都沒執(zhí)行完，后來取消掉了更新。

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

方法二：merge into

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

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

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

總結(jié)

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

最新評論