update subquery speed
Hi,The following update stmts do the same thing. Which one should be the fastest and which one is the slowest and why?
Version = 10.2.0.4
Thanks,
Sha
1.
UPDATE target_tab a SET target_col = (SELECT src_col FROM src_tab b WHERE a.key = b.key);
2.
UPDATE(
SELECT b.src_col,a.target_col
FROM target_tab a inner join src_tab b ON a.key = b.key
) SET target_col = src_col;
3.
MERGE INTO target_tab a
USING (SELECT src_col FROM src_tab) b
ON (a.key = b.key)
WHEN matched THEN
UPDATE SET a.target_col = b.src_col;
Version = 10.2.0.4
Thanks,
Sha
1.
UPDATE target_tab a SET target_col = (SELECT src_col FROM src_tab b WHERE a.key = b.key);
2.
UPDATE(
SELECT b.src_col,a.target_col
FROM target_tab a inner join src_tab b ON a.key = b.key
) SET target_col = src_col;
3.
MERGE INTO target_tab a
USING (SELECT src_col FROM src_tab) b
ON (a.key = b.key)
WHEN matched THEN
UPDATE SET a.target_col = b.src_col;
0