Hi Lohar,
I need to update 2.4 million rows in the master_table of 223 million rows.
and update 370 million rows in child_1_table, which contains billions of
records, and update 4.5 millions rows child_table_2 with billions of rows
in it.
Could you please tell me what you want me to insert wnd where.
Thanks,
Ashoke
On Sun, May 29, 2016 at 5:16 PM, Lothar Flatz <l.flatz@xxxxxxxxxx> wrote:
Hi,
it should only take a couple of hours.
- don't do updates, do inserts, create a copy of your table with a new
name, insert into it, drop your old table, rename the new table with the
old name.
- NEVER do row-by-row processing in PL/SQL when it comes to speed. Do
insert - selects only, and do it parallel.
Regards
Lothar
On 29.05.2016 23:42, Ashoke Mandal wrote:
Dear All, Let me know if you have any suggestions to make this update
faster as six days is too high.
Background: Due to some unexpected reason we have same set of test_ids in
the table for two different testing facilities. We are trying to change the
ut_ids of one facility to resolve the duplication issue using the following
code. It works but taking *6* days to complete the update and looking for
tips to make the update faster. The ut_id is part of the primary key in all
three tables (master_table, child_table_1 and child_table_2).
Records to be updated in master_table: 2437461 (total records in this
table is 222760096)
Records to be updated in child_table_1: 370569916
Records to be updated in child_table_2: 4594843
Using following UNIX shell script and a stored procedure:
*=> Calling shell script code:*
export PATH=$PATH:/opt/bin:/usr/ccs/bin:/usr/sbin:/var/opt/oracle
export DMAS_ROOT=/med_oracle/dmas/production
export start_utid=$1
export end_utid=$2
export ORACLE_SID=$3
# Set oracle environment for the specified database ($3)
export ORAENV_ASK=NO
. /usr/local/bin/oraenv
sqlplus user/password <<EOF1
-- Disable the Foreign key constraints on the child tables
ALTER TABLE CHILD_TABLE_1 DISABLE CONSTRAINT
CHILD_TABLE_1_FK_UT_ID_TEST_DATE;
ALTER TABLE CHILD_TABLE_2 DISABLE CONSTRAINT TEST_NOTE_FK_UT_ID_TEST_DATE;
TRUNCATE TABLE master_table_temp;
INSERT INTO master_table_temp select * from master_table where (ut_id>=
${start_utid} and ut_id <= ${end_utid}) and test_facility_code <> 'E';
-- Call the procedure to update the UT_Ids
EXECUTE proc_update_utid ('$1','$2');
-- Enable the foreign key constarints
ALTER TABLE CHILD_TABLE_1 ENABLE NOVALIDATE CONSTRAINT
child_table_1_FK_UT_ID_TEST_DATE;
ALTER TABLE TEST_NOTE ENABLE NOVALIDATE CONSTRAINT
child_table_2_FK_UT_ID_TEST_DATE;
EXIT;
EOF1
*=> Called stored procedure*
create or replace PROCEDURE proc_update_utid(start_utid number, end_utid
number) AS
lv_rec_count number :=0;
lv_update_number number :=0;
lv_total_rec_cnt number :=0;
lv_last_utid number;
lv_logfile varchar2(40);
lv_db_name varchar2(8);
lv_stmt varchar2(1000);
output_file UTL_FILE.FILE_TYPE;
-- Declare a cursor on the impacted records stored in the temporary
working table
cursor ut_cur is select * from master_table_temp order by ut_id;
begin
dbms_output.enable(10000000); -- setup of test output
-- Get the database name
select name into lv_db_name from v$database;
lv_logfile := lv_db_name || '_' || 'update.log';
output_file := UTL_FILE.FOPEN('/med_oracle/dmas/production/log',
lv_logfile, 'A');
utl_file.put_line(output_file,'Start Time: '|| to_char(sysdate,
'DD-MON-YYYY HH24:MI:SS'));
-- Check the number of duplicate UT_IDs to be updated
select count(*) into lv_total_rec_cnt from master_table
where (ut_id >= start_utid and ut_id <= end_utid) and test_facility_code
<> 'E';
utl_file.put_line(output_file,'Total number of duplicate UT_ID before the
update: '|| lv_total_rec_cnt);
--
for ur_rec in ut_cur
loop
-- dbms_output.put_line ('This is right before the update of unit_test
table');
update master_table set ut_id=ut_id+9000000000 where
ut_id=ur_rec.ut_id and
test_facility_code <> 'E';
-- dbms_output.put_line ('This is right before the update of
child_table_1 table');
update child_table_1 set ut_id=ut_id+9000000000 where
child_table_1.ut_id=ur_rec.ut_id and
test_date=ur_rec.test_date;
update child_table_2 set ut_id=ut_id+9000000000 where
child_table_2.ut_id=ur_rec.ut_id and
test_date=ur_rec.test_date;
test_date=ur_rec.test_date;
lv_rec_count := lv_rec_count+1;
if lv_rec_count = 1000
then
lv_update_number := lv_update_number+1;
-- insert a row to the update_status table
insert into update_status values(lv_update_number, ur_rec.ut_id,
sysdate);
commit;
lv_rec_count := 0;
else
lv_last_utid := ur_rec.ut_id;
end if;
end loop;
-- Following code is to address the last set of rows, which are less
than 1000.
if lv_rec_count != 1000
then
lv_update_number := lv_update_number+1;
-- insert a row to the update_status table
insert into update_status values(lv_update_number, lv_last_utid,
sysdate);
-- dbms_output.put_line ('This is right before the second commit');
commit;
end if;
-- Verify if the UT_ID between start_utid and end_utid range with
facility_code <> 'E' are
-- all assigned to a different UT_ID. If that is true then the following
query shouldn't return any row.
select count(*) into lv_total_rec_cnt from master_table
where (ut_id >= start_utid and ut_id <= end_utid) and test_facility_code
<> 'E';
dbms_output.put_line ('The total duplicate UT_IDs in master_table is
' ||lv_total_rec_cnt);
--
utl_file.put_line(output_file,'Total number of duplicate UT_ID after the
update: '|| lv_total_rec_cnt);
utl_file.put_line(output_file,'End Time: '|| to_char(sysdate, 'DD-MON-YYYY
HH24:MI:SS'));
utl_file.fclose(output_file);
end proc_update_utid;
Thanks,
Ashoke
--
