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的压力也更大些。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10856805/viewspace-1022392/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10856805/viewspace-1022392/