SPGuar February 2016

Which is best loop to fetch data from a Cusor in Oracle PL/SQL?

I am learning Oracle PL/SQL. I want to know that which loop is best for fetching the data from a Cursor. And also please tell me that when should i use which loop for best performance.

Answers


Lalit Kumar B February 2016

I would rather avoid using an explicit cursor and use a cursor for loop.

For example,

BEGIN
  FOR i IN
  (SELECT column_list FROM table
  )
  LOOP
    <do something>
  END LOOP;
END;
/

Remember, a loop would do it row-by-row a.k.a. slow-by-slow.

Also, there is a reason why the cursor for loop is better over the explicit cursor. In the recent Oracle versions, it is optimized in a better way by doing a bulk collect limit 100 internally.

But, it's not just about bulk collecting, we are dealing with the operations we would subsequently do on the array that we have fetched incrementally. We could further improve the performance by using FORALL statement along with BULK COLLECT.

IMO, if it is possible, then do it in pure SQL. Because, SQL and PL/SQL are two different engines. All procedural code is handled by the PL/SQL engine while all SQL is handled by the SQL statement executor, or SQL engine. There is an overhead associated with each context switch between the two engines. So, if possible avoid context switching by doing it in pure SQL.

Post Status

Asked in February 2016
Viewed 1,450 times
Voted 10
Answered 1 times

Search




Leave an answer