Oracle simple update conversion to CURSOR FOR UPDATE OF

Question

I'm working in Oracle RDBMS.

Suppose I have the following UPDATE statement in the context of a stored procedure:

UPDATE memberPlan mp /* C$MP$MEMBERPLANID */
SET ( mp.lastContractId          ,
      mp.lastContractIdChanged   ,
      mp.lastGroupOrPolicyNumber ) = (
         SELECT /*+ INDEX(htu,HTUPDATEMEMBERPLAN$MEMBERPLAN) */
            htu.contractId                        as lastContractId          ,
            CASE WHEN htu.contractId IS NULL THEN 
               NULL                               
            ELSE                                  
               varRunDate                         
            END                                   as lastContractIdChanged   ,
            htu.groupOrPolicyNumber               as lastGroupOrPolicyNumber
         FROM htUpdateMemberPlan htu /* HTUPDATEMEMBERPLAN$MEMBERPLAN */
         WHERE htu.memberPlanId = mp.memberPlanId)
WHERE EXISTS (
    SELECT /*+ INDEX(htu,HTUPDATEMEMBERPLAN$MEMBERPLAN) */ 1
    FROM htUpdateMemberPlan htu /* HTUPDATEMEMBERPLAN$MEMBERPLAN */
    WHERE htu.memberPlanId = mp.memberPlanId);

The problem is that the memberplan table gets locked while the procedure is running.

I'd like to only row-lock, update and release one row at a time and leave the rest of the rows in the table unlocked.

So I devised the following solution:

DECLARE
   CURSOR memberPlan1_cur(parmRunDate IN DATE) IS
      SELECT 
         mp.lastContractId           as lastContractId             ,
         mp.lastContractIdChanged    as lastContractIdChanged      ,
         mp.lastGroupOrPolicyNumber  as lastGroupOrPolicyNumber    ,
         htu.lastContractId          as updLastContractId          ,
         htu.lastContractIdChanged   as updLastContractIdChanged   ,
         htu.lastGroupOrPolicyNumber as updLastGroupOrPolicyNumber 
      FROM 
         memberPlan mp
            INNER JOIN
              (SELECT /*+ INDEX(htu,HTUPDATEMEMBERPLAN$MEMBERPLAN) */
                  memberPlanId                      as memberPlanId            ,
                  contractId                        as lastContractId          ,
                  CASE WHEN contractId IS NULL THEN 
                     NULL                           
                  ELSE                              
                     parmRunDate
                  END                               as lastContractIdChanged   ,
                  groupOrPolicyNumber               as lastGroupOrPolicyNumber
               FROM htUpdateMemberPlan) htu /* HTUPDATEMEMBERPLAN$MEMBERPLAN */
            ON mp.memberPlanId = htu.memberPlanId
      WHERE EXISTS (
         SELECT /*+ INDEX(htu,HTUPDATEMEMBERPLAN$MEMBERPLAN) */ 1
         FROM htUpdateMemberPlan htu /* HTUPDATEMEMBERPLAN$MEMBERPLAN */
         WHERE htu.memberPlanId = mp.memberPlanId)
      FOR UPDATE OF
         mp.lastContractId          ,
         mp.lastContractIdChanged   ,
         mp.lastGroupOrPolicyNumber ;
BEGIN
   FOR memberPlan1_row IN memberPlan1_cur(varRunDate) LOOP
      UPDATE memberPlan mp /* C$MP$MEMBERPLANID */
      SET mp.lastContractId          = memberPlan1_row.updLastContractId          ,
          mp.lastContractIdChanged   = memberPlan1_row.updLastContractIdChanged   ,
          mp.lastGroupOrPolicyNumber = memberPlan1_row.updLastGroupOrPolicyNumber 
      WHERE CURRENT OF memberPlan1_cur;
   END LOOP;
   COMMIT;
END;

I'm basically returning the update values along with the source fields I want to update.

The solution above can be optimized to the code below,
a variant of the SQL above where the existence test is
replaced by the direct INNER JOIN:

DECLARE
   CURSOR memberPlan1_cur(parmRunDate IN DATE) IS
      SELECT /*+ FULL(mp) INDEX(htu,HTUPDATEMEMBERPLAN$MEMBERPLAN) */
         mp.lastContractId                     as lastContractId             ,
         mp.lastContractIdChanged              as lastContractIdChanged      ,
         mp.lastGroupOrPolicyNumber            as lastGroupOrPolicyNumber    ,
         htu.contractId                        as updLastContractId          ,
         CASE WHEN htu.contractId IS NULL THEN 
            NULL                           
         ELSE                              
            parmRunDate
         END                                   as updLastContractIdChanged   ,
         htu.groupOrPolicyNumber               as updLastGroupOrPolicyNumber 
      FROM 
         memberPlan mp
            INNER JOIN htUpdateMemberPlan htu  /* HTUPDATEMEMBERPLAN$MEMBERPLAN */
            ON mp.memberPlanId = htu.memberPlanId
      FOR UPDATE OF
         mp.lastContractId          ,
         mp.lastContractIdChanged   ,
         mp.lastGroupOrPolicyNumber ;
BEGIN
   FOR memberPlan1_row IN memberPlan1_cur(varRunDate) LOOP
      UPDATE memberPlan mp /* C$MP$MEMBERPLANID */
      SET mp.lastContractId          = memberPlan1_row.updLastContractId          ,
          mp.lastContractIdChanged   = memberPlan1_row.updLastContractIdChanged   ,
          mp.lastGroupOrPolicyNumber = memberPlan1_row.updLastGroupOrPolicyNumber 
      WHERE CURRENT OF memberPlan1_cur;
   END LOOP;
   COMMIT;
END;

I've thought about going about this another way below:

DECLARE
   CURSOR memberPlan1_cur IS
      SELECT 
         mp.lastContractId          ,
         mp.lastContractIdChanged   ,
         mp.lastGroupOrPolicyNumber 
      FROM memberPlan mp
      WHERE EXISTS (
         SELECT /*+ INDEX(htu,HTUPDATEMEMBERPLAN$MEMBERPLAN) */ 1
         FROM htUpdateMemberPlan htu /* HTUPDATEMEMBERPLAN$MEMBERPLAN */
         WHERE htu.memberPlanId = mp.memberPlanId)
      FOR UPDATE OF
         mp.lastContractId          ,
         mp.lastContractIdChanged   ,
         mp.lastGroupOrPolicyNumber ;
BEGIN
   FOR memberPlan1_row IN memberPlan1_cur LOOP
      UPDATE memberPlan mp
      SET (mp.lastContractId          ,
           mp.lastContractIdChanged   ,
           mp.lastGroupOrPolicyNumber ) =
              (SELECT /*+ INDEX(htu,HTUPDATEMEMBERPLAN$MEMBERPLAN) */
                  htu.contractId                        as lastContractId          ,
                  CASE WHEN htu.contractId IS NULL THEN 
                     NULL                           
                  ELSE                              
                     varRunDate
                  END                                   as lastContractIdChanged   ,
                  htu.groupOrPolicyNumber               as lastGroupOrPolicyNumber
               FROM htUpdateMemberPlan htu /* HTUPDATEMEMBERPLAN$MEMBERPLAN */
               WHERE mp.memberPlanId = htu.memberPlanId)
      WHERE CURRENT OF memberPlan1_cur;
   END LOOP;
   COMMIT;
END;

The solution above I believe will give me problems because the update is not assigning values individually as in

SET column1=value1,column2=value2,column3=value3

but using the

SET (column1,column2,column3) = (SELECT ...) syntax

I thought of a third way to accomplish my goal using a second
cursor and 3 variables:

DECLARE
   varLastContractId          memberPlan.lastContractId%TYPE          ;
   varLastContractIdChanged   memberPlan.lastContractIdChanged%TYPE   ;
   varLastGroupOrPolicyNumber memberPlan.lastGroupOrPolicyNumber%TYPE ;

   CURSOR memberPlan1_cur IS
      SELECT 
         mp.memberPlanId            ,
         mp.lastContractId          ,
         mp.lastContractIdChanged   ,
         mp.lastGroupOrPolicyNumber 
      FROM memberPlan mp
      WHERE EXISTS (
         SELECT /*+ INDEX(htu,HTUPDATEMEMBERPLAN$MEMBERPLAN) */ 1
         FROM htUpdateMemberPlan htu /* HTUPDATEMEMBERPLAN$MEMBERPLAN */
         WHERE htu.memberPlanId = mp.memberPlanId)
      FOR UPDATE OF
         mp.lastContractId          ,
         mp.lastContractIdChanged   ,
         mp.lastGroupOrPolicyNumber ;

   CURSOR htUpdateMemberPlan1_cur(
             parmRunDate      IN DATE  ,
             parmMemberPlanId IN NUMBER) IS
      SELECT /*+ INDEX(htu,HTUPDATEMEMBERPLAN$MEMBERPLAN) */
         htu.contractId                        as lastContractId          ,
         CASE WHEN htu.contractId IS NULL THEN 
            NULL                           
         ELSE                              
            parmRunDate
         END                                   as lastContractIdChanged   ,
         htu.groupOrPolicyNumber               as lastGroupOrPolicyNumber
      FROM htUpdateMemberPlan htu /* HTUPDATEMEMBERPLAN$MEMBERPLAN */
      WHERE htu.memberPlanId = parmMemberPlanId;
BEGIN
   FOR memberPlan1_row IN memberPlan1_cur LOOP
      OPEN htUpdateMemberPlan1_cur(
         varRunDate                   ,
         memberPlan1_row.memberPlanId );
      FETCH htUpdateMemberPlan1_cur INTO 
         varLastContractId          ,
         varLastContractIdChanged   ,
         varLastGroupOrPolicyNumber ;

      UPDATE memberPlan mp
      SET mp.lastContractId          = varLastContractId          ,
          mp.lastContractIdChanged   = varLastContractIdChanged   ,
          mp.lastGroupOrPolicyNumber = varLastGroupOrPolicyNumber 
      WHERE CURRENT OF memberPlan1_cur;

      CLOSE htUpdateMemberPlan1_cur;
   END LOOP;
   COMMIT;
END;

Which of the three CURSOR solutions above is the right way to go about this?

Answer 1

Just off the cuff I would guess that the first cursor version would perform better than the second cursor version (assuming they end up with the same results). The reasoning: performing a single inner join (first cursor version) with htUpdateMemberPlan would be expected to perform better than the multiple joins (one for each iteration in the cursor loop) of the second version. But that is really a just a guess. I'd run a sql trace and look at what is going on.

It also seems that the first version retrieves a static snapshot of the values from the htUpdateMemberPlan which is consistently used in the cursor loop. The second version does a lookup on htUpdateMemberPlan for each row as it is being processed. So it may be possible that the value of, say, varRunDate might be changed by another session between the time the cursor was created and the time a specific row of the cursor was processed in the second version. But not in the first version. That would give slightly different meanings to the two approaches. Which would you want? Again, just hand waving here. May be mistaken.

Also wondering if the CASE statement might be replaced by NVL2(contractId, varRunDate, null). Then again, perhaps not any better :)