Oracle 10g Explicit Cursor ~ need help

I have (3) stored procedures that I was asked to combine into one procedure and to create
an explicit cursor calling each row to prevent the full table scan.  I am updating a Med_Claims table with
5 million members with 27 months of claims. Right now the procedures i have  take 90 hours.

I am not sure how to to it. Does anyone know how to collapse these (3) into one and change it
to an explicit cursor?

BID is the schema
Med_Claims is the medical claims table where all the updates need to occur.
Member table has the member dob which will be used to calc and update the Med_Claims table.


///////////////////////////////////////////////////////////////////////////////////////////////////////////////
this procedure takes the date of birth (dob) from the BID.Member
to get the actual age based on the date of service (dos) on the BID.med_Claims table
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

CREATE OR REPLACE PROCEDURE UPDATE_NULL_CLAIM_AGE
IS
BEGIN
    LOOP
        UPDATE   BID.Med_Claims c
           SET   CLAIM_AGE   =
                   NVL((SELECT   floor(months_between(c.dos,m.dob)/12)
                        FROM   BID.MEMBER m
                       WHERE   c.member = m.member) ,0)
             WHERE   AGE IS NULL AND ROWNUM <= 50000;
        EXIT WHEN sql%ROWCOUNT = 0;
        COMMIT;
    END LOOP;
END;

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////
this procedure takes info from the reference table and joins it to the Med_Claims table
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

CREATE OR REPLACE PROCEDURE UPDATE_PROC_DESC
IS
  BEGIN
      LOOP
 UPDATE BID.Med_Claims a
      SET PROC_DESC  =
            NVL((SELECT   PROC_CAT
                 FROM BID.REF_PROC_CAT b
                      WHERE a.PROCCODE = b.PROC_CD), 'none')
                WHERE  PROC_DESC IS NULL AND ROWNUM <= 50000;
EXIT WHEN sql%ROWCOUNT = 0;
        COMMIT;
   END LOOP;
END;

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
this procedure updates the TOS column and assigns a code based on other columns in the
Med_Claims table.
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


CREATE OR REPLACE PROCEDURE UPDATE_TOS
IS
BEGIN
    LOOP
        UPDATE   BID.Med_Claims
           SET   TOS    =
                     CASE

WHEN CLASS = 'CHIR'  AND NOT
 (TOS  = '4' OR PROC IN ('RAD_CT', 'RAD', 'RAD_GEN')) THEN  '11'

WHEN PROC_DESC = 'SRG'  AND CLASS IN ('MD','DO') AND
      POS = '1' AND TOS IN('2','9') THEN '12'

ELSE '99'
                     END
         WHERE  TOS  IS NULL AND ROWNUM <= 50000;
 
        EXIT WHEN sql%ROWCOUNT = 0;
        COMMIT;
    END LOOP;
END;
 
JulieGraceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
Updating the same full table 3 times is definitely NOT a good idea.
See the below code, it uses an explicit cursor and bulk operations. Not tested. Fix the syntax errors if there are any.
If you are sure that the ref_proc_cat table will have a matching entry for the proccode every time, then you may change the LEFT OUTER JOIN to INNER JOIN to further enhance the speed.
create or replace procedure update_med_claims
as
 type vchar_tbl_type is table of varchar2(20) index by binary_integer;
 type num_tbl_type is table of number index by binary_integer;
 type rwid_tbl_type is table of rowid index by binary_integer;
 
 l_age_tbl num_tbl_type;
 l_proc_desc_tbl vchar_tbl_type;
 l_tos_tbl vchar_tbl_type;
 l_rwid_tbl rwid_tbl_type;
 l_batch_size number := 10000;
 
 cursor med_cur IS
 SELECT ROWID, floor(months_between(c.dos,m.dob)/12) age, b.PROC_CAT,
 CASE  WHEN CLASS = 'CHIR'  AND NOT (TOS  = '4' OR PROC IN ('RAD_CT', 'RAD', 'RAD_GEN')) THEN  '11'
       WHEN PROC_DESC = 'SRG'  AND CLASS IN ('MD','DO') AND POS = '1' AND TOS IN('2','9') THEN '12'
       ELSE '99'
 END TOS
 FROM med_claims c INNER JOIN member m on (c.member = m.member)
 LEFT OUTER JOIN REF_PROC_CAT b on (c.PROCCODE = b.PROC_CD);
begin
 open med_cur;
 loop
   fetch med_cur bulk collect into l_rwid_tbl, l_age_tbl, l_proc_desc_tbl, l_tos_tbl LIMIT l_batch_size;
   exit when l_rwid_tbl.count = 0;
   
   forall i in l_rwid_tbl.first..l_rwid_tbl.last
     update med_claims
     set CLAIM_AGE = l_age_tbl(i),
         PROC_DESC = l_proc_desc_tbl(i),
         TOS = l_tos_tbl(i)
     where rowid = l_rwid_tbl(i);
     
   commit;
 end loop;
 commit;
 close med_cur;
end;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JulieGraceAuthor Commented:
I forgot to mention that the procedure above for the Case statement to assign the TOS has to go last since it depends on the other two updates to fill the column before it can run.  
0
SujithData ArchitectCommented:
Have you read the code posted above?
The only dependency I see is that it uses PROC_CAT to get TOS. You may change the cursor as below.
(You know more about your  system, so you should be able to find what changes to be made, this is a direction for you to start with, though mostly complete.)

Change the cursor like this.
 cursor med_cur IS
 SELECT ROWID, floor(months_between(c.dos,m.dob)/12) age, b.PROC_CAT,
 CASE  WHEN CLASS = 'CHIR'  AND NOT (TOS  = '4' OR PROC IN ('RAD_CT', 'RAD', 'RAD_GEN')) THEN  '11'
       WHEN b.PROC_CAT = 'SRG'  AND CLASS IN ('MD','DO') AND POS = '1' AND TOS IN('2','9') THEN '12'
       ELSE '99'
 END TOS
 FROM med_claims c INNER JOIN member m on (c.member = m.member)
 LEFT OUTER JOIN REF_PROC_CAT b on (c.PROCCODE = b.PROC_CD);

Open in new window

0
JulieGraceAuthor Commented:
Since the TOS is updated last it will work.  Thanks for your help.  If i run into another issue I will re-post.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.