1) Yes, you never want to make an (estimated) 12M times an index access on a large table.
The index blocks will be largely on the disc, so you may expect about access to 100 rows per seconds.
Even if you get 1000 index aceess per second due to caching,
you still have to wait hours for processing 12M rows.
So the execution plan 1 is NO GO for a large table.
2) The execution plan of the MERGE
(hash join with two full table scans) looks fine and should work.
3) Yes you can use UPDATE
statement without problems in your setup. You will use the feature of the updatable join view
Query
update (
select SRC_A, SRC_B,DEST_B, DEST_C
from MY_DEST d
left outer join MY_SRC s
on d.DEST_KEY = s.SRC_KEY and d.DEST_DATE = s.SRC_DATE)
set DEST_B = SRC_A + SRC_B,
DEST_C = SRC_B
;
Execution plan
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1000K| 57M| | 6682 (1)| 00:00:01 |
| 1 | UPDATE | MY_DEST | | | | | |
|* 2 | HASH JOIN OUTER | | 1000K| 57M| 40M| 6682 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| MY_DEST | 1000K| 28M| | 1341 (2)| 00:00:01 |
| 4 | TABLE ACCESS FULL| MY_SRC | 1000K| 28M| | 1341 (2)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEST_DATE"="S"."SRC_DATE"(+) AND
"D"."DEST_KEY"="S"."SRC_KEY"(+))
You see the similar execution plan is created as for the MERGE
, so you will excpect also similar performance.
Final Notes
You may additionally use parallel hint to speed up.
Don't forget you must enable parallel dml in your session.
ALTER SESSION ENABLE PARALLEL DML;
Your MERGE
statement is not equal to your first UPDATE
statement.
The difference appears when a primary key exists in the destination table but not in the source table.
The UPDATE
resets the destination columns to NULL
while the MERGE
let them unchanged.
My UPDATE
statement use an outer join so it behaves as your UPDATE
- switch to inner join to get the MERGE
behaviour.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…