Hi Ashoke,
Scott already gave you the long version of the procedure.
Since I understand you want to update all three tables, you make an
empty copy of all three tables.
Then you insert into the copy selecting from the original table. In your
select you change the data as you want it to be.
The point is that inserts create much less overhead than updates.
When I was part of RWPG we had two tables or to compare, 2 billion rows
each. I looked up my timings.
It took 15-20 Minutes to write 2 billion rows with DOP 256 .
That was no super fast hardware, like an Exadata.
Regards
Lothar
On 30.05.2016 02:02, Ashoke Mandal wrote:
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 <mailto: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
--
