Re: Table update is taking too much time

• 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
>

-- 





--
http://www.freelists.org/webpage/oracle-l