This is Raghav. I have a small but hecty lot of a problem. I have to update a target table which contains approx 3.1 crore records. Update is based on the following criterias: 1> For each item_row number, i have to execute update statement because for each row, I have to check whether that row has column_ind as 'R'. If yes, then update its colA(cal_yr), colB(cal_mh) and colC(ref_cd). 2> Values of ColA, colB and colC differs for each row.
Example: update table1 set cal_yr_no=2006, cal_mh_no=3 , ref_cd='26052STN' where row_no =1 and column_ind='R';
update table1 set cal_yr_no=2005, cal_mh_no=4 , ref_cd='26463STN' where row_no =2 and column_ind='R';
update table1 set cal_yr_no=2006, cal_mh_no=1 , ref_cd='26046STN' where row_no =3 and column_ind='R';
update table1 set cal_yr_no=NULL, cal_mh_no=NULL , ref_cd='2665STN' where row_no =4 and column_ind='R';
update table1 set cal_yr_no=2006, cal_mh_no=NULL , ref_cd='26STN' where row_no =5 and column_ind='R';
Like this I have 209324 UPDATE statements. Since the target table table1 already contains nearly 3.1 crore records, it is consuming 22 minutes to execute 192 sample UPDATE statements which will be approx 6 days to execute 209324 UPDATE statements.
Is there any way out in which I can complete this wihin few minutes
Hi Raghav, you must be joking, you typed 209324 update statements using Textpad? How do you know the values for each update? There must be some input data or table.
Each upate is doing a Full Table Scan on 31.000.000 rows, that's why is slow. If you create a USI on (row_no, column_ind) before running the updates, this would be much faster, because the FTS will be replaced by a 2-AMP access. After updating you can drop it again.
But there's probably a much better solution, if you answer the first Qs.
Being an analyst, joking is beyond my stature and responsibility. Before posting a question, I have analyzed all the possibilities and only when I cud not find a proper solution I have put it in the Forum.
As for ur questions, We are deriving values for yr_no, mh_no and ref_cd based on the existing columns in table1. For your info, my source and my target tables are same. So, all I need to do is execute a SELECT statement to see whether the values derived are proper or not. Using these values, I have copied and pasted in Textpad.
Thanks for your interest. It is really appreciable.
Hi Raghav, if you can create all those Updates using a Select, why don't you simply write a single update statement?
update table1 from (select ... as cal_yr_no, ... as cal_mh_no, ... as ref_cd, ... as row_no, ... as column_ind from table1 where ... ) as dt set cal_yr_no = dt.cal_yr_no cal_mh_no = dt.cal_mh_no ref_cd = dt.ref_cd where row_no = dt.row_no and column_ind = dt.column_ind ;