CODEWITHSUNDEEP
sqloracle-databaserdbms
mktl
mktl

Reputation: 29

update query takes long time in oracle

i have a function which update rows of a table , the update is done selecting the lowest creating date and comparing the respective billing dates ,there after increasing the creating date to top most creating date, i am trying to update the originated date based on this selections.

CREATE OR REPLACE FUNCTION OriginatedDate_update( total in Number )
  RETURN VARCHAR2
IS
  intialCount Number(10);
  temp Number(10);
  count1 Number(10);
BEGIN
  SELECT COUNT(DISTINCT(p2.created_date)) INTO temp FROM discrepancy_grid_info p2 where billing_date is not null;
  intialCount :=1;
  temp :=temp+1;
  WHILE intialCount < temp loop 
     update (SELECT * FROM discrepancy_grid_info p1 WHERE (intialCount-1) = (SELECT COUNT(DISTINCT(p2.created_date)) FROM discrepancy_grid_info p2 WHERE p2.created_date < p1.created_date ) and billing_date is not null) f 
     set f.ORIGINATED_DATE=(SELECT MAX(p.ORIGINATED_DATE) FROM DISCREPANCY_GRID_INFO p where  p.PRIM_ID=f.PRIM_ID AND p.PLAN_NAME=f.PLAN_NAME and   p.PROCESS_INSTANCE_ID IN (select process_instance_id from audit_process where client_vendor_id in (select id from client_vendor where vendor_id=(select vendor_id from client_vendor where id=(select client_vendor_id from audit_process where process_instance_id=f.process_instance_id)) and client_id=( select client_id  from client_vendor where id=(select client_vendor_id from audit_process where process_instance_id=f.process_instance_id)))) 
            and p.billing_date between add_months(trunc(f.billing_date,'mm'),-1) and last_day(add_months(trunc(f.billing_date,'mm'),-1)) and p.billing_date is not null and p.created_date < f.created_date  group by p.PRIM_ID,p.PLAN_NAME) 
    where f.billing_date is not null;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
    count1 :=count1+SQL%ROWCOUNT;    
    intialCount :=intialCount+1;
    end loop;
  RETURN count1;
END;

Upvotes: 2

Views: 1268

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Run just ONE query:

 update 
   discrepancy_grid_info p1 
  -- WHERE (intialCount-1) = 
  --  (
  -- SELECT * FROM discrepancy_grid_info p1 
  --     WHERE (intialCount-1) = (
  --         SELECT COUNT(DISTINCT(p2.created_date)) 
  --         FROM discrepancy_grid_info p2 
  --         WHERE p2.created_date < p1.created_date 
  --   ) and */

 set ORIGINATED_DATE=(
   .... rest of your query goes here
)
WHERE p1.billing_date is not null ;

instead of repeating the same query tens, hundreds or maybe thousands number of times in a loop.
Remove the loop also, it is unneeded.
The query can be futhrer optimized, but this very simple change should give a huuuuuuuuge improvement.

Upvotes: 3

Related Questions