一种高效率的update多表关联更新测试

时间:2022-05-26 13:27:40
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) 这样就可以了。