troubleshooting Question

Is it better to use WHERE CURRENT OF statement

Avatar of klyles95
klyles95 asked on
Oracle Database
12 Comments1 Solution1273 ViewsLast Modified:
When using Oracle explicit cursors, I have tended to use them as follows (see SNIPPET 1), where when I modifiy the record being parsed, I'll use the <cursor_variable>.<field_name> as the key to locate the record (below snipets will run in default HR schema):
___________
SNIPPET 1
DECLARE
c_commits constant number := 50;
cursor emp_cur is select * from employees;
rec_emp emp_cur%ROWTYPE;
 
BEGIN
      OPEN emp_cur;
      LOOP            
          FETCH emp_cur INTO rec_emp;  
          EXIT WHEN emp_cur%NOTFOUND;
                                    
          UPDATE EMPLOYEES
          SET COMMISSION_PCT = NVL(COMMISSION_PCT,0) + 0.01
          WHERE employee_id = rec_emp.employee_id;  -- HERE IS HOW I TYPICALLY LOCATE RECORD TO BE MODIFIED
                      
          IF (MOD(emp_cur%ROWCOUNT, c_commits) = 0) THEN                                                
            COMMIT;                                   
          END IF;                        
    END LOOP;
    CLOSE emp_cur;
    COMMIT;  
end;
/
__________

Recently I was told that the use of WHERE CURRENT OF will reap increase performance gains because the cursor already knows where it is and knows which record requires the modification rather than doing a search as is the above (even though the field is indexed).  Therefore, as an implementation I was advised to place my loop with in a loop and use WHERE CURRENT OF.  I dont know if I have written the next snippet efficiently but was the only way I could get it to work.  I have had to add 3 new variables, include a select count of the table employees as a mechanism to control the first outer loop, change simple loops to while loops.

_____________
SNIPPET 2:
DECLARE
c_commits constant number := 50;

v_id VARCHAR2(10) :=0;
v_loop NUMBER(10) := 0;
v_max_recs VARCHAR2(10) ;

cursor emp_cur is
select * from employees
where employee_id > v_id
order by employee_id
FOR UPDATE;

rec_emp emp_cur%ROWTYPE;

BEGIN    
      SELECT count(*) INTO v_max_recs from employees;
   
      WHILE ( v_loop < v_max_recs )
      LOOP          
            OPEN emp_cur;
            WHILE emp_cur%ISOPEN
            LOOP                  
                  FETCH emp_cur INTO rec_emp;  
                  EXIT WHEN emp_cur%NOTFOUND;  
                  
                  UPDATE EMPLOYEES
                  SET COMMISSION_PCT = NVL(COMMISSION_PCT,0) + 0.01
                  WHERE CURRENT OF emp_cur;  
                               
                  IF (MOD(emp_cur%ROWCOUNT, c_commits) = 0) THEN  
                      v_id := rec_emp.employee_id;                                                      
                      CLOSE emp_cur;
                      COMMIT;                                   
                  END IF;                    
                  v_loop := v_loop +1;                
            END LOOP;
      END LOOP;
      CLOSE emp_cur;
      COMMIT;  
END;
/
_____________

So in SNIPPET 2, my cursor has changed to include a where clause, which can be manipulated in the pl/sql block (where employeed_id > v_id). I now close the cursor after a defined number of records are read and commited, which now requires me to save the last id processed and re-open the cursor with a smaller subset of records.

HERE ARE MY QUESTIONS:  
1.  In a situation of a hundreds of records, I dont believe speed would be such a big deal, but if we were to speak about millions of records, and where it is important to commit periodically, is it better to use WHERE CURRENT OF with in 2 loops as in snippet 2?
2.  Have we gained speed by using WHERE CURRENT OF, only to lose its benefit by having to OPEN..CLOSE the cursor multiple times as well as having to do a SELECT COUNT(*) to get total count to control 1st loop.
3.  If WHERE CURRENT OF syntax is preferable, is snippet 2 efficient or can it be improved further/written better.

Any opinions would be welcomed
ASKER CERTIFIED SOLUTION
Swadhin Ray

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros