Bulk Update Methods Compared
For comparing update methods, lets create 2 very simple tables using
CREATE TABLE AS SELECT
DROP TABLE t_test;
CREATE TABLE t_test AS
SELECT
LEVEL id,
trunc(dbms_random.value*10) val_prev,
0 target_upd1,
0 target_upd2
FROM dual
CONNECT BY LEVEL <1000000;
DROP TABLE t_test2;
CREATE TABLE t_test2 AS
SELECT
LEVEL id,
trunc(dbms_random.value) val_prev,
trunc(dbms_random.value*100) source_upd1,
trunc(dbms_random.value*1000) source_upd2
FROM dual
CONNECT BY LEVEL <1000000;
Both tables will contain milion rows. First table will have id columns as a quiz of numbers from 1 to 1 000 000, some value column which would not be updated, and two columns with 0 values, as target columns for updting. Second table also have id column as quiz and 2 columns vith random numbers as source updating columns.
For each table we will create B-Tree index on id column
CREATE INDEX t_test_idx ON t_test(id) ;
CREATE INDEX t_test2_idx ON t_test2(id);
Example first. Update with nested SET subquery :
UPDATE t_test
SET (target_upd1,target_upd2) = (
SELECT source_upd1,source_upd2
FROM t_test2
WHERE id = t_test.id
)
WHERE id IN (
SELECT id
FROM t_test2
);
Lets go throw this statment line by line
UPDATE target_table_name
SET (target columns list divided by , [This columns would be updated]) = (
SELECT [list of columns from source table needed for update]
FROM target_table_name
WHERE [join condition]
)
WHERE [list of id's from target table for updating] IN (
SELECT [list of id's from source table for updating]
FROM [source_table_name]
);
Explain plan without indexes.
UPDATE STATEMENT, GOAL = ALL_ROWS 22322484 743940 38684880
UPDATE DWH T_TEST
HASH JOIN RIGHT SEMI 4284 743940 38684880
INDEX FAST FULL SCAN DWH T_TEST2_IDX 626 1003453 13044889
TABLE ACCESS FULL DWH T_TEST 658 743940 29013660
TABLE ACCESS BY INDEX ROWID DWH T_TEST2 14 10035 391365
INDEX RANGE SCAN DWH T_TEST2_IDX 3 4014
The Cost is rather big so we wil gathered statistics
begin
dbms_stats.GATHER_TABLE_STATS('dwh','t_test');
dbms_stats.GATHER_TABLE_STATS('dwh','t_test2');
end;
begin
dbms_stats.GATHER_INDEX_STATS('dwh','t_test_idx');
dbms_stats.GATHER_INDEX_STATS('dwh','t_test_idx');
end;
Explain plan with indexes and staistics.
UPDATE STATEMENT, GOAL = ALL_ROWS 10003084 999999 13999986
UPDATE DWH T_TEST
HASH JOIN RIGHT SEMI 3094 999999 13999986
INDEX FAST FULL SCAN DWH T_TEST2_IDX 613 999999 4999995
TABLE ACCESS FULL DWH T_TEST 661 999999 8999991
TABLE ACCESS BY INDEX ROWID DWH T_TEST2 4 1 12
INDEX RANGE SCAN DWH T_TEST2_IDX 3 1
Thes cost in rather lower,but we will test second type of bulk updating.
Example second.Updateble Join View.
This type of Join needs an uniqueindex on target table id. (For example Primary key)
update(
select new.id as id
,new.source_upd1 as source_upd1
,new.source_upd2 as source_upd2
,old.*
from t_test2 new
JOIN t_test old ON ( new.id = old.id)
)
SET target_upd1 = source_upd1
,target_upd2 = source_upd2
Example third.Merge.
MERGE INTO t_test old
USING t_test2 new ON(old.id = new.id)
WHEN MATCHED THEN UPDATE SET
target_upd1 = source_upd1
,target_upd2 = source_upd2
After implementing indexes and gathering statistics Plan would be:
MERGE STATEMENT, GOAL = ALL_ROWS 3784 999999 29999970
MERGE DWH T_TEST
VIEW DWH
HASH JOIN 3784 999999 25999974
TABLE ACCESS FULL DWH T_TEST 661 999999 11999988
TABLE ACCESS FULL DWH T_TEST2 736 999999 13999986
