Update taking time because of db file sequential read.
One of my users is running following query to update.
update BM_ANUSALES_OCT2005 bm set totalmou=
(select sum(rated_volume)/60 from tnpostpaid.rtx_jul_aug
where r_p_contract_id=bm.co_id and
sncode=1 and call_type=1 and xfile_ind='H')
where act_date < '01-Aug-2005'
he said its running very slow so i traced his session and found that trace file contains ony foll nothing else...
Also, how many rows are you updating here? Might it be possible to run the update "indirectly" by creating a new temporary data set based on a join of BM_ANUSALES_OCT2005 and tnpostpaid.rtx_jul_aug, then either replacing the data in the target table or updating it through an in-line view that joins it with the temporary table? Or maybe you can even update against an in-line view between BM_ANUSALES_OCT2005 and tnpostpaid.rtx_jul_aug right now (although it looks tricky).
yes actually developer was using that to_date. While trying to see explain plan i removed it. sorry for that.
I have asked user to change the logic .
My thinking is since he is using sub-query it might be the cause. I have asked him to make use of cursor. I have aksed him to test with bulk collect.. Do you think that will help.
The way that this update will be processed is that the subquery will be executed for every individual row that needs to be updated ... so you are potentially executing that subquery (and accessing the same index blocks) 100,000 times if you have that many rows to update.
Hence my suggestions to try and join the two tables directly together to get the result calculated more efficiently ... it could be that there is a more effective join strategy than the nested loops you are currently probably seeing.
Yes file is part of tablespace which contains all rtx partition tables for JUL,AUG,SEP,OCT. and rtx_jul_aug is view for these base tables..
I suspect the cause may be in the view definition.
Do one thing:
First create a temp table and measure run time:
create table temp_rtx NOLOGGING
select r_p_contract_id, r_p_customer_id,
where sncode=1 and call_type=1 and xfile_ind='H'
group by r_p_contract_id, r_p_customer_id, rtx_ent_date
And also check how many rows in the temp file.