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倍