SQL> set echo on SQL> set time on 17:56:09 SQL> set timing on 17:56:12 SQL> set autotrace traceonly 17:56:19 SQL> @aaa.sql 17:56:21 SQL> update ( 17:56:21 2 select /*+use_hash(a,b)*/a.bill_bal bill_bal_a ,b.bill_bal bill_bal_b 17:56:21 3 from tjw_target_user a,tmp_tjw b 17:56:21 4 where a.bill_id = b.bill_id 17:56:21 5 ) set bill_bal_a = bill_bal_b; 1335068 rows updated. Elapsed: 00:02:52.74 Execution Plan ---------------------------------------------------------- 0 UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=681 Card=82 Bytes= 2952) 1 0 UPDATE OF 'TJW_TARGET_USER' 2 1 HASH JOIN (Cost=681 Card=82 Bytes=2952) 3 2 TABLE ACCESS (FULL) OF 'TMP_TJW' (Cost=2 Card=82 Bytes =1804) 4 2 TABLE ACCESS (FULL) OF 'TJW_TARGET_USER' (Cost=674 Car d=1540412 Bytes=21565768) Statistics ---------------------------------------------------------- 610 recursive calls 1365395 db block gets 47221 consistent gets 18052 physical reads 318042748 redo size 495 bytes sent via SQL*Net to client 671 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1335068 rows processed 17:59:14 SQL> 17:59:14 SQL> commit; Commit complete. Elapsed: 00:00:00.04 17:59:14 SQL> 17:59:14 SQL> update tjw_target_user a set bill_bal = ( 17:59:14 2 select bill_bal from tmp_tjw b where a.bill_id = b.bill_id 17:59:14 3 ) 17:59:14 4 where exists ( 17:59:14 5 select 1 from tmp_tjw c where a.bill_id = c.bill_id 17:59:14 6 ); 1335068 rows updated. Elapsed: 00:07:08.56 Execution Plan ---------------------------------------------------------- 0 UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=169 Card=82 Bytes= 1886) 1 0 UPDATE OF 'TJW_TARGET_USER' 2 1 NESTED LOOPS (Cost=169 Card=82 Bytes=1886) 3 2 SORT (UNIQUE) 4 3 INDEX (FAST FULL SCAN) OF 'PK_TMP_TJW_IDX' (UNIQUE) (Cost=2 Card=82 Bytes=738) 5 2 INDEX (RANGE SCAN) OF 'PK_TJW_TARGET_USER_IDX' (UNIQUE ) (Cost=2 Card=1 Bytes=14) 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'TMP_TJW' (Cost=1 Card= 1 Bytes=22) 7 6 INDEX (RANGE SCAN) OF 'PK_TMP_TJW_IDX' (UNIQUE) (Cost= 1 Card=1) Statistics ---------------------------------------------------------- 140 recursive calls 1364964 db block gets 6713317 consistent gets 19867 physical reads 317737072 redo size 506 bytes sent via SQL*Net to client 672 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 1 sorts (disk) 1335068 rows processed 注意 :
需要在a.bill_id, b.bill_id列上有唯一约束或索引才可以,否则报错: 实际例子: update cm_cb_ss_circuitendpoint_bak c set c.sys_int_id=(select max(a.sys_int_id) from (select * from cm_cb_ss_circuitendpoint_bak where UPPER(data_status)!='NEW' and data_status is not null) a where a.dn=c.dn) where c.dn in (select dn from cm_cb_ss_circuitendpoint_bak where UPPER(data_status)='NEW' or data_status is null) 这样就可以了。
一种高效率的update多表关联更新测试
最新推荐文章于 2022-08-14 15:34:10 发布