--创建更新目的表:
DROP TABLE test_xie;
CREATE TABLE test_xie(
member_id varchar2(64),
score_1 NUMBER,
score_2 NUMBER);
--生成测试数据 100W
DECLARE
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO test_xie (member_id, score_1, score_2)
VALUES ('m'||i, i, NULL);
IF i MOD 10000 = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
--数据源表
DROP TABLE test_xie_2;
CREATE TABLE test_xie_2(member_id varchar2(64),score_1 NUMBER, score_2 NUMBER);
--源数据100W
INSERT INTO test_xie_2
(member_id, score_1, score_2)
SELECT member_id, NULL, 1 + score_1 FROM test_xie;
COMMIT;
15:11:07 SQL> set linesize 1000;
15:11:28 SQL> set timing on;
15:11:29 SQL> SET TIME ON;
15:11:29 SQL> set autotrace traceonly;
15:11:29 SQL> UPDATE test_xie a
15:11:29 2 SET score_2 = (SELECT score_2 FROM test_xie_2 b WHERE a.member_id = b.member_id)
15:11:29 3 WHERE EXISTS (SELECT 1 FROM test_xie_2 b WHERE a.member_id = b.member_id);
1000000 rows updated.
Elapsed: 00:00:29.66
Execution Plan
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 986K| 76M| 9347 (1)|
| 1 | UPDATE | TEST_XIE | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 986K| 76M| 9347 (1)|
| 3 | INDEX FAST FULL SCAN | U_IND_TEST_XIE_2 | 1048K| 33M| 699 (2)|
| 4 | TABLE ACCESS FULL | TEST_XIE | 986K| 44M| 1204 (1)|
| 5 | TABLE ACCESS BY INDEX ROWID| TEST_XIE_2 | 1 | 47 | 3 (0)|
|* 6 | INDEX UNIQUE SCAN | U_IND_TEST_XIE_2 | 1 | | 2 (0)|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."MEMBER_ID"="B"."MEMBER_ID")
6 - access("B"."MEMBER_ID"=:B1)
Statistics
----------------------------------------------------------
169 recursive calls
1777989 db block gets
2261811 consistent gets
479808 physical reads
256558164 redo size
844 bytes sent via SQL*Net to client
925 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000000 rows processed
15:12:00 SQL>
--update 方法2
set timing on;
set autotrace traceonly;
UPDATE (SELECT a.member_id, a.score_2 AS score_2_a, b.score_2 AS score_2_b
FROM test_xie a, test_xie_2 b
WHERE a.member_id = b.member_id)
SET score_2_a = score_2_b;
--执行计划
1000000 rows updated.
Elapsed: 00:00:12.64
Execution Plan
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 986K| 88M| 10326 (1)| 00:02:04 |
| 1 | UPDATE | TEST_XIE | | | | |
|* 2 | HASH JOIN | | 986K| 88M| 10326 (1)| 00:02:04 |
| 3 | TABLE ACCESS FULL| TEST_XIE | 986K| 44M| 1204 (1)| 00:00:15 |
| 4 | TABLE ACCESS FULL| TEST_XIE_2 | 1048K| 46M| 726 (2)| 00:00:09 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."MEMBER_ID"="B"."MEMBER_ID")
Statistics
----------------------------------------------------------
1032 recursive calls
1778004 db block gets
7455 consistent gets
26932 physical reads
256557256 redo size
845 bytes sent via SQL*Net to client
932 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
13 sorts (memory)
2 sorts (disk)
1000000 rows processed
15:22:33 SQL> 15:22:33 SQL>
--方法3 :使用merge
MERGE INTO test_xie a
USING (SELECT member_id, score_2 AS score_2_b FROM test_xie_2) b
ON (a.member_id = b.member_id)
WHEN MATCHED THEN
UPDATE SET score_2 = score_2_b;
15:22:33 SQL> 15:22:33 SQL> MERGE INTO test_xie a
15:25:32 2 USING (SELECT member_id, score_2 AS score_2_b FROM test_xie_2) b
15:25:32 3 ON (a.member_id = b.member_id)
15:25:32 4 WHEN MATCHED THEN
15:25:32 5 UPDATE SET score_2 = score_2_b;
1000000 rows merged.
Elapsed: 00:00:13.69
Execution Plan
----------------------------------------------------------
Plan hash value: 3736152574
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 986K| 24M| 12046 (1)| 00:02:25 |
| 1 | MERGE | TEST_XIE | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN | | 986K| 111M| 12046 (1)| 00:02:25 |
| 4 | TABLE ACCESS FULL| TEST_XIE_2 | 1048K| 46M| 726 (2)| 00:00:09 |
| 5 | TABLE ACCESS FULL| TEST_XIE | 986K| 67M| 1204 (1)| 00:00:15 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."MEMBER_ID"="MEMBER_ID")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
230 recursive calls
1778018 db block gets
7370 consistent gets
31342 physical reads
256557052 redo size
845 bytes sent via SQL*Net to client
927 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
2 sorts (disk)
1000000 rows processed
15:25:46 SQL>
总结:
1. 使用方法2,即内嵌视图的方法,大大的提高了效率;
2. 更直观更简捷的方法是使用merge
注意:
在使用第二种方法的时候,需要在源表test_xie_2的列member_id上有唯一约束或索引才可以,否则报错:
ORA-01779: cannot modify a column which maps to a non key-preserved table