2表关联更新测试
第一种
UPDATE yz.T1 a
SET a.object_name =
(select b.object_name from yz.t2 b where a.object_id = b.object_id)
WHERE EXISTS (SELECT 1 FROM yz.t2 b WHERE b.object_id = a.object_id);
缺点：
1、执行时间长，耗费资源
2、表中object_id列必须唯一
SQL> UPDATE yz.T1 a
SET a.object_name =
(select b.object_name from yz.t2 b where a.object_id = b.object_id)
WHERE EXISTS (SELECT 1 FROM yz.t2 b WHERE b.object_id = a.object_id); 2 3 4
4977 rows updated.
Elapsed: 00:07:22.01
[qdtais1]@ht19c01[/home/oracle]$./ora sql 6fd93n7jb1jkg
DBA_HIST_SQLSTAT detail(recent 15 days):
SNAP_ID SNAP_DATE INST PHV CPU_S EXECS BUF_PE ROW_PE MS_PE DISK_R IO_S APP_MS
-------- -------------------- ---- ---------- ------- --------- ---------- ---------- --------- ---------- ------- ----------
1095 20220525 18:01 1 3641560594 420 1 432536652 4977 439046 432466359 205 0
SQL top event in gv_active_session_history:
INST_ID EVENT CNT
---------- -------------------------------------------------- ----------
1 94
1 direct path read 91
1 db file scattered read 5
Historical SQL plans in AWR:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6fd93n7jb1jkg
--------------------
UPDATE yz.T1 a SET a.object_name = (select b.object_name from
yz.t2 b where a.object_id = b.object_id) WHERE EXISTS (SELECT 1 FROM
yz.t2 b WHERE b.object_id = a.object_id)
Plan hash value: 3641560594
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 177M(100)| |
| 1 | UPDATE | T1 | | | | |
| 2 | HASH JOIN RIGHT SEMI| | 4977 | 184K| 54130 (1)| 00:00:03 |
| 3 | TABLE ACCESS FULL | T2 | 4977 | 19908 | 23748 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 9975 | 331K| 30382 (1)| 00:00:02 |
| 5 | TABLE ACCESS FULL | T2 | 1 | 23 | 23748 (1)| 00:00:01 |
------------------------------------------------------------------------------
Current SQL plans in Curor:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6fd93n7jb1jkg, child number 0
-------------------------------------
UPDATE yz.T1 a SET a.object_name = (select b.object_name from
yz.t2 b where a.object_id = b.object_id) WHERE EXISTS (SELECT 1 FROM
yz.t2 b WHERE b.object_id = a.object_id)
Plan hash value: 3641560594
-------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 177M(100)| |
| 1 | UPDATE | T1 | | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 4977 | 184K| 54130 (1)| 00:00:03 |
| 3 | TABLE ACCESS FULL | T2 | 4977 | 19908 | 23748 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 9975 | 331K| 30382 (1)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | T2 | 1 | 23 | 23748 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."OBJECT_ID"="A"."OBJECT_ID")
5 - filter("B"."OBJECT_ID"=:B1)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Historical Plans Summary(dba_hist_sqlstat):
RN PLAN_HASH_VALUE AVG_ETIME_S AVG_CPU_S AVG_BUFFERS AVG_READS AVG_ROWS EXECS FIRST_SNAP LAST_SNAP
---- --------------- ------------ ------------ ----------- ---------- ---------- --------- ------------------- -------------------
1 3641560594 439.046 420.428 432536652 432466359 4977 1 2022-05-25 18:01:46 2022-05-25 18:01:46
Current Plans Summary(gv_sql):
RN PLAN_HASH_VALUE AVG_ETIME_S AVG_CPU_S AVG_BUFFERS AVG_READS AVG_ROWS TOTAL_EXEC FIRST_LOAD_TIME LAST_ACTIVE
--- --------------- ------------ ------------ ----------- ---------- ---------- ---------- -------------------- --------------------
1 3641560594 193.348 178.332 183979908 183950468 0 1 2022-05-26/11:02:59 2022-05-26 11:06:12
Tables involved(used objects may not exists now):
TABLE_OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED SIZE_M
------------------------------ ------------------------------ ---------- -------------------- ----------
YZ T2 4977 2022-05-25 17:47:09 688
YZ T1 9975 2022-05-25 17:47:08 880
Above is sql info for sql_id :6fd93n7jb1jkg ,plan format is typical. for advanced plan ,use ora sql <sqlid> adv
清空shared pool 和 db cache
alter system flush buffer_cache; alter system flush shared_pool;
使用merge更新，merge 更新时表中的数据可以不时唯一的
merge into yz.t1 a
using (select t2.object_id,t2.object_name from yz.t2) b
on (a.object_id = b.object_id)
when matched then
update set a.object_name = b.object_name;
SQL> merge into yz.t1 a
using (select t2.object_id,t2.object_name from yz.t2) b
on (a.object_id = b.object_id)
when matched then
update set a.object_name = b.object_name; 2 3 4 5
4977 rows merged.
Elapsed: 00:00:00.52
[qdtais1]@ht19c01[/home/oracle]$./ora sql 337s73614yhbj
DBA_HIST_SQLSTAT detail(recent 15 days):
SNAP_ID SNAP_DATE INST PHV CPU_S EXECS BUF_PE ROW_PE MS_PE DISK_R IO_S APP_MS
-------- -------------------- ---- ---------- ------- --------- ---------- ---------- --------- ---------- ------- ----------
1096 20220526 11:12 1 2683531971 0 1 204686 4977 415 198825 0 0
SQL top event in gv_active_session_history:
Historical SQL plans in AWR:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 337s73614yhbj
--------------------
merge into yz.t1 a using (select t2.object_id,t2.object_name from
yz.t2) b on (a.object_id = b.object_id) when matched then update set
a.object_name = b.object_name
Plan hash value: 2683531971
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 54130 (100)| |
| 1 | MERGE | T1 | | | | |
| 2 | VIEW | | | | | |
| 3 | HASH JOIN | | 4977 | 816K| 54130 (1)| 00:00:03 |
| 4 | TABLE ACCESS FULL| T2 | 4977 | 111K| 23748 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T1 | 9975 | 1412K| 30383 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Current SQL plans in Curor:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 337s73614yhbj, child number 0
-------------------------------------
merge into yz.t1 a using (select t2.object_id,t2.object_name from
yz.t2) b on (a.object_id = b.object_id) when matched then update set
a.object_name = b.object_name
Plan hash value: 2683531971
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 54130 (100)| | | | |
| 1 | MERGE | T1 | | | | | | | |
| 2 | VIEW | | | | | | | | |
|* 3 | HASH JOIN | | 4977 | 816K| 54130 (1)| 00:00:03 | 1355K| 1355K| 1662K (0)|
| 4 | TABLE ACCESS FULL| T2 | 4977 | 111K| 23748 (1)| 00:00:01 | | | |
| 5 | TABLE ACCESS FULL| T1 | 9975 | 1412K| 30383 (1)| 00:00:02 | | | |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Historical Plans Summary(dba_hist_sqlstat):
RN PLAN_HASH_VALUE AVG_ETIME_S AVG_CPU_S AVG_BUFFERS AVG_READS AVG_ROWS EXECS FIRST_SNAP LAST_SNAP
---- --------------- ------------ ------------ ----------- ---------- ---------- --------- ------------------- -------------------
1 2683531971 0.415 0.410 204686 198825 4977 1 2022-05-26 11:12:11 2022-05-26 11:12:11
Current Plans Summary(gv_sql):
RN PLAN_HASH_VALUE AVG_ETIME_S AVG_CPU_S AVG_BUFFERS AVG_READS AVG_ROWS TOTAL_EXEC FIRST_LOAD_TIME LAST_ACTIVE
--- --------------- ------------ ------------ ----------- ---------- ---------- ---------- -------------------- --------------------
1 2683531971 0.521 0.470 205276 199000 4977 1 2022-05-26/11:15:26 2022-05-26 11:15:27
Tables involved(used objects may not exists now):
TABLE_OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED SIZE_M
------------------------------ ------------------------------ ---------- -------------------- ----------
YZ T2 4977 2022-05-25 17:47:09 688
YZ T1 9975 2022-05-25 17:47:08 880
Above is sql info for sql_id :337s73614yhbj ,plan format is typical. for advanced plan ,use ora sql <sqlid> adv
通过上述数据分析，可知使用merge更新比传统的update执行事件快1000倍
