Oracle多表關聯更新的方式選擇, Loop or Hash update?

Oracle多表關聯更新的方式選擇。

環境：Oracle 10.2.0.3 on RHEL4 X86_64

SQL> desc test_a;

Name Null? Type

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

C1 NUMBER

C2 VARCHAR2(32)

測試表2

SQL> desc test_b;

Name Null? Type

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

C1 NOT NULL NUMBER

C2 VARCHAR2(32)

[@more@]

Test_a資料來源 dba_objects的object_id,object_name

Test_a資料來源 dba_objects的object_id,object_name 重複2次(第二次object_id+30000)

SQL> select count(*) from test_a;

COUNT(*)

----------

72787

SQL> select count(*) from test_b;

COUNT(*)

----------

145574

為保證更新資料來源唯一，test_b的c1列建立一個PK(or unique index)

SQL> alter table test_b

2 add constraint PK_test_b primary key (c1) using index tablespace users;

寫法有2種：

1．Loop UPDATE

update test_a a set a.C2=(select b.c2 from test_b b where b.c1=a.c1) where a.c1<10000；

2.join Update

update (select a.c2 ac2,b.c2 bc2 from test_a a,test_b b where a.c1=b.c1 and a.c1<10000)

set ac2=bc2；

比較下2種的適用性：

Execution Plan:

1.

SQL> update test_a a set a.C2=(select b.c2 from test_b b where b.c1=a.c1) where a.c1<10000;

9613 rows updated.

Elapsed: 00:00:00.19

Execution Plan

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

Plan hash value: 1516591834

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

----------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

Time |

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

----------

| 0 | UPDATE STATEMENT | | 3190 | 86130 | 139 (2)|

00:00:01 |

| 1 | UPDATE | TEST_A | | | |

|

|* 2 | TABLE ACCESS FULL | TEST_A | 3190 | 86130 | 139 (2)|

00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| TEST_B | 1 | 29 | 2 (0)|

00:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_TEST_B | 1 | | 1 (0)|

00:00:01 |

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

----------

Predicate Information (identified by operation id):

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

2 - filter("A"."C1"<10000)

4 - access("B"."C1"=:B1)

Statistics

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

1 recursive calls

9910 db block gets

19602 consistent gets

0 physical reads

2768436 redo size

832 bytes sent via SQL*Net to client

781 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

9613 rows processed

2.

SQL> update (select a.c2 ac2,b.c2 bc2 from test_a a,test_b b where a.c1=b.c1 and a.c1<10000)

2 set ac2=bc2;

9613 rows updated.

Elapsed: 00:00:00.14

Execution Plan

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

Plan hash value: 3240061431

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

-----------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

Time |

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

-----------

| 0 | UPDATE STATEMENT | | 3190 | 174K| 267 (2)|

00:00:02 |

| 1 | UPDATE | TEST_A | | | |

|

|* 2 | HASH JOIN | | 3190 | 174K| 267 (2)|

00:00:02 |

|* 3 | TABLE ACCESS FULL | TEST_A | 3190 | 86130 | 139 (2)|

00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| TEST_B | 9580 | 271K| 127 (0)|

00:00:01 |

|* 5 | INDEX RANGE SCAN | PK_TEST_B | 9580 | | 21 (0)|

00:00:01 |

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

-----------

Predicate Information (identified by operation id):

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

2 - access("A"."C1"="B"."C1")

3 - filter("A"."C1"<10000)

5 - access("B"."C1"<10000)

Statistics

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

33 recursive calls

9922 db block gets

444 consistent gets

0 physical reads

2768864 redo size

834 bytes sent via SQL*Net to client

795 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

9613 rows processed

儘管 Hash update Cost > Loop Update Cost, 但就實際執行時間和block gets來看，卻佔有優勢。

又經過a.c1<10, a.c1<100, a.c1<1000, a.c1<20000,a.c1<30000 等測試，

Loop update的block gets隨a.c1的範圍增大而增大

Hash update的block gets隨a.c1的範圍增大變動不大

基本符合各自執行原理的特點。

在這個例子中，a.c1<10這個級別的update, hash update的block gets還是稍稍佔優。

BTW,把optimizer_features_enable改為9.2.0後, 2種方法的block gets都增加了，10G對多表關聯的最佳化確實比9I要好一些。

一個Hint紀錄一下：針對沒有unique constraint的更新資料來源：

+BYPASS_UJVC

跳過Oracle的檢查，但若更新資料來源不唯一，會造成某些行被多次更新導致難以預知的結果。

接下來為TEST_A的C1建立Index，

類似第一次的測試結果表明,Loop update/Hash update 都降低了數百consistent gets,百分比而言，是hash update佔有優勢。

從這系列結果看，大規模關聯table UPDATE， Hash update在I/O上佔有明顯優勢。

而小規模關聯table UPDATE， 2種方式在絕對I/O上的差異並不很大。

相對的，Hash update對秒間Redo log和UNDO的壓力也更大些。