some notes from my friend : heribertus Bramundito
I created a small comparison between below 3 update methods
(so far that I know) with Full Table Scan and also Index Scan:
-
Correlated Update
-
Merge
-
Update from Select
Some highlighted items:
1.
“Correlated Update” is not good at all if we
don’t have index access on the inner table. It will produces huge I/O since we
have to FTS the inner table over and over again (as much as the number of rows
in the outer table)
2.
If the join condition is not matched, “Correlated
Update” will update the row with empty string. We need to pay attention on this
(please check “Data Changes” section below)
It will produce more Undo for those
additional update
3.
If the join condition is not matched, both “Merge”
and “Update from Select” method will leave the row as is. To me it is more
reasonable
4.
With “Update from Select” method, we need to create unique index on the inner
table, otherwise we will ended up with ORA- error
5.
“Update from Select” method is little bit
superior compare to the other 2 methods when we update small percentage of
outer table and there is index on the outer table, so that we can get benefit
from Index Range Scan
Sample Data
SQL> create
table ttarget
2
pctfree 99
3 as
4
select rownum id, lpad(chr(rownum), 70) data
5 from
dual
6
connect by rownum <= 1000;
Table created.
SQL> create
table tsource
2
pctfree 99
3 as
4
select rownum id, lpad(chr(rownum)||'_CHG', 70) data
5 from
dual
6
connect by rownum <= 500;
Table created.
SQL> exec
dbms_stats.gather_table_stats(USER, 'TTARGET');
PL/SQL
procedure successfully completed.
SQL> exec
dbms_stats.gather_table_stats(USER, 'TSOURCE');
PL/SQL
procedure successfully completed.
SQL> select
table_name, blocks, empty_blocks, num_rows
2 from
user_tables
3
where table_name in ('TTARGET', 'TSOURCE');
TABLE_NAME BLOCKS
EMPTY_BLOCKS NUM_ROWS
------------------------------
---------- ------------ ----------
TSOURCE 518 0 500
TTARGET 1024 0 1000
SQL> create
unique index idx_tsource on tsource(id);
Index created.
SQL> select
leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor,
uniqueness from user_indexes where index_name = 'IDX_TSOURCE';
LEAF_BLOCKS
DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
UNIQUENES
-----------
------------- ----------------------- ----------------------- -----------------
---------
1 500 1 1 500 UNIQUE
SQL> create
index idx_ttarget on ttarget (id);
Index created.
Data Changes
Correlated Update
For un-matched rows, we can see DATA is CHANGED
SQL> select
id, trim(data) from ttarget where id > 500 and rownum < 3;
ID TRIM(DATA)
----------
--------------------
501 )
502 ÷
SQL> select
count(*) from ttarget where data like '%CHG%';
COUNT(*)
----------
0
SQL> update
ttarget a set data = (select data from tsource b where a.id=b.id);
1000 rows updated.
SQL> select
count(*) from ttarget where data like '%CHG%';
COUNT(*)
----------
500
SQL> select
id, trim(data) from ttarget where id > 500 and rownum < 3;
ID TRIM(DATA)
---------- --------------------
501
502
Merge
For un-matched rows, we can see data is not changed
SQL> select
id, trim(data) from ttarget where id > 500 and rownum < 3;
ID TRIM(DATA)
---------- --------------------
501 )
502 ÷
SQL> select
count(*) from ttarget where data like '%CHG%';
COUNT(*)
----------
0
SQL> merge
into ttarget a
2
using tsource b
3 on
(a.id=b.id)
4 when
matched then
5
update set a.data=b.data;
500 rows merged.
SQL> select
count(*) from ttarget where data like '%CHG%';
COUNT(*)
----------
500
SQL> select
id, trim(data) from ttarget where id > 500 and rownum < 3;
ID TRIM(DATA)
---------- --------------------
501 )
502 ÷
Update from Select
For un-matched rows, we can see data is not changed
SQL> select
id, trim(data) from ttarget where id > 500 and rownum < 3;
ID TRIM(DATA)
---------- --------------------
501 )
502 ÷
SQL> select
count(*) from ttarget where data like '%CHG%';
COUNT(*)
----------
0
SQL> update
2
(select a.data dtarget, b.data dsource
3
from ttarget a, tsource b
4
where a.id=b.id) c
5 set
c.dtarget=c.dsource;
500 rows merged.
SQL> select
count(*) from ttarget where data like '%CHG%';
COUNT(*)
----------
500
SQL> select
id, trim(data) from ttarget where id > 500 and rownum < 3;
ID TRIM(DATA)
---------- --------------------
501 )
502 ÷
