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)
这样就可以了。
