關于 ROWID 的介紹參考我的 Blog ：

Oracle Rowid 介紹

http://blog.csdn.net/tianlesoftware/archive/2009/12/16/5020718.aspx

關于大表 Update 的一個討論，參考 itpub ：

http://www.itpub.net/viewthread.php?tid=1052077

一 . 在虛擬機上 使用 rowid 進行 update 測試

使用 rowid 進行 update 能提高速度，是因為通過 rowid 能夠迅速的進行定位，不用全表進行掃描。

-- 查看表 dave 記錄數

SYS@dave2(db2)> select count(*) from dave;

COUNT(*)

----------

3080115 --300 萬數據

-- 創建測試表 dba

SYS@dave2(db2)> create table dba as select * from dave;

Table created.

-- 用 dave 表去更新 DBA 表

SYS@dave2(db2)> update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id);

3080115 rows updated.

Elapsed: 00:16:12.81 -- 整個更新花了 16 分鐘

--update 期間查看 session 執行時間：

SQL>select sid, target , time_remaining , elapsed_seconds , message , sql_id from v$session_longops where sid= 138 ;

select * from v$lock where sid= 138 ;

select * from v$session_wait where sid= 138 ;

-- 使用 rowid 進行更新

DECLARE

CURSOR cur IS

SELECT

a. area_code , b . ROWID ROW_ID

FROM dave a, dba b

WHERE a.id = b .id

ORDER BY b . ROWID ; --- 如果表的數據量不是很大 , 可以不用 order by rowid

V_COUNTER NUMBER ;

BEGIN

V_COUNTER := 0 ;

FOR row IN cur LOOP

UPDATE dba

SET prov_code = row. area_code

WHERE ROWID = row. ROW_ID ;

V_COUNTER := V_COUNTER + 1 ;

IF ( V_COUNTER >= 1000 ) THEN

COMMIT;

V_COUNTER := 0 ;

END IF;

END LOOP;

COMMIT;

END;

PL/SQL procedure successfully completed.

Elapsed: 00:14:54.07 -- 執行花了 14 分鐘，速度提高不是很多。

在這個更新中， 使用了 ORDER BY b.ROWID 進行了排序 ，每個數據塊里面都有多條記錄，這樣按 rowid 進行排序，那么這樣每次訪問數據塊的時候就會相同，就會減小 block 在調用的次數，從而提高效率。

因為我這是虛擬機上的測試環境，所以內存分配的并不合適， I/O 也不行。

-- 我們把 order by 去掉，在更新看看

DECLARE

CURSOR cur IS

SELECT

a. area_code , b . ROWID ROW_ID

FROM dave a, dba b

WHERE a.id = b .id;

V_COUNTER NUMBER ;

BEGIN

V_COUNTER := 0 ;

FOR row IN cur LOOP

UPDATE dba

SET prov_code = row. area_code

WHERE ROWID = row. ROW_ID ;

V_COUNTER := V_COUNTER + 1 ;

IF ( V_COUNTER >= 1000 ) THEN

COMMIT;

V_COUNTER := 0 ;

END IF;

END LOOP;

COMMIT;

END;

PL/SQL procedure successfully completed.

Elapsed: 00:20:24.43

-- 居然用了 21 分鐘，看來對大表還是很有必要進行 order by rowid 的。

二 . 在測試服務器上測試

折騰了半天沒有折騰出效果來。將數據 dump 出來，在 imp 到測試服務器， 300w 的數據， dump 文件有 300M 。

-- 在測試服務器上直接 update

SQL> update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id);

update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id)

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

Elapsed: 00:20:45.04

一直的處理中 . 被迫取消。 查看了一下 session 的狀態：

SQL>select sid, target , time_remaining , elapsed_seconds , message , sql_id from v$session_longops where sid= 197 ;

等了 20 分鐘，才 8 個 blocks ，要處理到 39521 個 blocks ，不知道要到那個猴年馬月了。 居然比我虛擬機上測試的還慢。

-- 在測試服務器上使用 rowid + order by

DECLARE

CURSOR cur IS

SELECT

a. area_code , b . ROWID ROW_ID

FROM dave a, dba b

WHERE a.id = b .id

ORDER BY b . ROWID ; --- 如果表的數據量不是很大 , 可以不用 order by rowid

V_COUNTER NUMBER ;

BEGIN

V_COUNTER := 0 ;

FOR row IN cur LOOP

UPDATE dba

SET prov_code = row. area_code

WHERE ROWID = row. ROW_ID ;

V_COUNTER := V_COUNTER + 1 ;

IF ( V_COUNTER >= 1000 ) THEN

COMMIT;

V_COUNTER := 0 ;

END IF;

END LOOP;

COMMIT;

END;

PL/SQL procedure successfully completed.

Elapsed: 00:04:45.98

-- 總算看到效果了， 4 分多鐘搞定，如果在生產庫上，這個操作應該還會快一點。

-- 在測試服務器上使用 rowid

DECLARE

CURSOR cur IS

SELECT

a. area_code , b . ROWID ROW_ID

FROM dave a, dba b

WHERE a.id = b .id;

V_COUNTER NUMBER ;

BEGIN

V_COUNTER := 0 ;

FOR row IN cur LOOP

UPDATE dba

SET prov_code = row. area_code

WHERE ROWID = row. ROW_ID ;

V_COUNTER := V_COUNTER + 1 ;

IF ( V_COUNTER >= 1000 ) THEN

COMMIT;

V_COUNTER := 0 ;

END IF;

END LOOP;

COMMIT;

END;

PL/SQL procedure successfully completed.

Elapsed: 00:09:06.73 -- 花了 9 分鐘

通過以上測試，驗證了對于大表的 update ，除了使用 rowid ，還需要根據 rowid 排序一下。

-------------------------------------------------------------------------------------------------------

Blog ： http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群： 62697716( 滿 ); DBA2 群： 62697977( 滿 ) DBA3 群： 62697850( 滿 )

DBA 超級群： 63306533( 滿 ); DBA4 群： 83829929 DBA5 群： 142216823

DBA6 群： 158654907 聊天 群： 40132017 聊天 2 群： 69087192

-- 加群需要在備注說明 Oracle 表空間和數據文件的關系，否則拒絕申請

Oracle 利用 rowid 提升 update 性能