Oracle多表关联更新的方式选择

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)




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的压力也更大些。