Oracle :Batch updates using PLSQL Blocks.
In this article , we will learn how to update million records in a table using plsql blocks.
Aim : To avoid Undo errors
–> For every 200 transactions we are keeping commit to avoid snapshot too old error.
–> To avoid locks on table.
Please check below video link :
Example :
Step 1 :
Create tables structure as below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
##TABLE_CA_UPDATION is a main table on this table we are updating 1 million records. SYS>>CREATE TABLE DBMON.TABLE_CA_UPDATION(USER_ID NUMBER(10),STATUS NUMBER(4),HIREDATE DATE); Table created. ##TABLE_RECORDS_BKP table is a backup table for TABLE_CA_UPDATION(before update on TABLE_CA_UPDATION the records will be backed up in TABLE_RECORDS_BKP ) sys>>CREATE TABLE DBMON.TABLE_RECORDS_BKP as select * from DBMON.TABLE_CA_UPDATION where 1=2; Table created. ##TABLE_TAR is a a tracking table.This tables will tell us how many records are updated. SYS>>create table DBMON.TABLE_TAR (ID NUMBER); Table created. ##This is driving table to to identify the which records to be updated. SYS>>create table DBMON.TABLE_REC_CA_7MAR(id number); Table created. |
Step 2 :
Check each table structure
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
DBMON>>select * from tab; TNAME TABTYPE CLUSTERID ----------------- ------- TABLE_CA_UPDATION TABLE TABLE_RECORDS_BKP TABLE TABLE_REC_CA_7MAR TABLE TABLE_TAR TABLE 4 rows selected. DBMON>>desc TABLE_CA_UPDATION Name Null? Type ----------------------------------------- -------- ---------------------------- USER_ID NUMBER(10) STATUS NUMBER(4) HIREDATE DATE DBMON>>desc TABLE_RECORDS_BKP Name Null? Type ----------------------------------------- -------- ---------------------------- USER_ID NUMBER(10) STATUS NUMBER(4) HIREDATE DATE DBMON>>desc TABLE_REC_CA_7MAR Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER DBMON>>desc TABLE_TAR Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER |
Step 3 :
Insert records into TABLE_REC_CA_7MAR using block.
|
1 2 3 4 5 6 7 8 9 |
BEGIN for i in 1 .. 100000 loop insert into DBMON.TABLE_REC_CA_7MAR values(i); end loop; end; / SYS>> commit; |
Count the records TABLE_REC_CA_7MAR
|
1 2 3 4 |
SYS>>select count(*) from DBMON.TABLE_REC_CA_7MAR; COUNT(*) ---------- 100000 |
Step 4 :
Insert test data into actual table TABLE_CA_UPDATION (1 million).
|
1 2 3 4 5 6 7 8 9 |
BEGIN for i in 1 .. 100000 loop insert into DBMON.TABLE_CA_UPDATION VALUES(i,1,sysdate-i); end loop; END; / SYS>> commit; |
Count the records
|
1 2 3 4 |
SYS>>select count(*) from DBMON.TABLE_CA_UPDATION; COUNT(*) ---------- 100000 |
Step 5 :
insert dummy record into TABLE_TAR(Tracking table)
|
1 2 3 4 |
SYS>>insert into DBMON.TABLE_TAR values(0); 1 row created. SYS>> commit; |
Step 6 :
Script
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
DECLARE update_counter INTEGER; commit_count NUMBER; err_num number; err_msg varchar2(200); LAST_PROCESSED_ID number :=0; CURSOR TARGET_TABLE_id IS select id from DBMON.TABLE_REC_CA_7MAR SOURCE_TABLE where id > (SELECT ID FROM DBMON.TABLE_TAR) order by source_table.ID; TARGET_TABLE_id_rec TARGET_TABLE_id%ROWTYPE; BEGIN update_counter:=0; commit_count:=200; FOR TARGET_TABLE_id_rec IN TARGET_TABLE_id LOOP LAST_PROCESSED_ID := TARGET_TABLE_id_rec.id; insert into DBMON.TABLE_RECORDS_BKP select * from DBMON.TABLE_CA_UPDATION where user_id=TARGET_TABLE_id_rec.id; update DBMON.TABLE_CA_UPDATION TABLEX set TABLEX.STATUS =2, TABLEX.HIREDATE = SYSDATE where user_id = TARGET_TABLE_id_rec.id; update_counter:= update_counter+SQL%ROWCOUNT; IF MOD(update_counter, commit_count) = 0 THEN Update DBMON.TABLE_TAR set id = TARGET_TABLE_id_rec.id; COMMIT; dbms_application_info.set_action('PL/SQL progress - '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')||' rows: '||to_char(update_counter )); --DBMS_LOCK.SLEEP(1); END IF; END LOOP; update DBMON.TABLE_TAR set ID = LAST_PROCESSED_ID; COMMIT; DBMS_OUTPUT.PUT_LINE('Total count updated->' || update_counter || ': Updated on ->' || sysdate); EXCEPTION WHEN OTHERS THEN ROLLBACK; -- this is to rollback the changes done recently in case the script is terminated due to exception. err_num := SQLCODE; err_msg := substr(SQLERRM, 1, 200); dbms_output.put_line('Error: '||err_num||':: '||err_msg); END; / |
The above script will execute and commits for every 200 transactions.
Example for 1 CPU 10000 records update :
|
1 2 3 4 5 6 7 |
10000 records--> commit--200 rec --> 19.65 seconds SYS>>@script.sql PL/SQL procedure successfully completed. Elapsed: 00:00:19.65 |
Example for 1 CPU 100000 records update :
|
1 2 3 4 |
100000 Records -->Commit -200 --> 47:18.84 SYS>>@script.sql PL/SQL procedure successfully completed. Elapsed: 00:05:43.57 |
Example for 1 CPU 30000 records update :
|
1 2 3 4 5 |
30000 Records --> Commit-200 --> 7:53.65 SYS>>@script.sql PL/SQL procedure successfully completed. Elapsed: 00:03:53.65 |
Example for 1 CPU 30000 records update :
|
1 2 3 4 |
30000 Records --> Commit-1000 --> 7:15.69 SYS>>@script.sql PL/SQL procedure successfully completed. Elapsed: 00:03:15.69 |
Please Use Validations while script executing.
|
1 2 3 4 5 6 7 8 |
@validation.sql select count(1) from TABLE_CA_UPDATION; select count(*) from TABLE_RECORDS_BKP; select count(*) from TABLE_REC_CA_7MAR; select * from TABLE_TAR; select count(1) from TABLE_CA_UPDATION where status=1; select count(1) from TABLE_CA_UPDATION where status=2; |
Thank you …..
Note: Please test scripts in Non Prod before trying in Production.
Loading...
