Comparison of update statement efficiency (mergeinto)

Last night, I updated a batch of data. It took more than 20 minutes to use the old update method, but less than two seconds to end with mergeinto. The efficiency is really quite different. For details, see: Update T_TMP_N using the BIRTH field of T_TMP_SCHOOL (row 135868 ).

Last night, I updated a batch of data. It took more than 20 minutes to use the old update method, but less than two seconds to end with mergeinto. The efficiency is really quite different. For details, see: Update T_TMP_N using the BIRTH field of T_TMP_SCHOOL (row 135868 ).

A batch of data was updated last night. It took more than 20 minutes to use the old update method, but it took less than two seconds to end with merge. For details, see:

Use the BIRTH field of T_TMP_SCHOOL (row 135868) to update the BIRTHDATE field of T_TMP_NT_CUSTOMERDETAIL (row 763119). The connection condition is T_TMP_SCHOOL.ID = t_tmp_nt_customerdetail.SCHOOLID.

-- Table structure
Create table T_TMP_NT_CUSTOMERDETAIL
(
CUSTOMERID VARCHAR2 (15) not null,
DOCCATEGORY VARCHAR2 (2) not null,
DOCNUMBER VARCHAR2 (20) not null,
BIRTHDATE VARCHAR2 (8 ),
...........
SCHOOLID VARCHAR2 (60)
);

Create table T_TMP_SCHOOL
(
ID VARCHAR2 (20 ),
COMPANY VARCHAR2 (100 ),
NAME VARCHAR2 (20 ),
BIRTH VARCHAR2 (20)
);

-- For the data of the two tables, see:
Select count (1) from t_tmp_nt_customerdetail t; -- 763119
Select count (1) from t_tmp_school; -- 135868

-- To verify the results, the values of birthdate and website space are cleared before the test. A total of 135879 rows are changed.
Update t_tmp_nt_customerdetail t
Set t. birthdate = null
Where t. schoolid is not null;

--- Implementation process:
Create or replace procedure p_tmp_update_customerdetail
Is
V_BeginTran INT: = 0; -- indicates the transaction flag. The initial value is 0 and the Hong Kong Space indicates that no transaction exists.
V_ErrCode INT;
V_ErrMsg VARCHAR2 (200); -- handle abnormal Variables

Begin
-- Set the transaction flag to 1 to start the transaction.
V_BeginTran: = 1;

Merge into t_tmp_nt_customerdetail t
Using (select B. id, B. birth from t_tmp_school B where B. birth is not null)
On (t. schoolid = a. id)
When matched then
Update set t. birthdate = a. birth where t. schoolid is not null;

COMMIT;
-- Commit the transaction and set the transaction flag to 0.
V_BeginTran: = 0;

EXCEPTION
WHEN OTHERS THEN
-- Roll back the transaction if an exception occurs.
IF v_BeginTran = 1 THEN
ROLLBACK;
End if;
V_ErrCode: = SQLCODE;
V_ErrMsg: = SUBSTR (SQLERRM, 1,150 );
Dbms_output.put_line (v_ErrCode );
Dbms_output.put_line (v_ErrMsg );
End;

-- The execution process takes 1.11 seconds.
SQL> exec p_tmp_update_customerdetail;

-- Verify the result again. If the value of the previously cleared birthdate already exists, row 135879 is returned.
Select count (1) from t_tmp_nt_customerdetail t
Where t. schoolid is not null
And t. birthdate is not null;

-- The following statement is used to execute the data for more than 24 minutes:
Update t_tmp_nt_customerdetail t
Set t. birthdate = (select B. birth
From t_tmp_school B
Where t. schoolid = B. id)
Where t. schoolid =
(Select c. id from t_tmp_school c where t. schoolid = c. id)
And t. schoolid is not null; --- Note: Why do you need to write this wordy condition? Without this condition, all the data in the entire table is updated. Therefore, the data must be written to a VM. Therefore, we should do more to avoid being blinded by some superficial phenomena.

This article is from the "srsunbing" blog. Please keep this source