前言

本文旨在提供完善的總體思路，避免遺漏或出現(xiàn)嚴(yán)重問(wèn)題，具體Oracle命令太過(guò)繁瑣不在此陳列。

一、執(zhí)行范圍

首先我們要確定更新哪些表，每張表有多少待更新的數(shù)據(jù)量，每張表更新哪個(gè)字段，每個(gè)字段的類型是什么，字段最大長(zhǎng)度，字段是否有索引如下所示：

二、索引

由于表數(shù)據(jù)量比較大，select和update語(yǔ)句的where條件要使用主鍵或者有索引的列，有效避免全表掃描，減少鎖表時(shí)間。

三、舊數(shù)據(jù)/臟數(shù)據(jù)清理

先檢查一下表里和業(yè)務(wù)代碼，是否可以清理舊數(shù)據(jù)/臟數(shù)據(jù)，如果可以，說(shuō)不定本來(lái)百萬(wàn)級(jí)的表可以瘦身到一兩萬(wàn)的數(shù)據(jù)量，大大減輕批量更新的壓力。
再檢查一下臟數(shù)據(jù)會(huì)不會(huì)對(duì)你的更新邏輯產(chǎn)生影響。

四、更新時(shí)長(zhǎng)估算

可以在本地或測(cè)試環(huán)境造模擬數(shù)據(jù)，模擬生產(chǎn)環(huán)境真實(shí)的更新數(shù)據(jù)量，以及每條更新的數(shù)據(jù)長(zhǎng)度大小，估算出一個(gè)語(yǔ)句更新時(shí)長(zhǎng)，評(píng)估時(shí)長(zhǎng)是否可以接受；
若全部數(shù)據(jù)更新時(shí)間太長(zhǎng)不能接受，可以考慮是否可以先根據(jù)時(shí)間更新近期數(shù)據(jù)，或本次只更新部分表數(shù)據(jù)，更新完成后，后續(xù)再持續(xù)更新。

五、更新方式

1.分批次更新

如果單條數(shù)據(jù)占空間比較大，那么單批次量就要小一些，像我上面列舉的CLOB類型的大字段，每批次100~500條合理（根據(jù)服務(wù)器配置決定）；如果單條空間小，可以適當(dāng)增加每批次大小。

2.分批次事務(wù)提交

不要一條一條提交事務(wù)，也不要幾百萬(wàn)才提交一次事務(wù)，這里建議每次提交事務(wù)的量和上面批次的量一樣既可。

3.分頁(yè)查詢

如果你是在Java中進(jìn)行更新，記得使用分頁(yè)查詢，不要把幾百萬(wàn)條數(shù)據(jù)一下查詢到內(nèi)存里，最好手寫分頁(yè)，框架的分頁(yè)容易出bug。

4.暫停更新功能

比如要更新300w條數(shù)據(jù)，可能會(huì)出現(xiàn)更新到150w條數(shù)據(jù)時(shí)發(fā)現(xiàn)之前更新失敗的數(shù)據(jù)，需要暫停當(dāng)前更新，處理好失敗數(shù)據(jù)之后再繼續(xù)更新，需要做更新暫停功能，這樣就不用等待全部數(shù)據(jù)跑完才能處理異常數(shù)據(jù)了，從程序設(shè)計(jì)而言是一個(gè)非常好的靈活性設(shè)計(jì)。

六、更新失敗處理

1.日志記錄

要有完善的日志記錄，例如總數(shù)據(jù)量、每批更新數(shù)量、每批更新成功數(shù)量、每批更新失敗數(shù)量、更新失敗的數(shù)據(jù)ID、失敗原因

2.事務(wù)回滾方式

要根據(jù)自身業(yè)務(wù)場(chǎng)景，考慮好是一條失敗不影響繼續(xù)執(zhí)行，還是一條失敗整批失敗，還是一條失敗整表失敗等等

七、風(fēng)險(xiǎn)排除

1.表空間風(fēng)險(xiǎn)

如果你是更新clob字段，并且現(xiàn)有表空間剩余不足的情況下，就要謹(jǐn)慎一些，因?yàn)閏lob字段在update的時(shí)候需要將新數(shù)據(jù)和舊數(shù)據(jù)同時(shí)存儲(chǔ)（碎片化），但是并不是說(shuō)比如舊數(shù)據(jù)有1G，那就需要2G，Oracle有自動(dòng)回收機(jī)制，但是如果你的clob字段是BASICFILE，就需要處理一部分就手動(dòng)執(zhí)行SHRINK SPACE命令來(lái)整理碎片，釋放空間。如果clob字段是SECUREFILE，Oracle的自動(dòng)回收更積極，但是仍有風(fēng)險(xiǎn)，保險(xiǎn)起見還是要對(duì)表空間進(jìn)行擴(kuò)容。

2.索引風(fēng)險(xiǎn)

如果你的update語(yǔ)句的where條件沒(méi)有索引，可能會(huì)導(dǎo)致update執(zhí)行過(guò)慢，這個(gè)過(guò)程是鎖表的，如果你的業(yè)務(wù)不依賴這張表，那沒(méi)事，如果依賴，可能會(huì)導(dǎo)致業(yè)務(wù)停滯。

3.內(nèi)存風(fēng)險(xiǎn)

如果你是在java程序中去批量執(zhí)行update語(yǔ)句，要注意大字段在Java中的處理，小心OOM內(nèi)存溢出。

4.日期風(fēng)險(xiǎn)

如果你是通過(guò)日期去分批更新，注意不同表的同一日期范圍的數(shù)據(jù)量是不同的，比如我 日期范圍是近一個(gè)月，A表可能只有3000條數(shù)據(jù)，B表會(huì)有50萬(wàn)條數(shù)據(jù)，這種情況要考慮到。

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

最好用expdb數(shù)據(jù)泵方式導(dǎo)出，如果不行就使用exp命令，如果exp命令也使用不了，就使用下面的sql庫(kù)內(nèi)備份，但是庫(kù)內(nèi)備份要注意備份完之后的表空間容量是否不足。

CREATE TABLE T_ORDER_20260401_BAK AS SELECT * FROM T_ORDER;

九、數(shù)據(jù)更新時(shí)機(jī)

大批量的數(shù)據(jù)更新需要避開業(yè)務(wù)高峰期，在系統(tǒng)使用率低，數(shù)據(jù)庫(kù)流量小的時(shí)候進(jìn)行。

十、數(shù)據(jù)恢復(fù)

要提前寫好數(shù)據(jù)恢復(fù)的腳本/命令，不能等出了問(wèn)題想恢復(fù)的時(shí)候現(xiàn)寫，盡量減少數(shù)據(jù)變化產(chǎn)生的差異，下面列一條庫(kù)內(nèi)備份表的恢復(fù)命令（比普通update快非常多）。

MERGE INTO T_ORDER b
USING T_ORDER_BAK a
ON (b.id = a.id)
WHEN MATCHED THEN
UPDATE SET b.待恢復(fù)字段 = a.待恢復(fù)字段;

十一、數(shù)據(jù)驗(yàn)證

1、數(shù)據(jù)驗(yàn)證的時(shí)機(jī)要包括執(zhí)行前驗(yàn)證、執(zhí)行中驗(yàn)證、執(zhí)行后驗(yàn)證；
2、需要提前寫好數(shù)據(jù)驗(yàn)證的SQL，最好自動(dòng)化高一點(diǎn)，不要一條一條執(zhí)行然后在肉眼比對(duì)數(shù)據(jù)的那種SQL，等更新執(zhí)行完成之后，達(dá)到一鍵驗(yàn)證的效果；
3、數(shù)據(jù)驗(yàn)證的SQL盡量考慮全面的一些，針對(duì)不同的業(yè)務(wù)場(chǎng)景進(jìn)行驗(yàn)證。

十二、慶祝一下

如果你按照本文的方案完美完成了重要數(shù)據(jù)更新，那可以長(zhǎng)舒一口氣，然后夸夸自己了！

以上就是Oracle大批量數(shù)據(jù)更新的避坑指南與關(guān)鍵原則的詳細(xì)內(nèi)容，更多關(guān)于Oracle大批量數(shù)據(jù)更新的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論