Optimization of Cursor FOR Loops and Implicit Queries (11588)

The 8 February 2011 quiz tested your knowledge of the performance implications of different ways of fetching a single row of data, ranging from the use of OPEN FOR with a cursor variable to a cursor FOR loop.

The key objective of the quiz was to make sure you were aware that when you open a cursor variable, Oracle always performs a parse.

But we also scored as incorrect the choice that stated:

The PL/SQL optimizer "rewrites" the cursor FOR loop implementation (plch_use_cfl) so that the procedure executes a single, implicit query instead.

And two players objected. The procedure referenced is:

CREATE OR REPLACE PROCEDURE plch_use_cfl (
   id_in IN plch_employees.employee_id%TYPE)
IS
   l_employee   plch_employees%ROWTYPE;
BEGIN
   FOR rec IN (SELECT *
                 FROM plch_employees
                WHERE employee_id = id_in)
   LOOP
      l_employee := rec;
   END LOOP;
END;
/

The objections are as follows:

1. The fourth quiz option states that the last function (that uses a FOR loop over a query) will be rewritten to issue a single query. I marked this as "Correct" because I think I know what you're saying; but I don't think the option is really worded correctly. The FOR loop, even without any compiler optimisation, will only execute 1 query - but without optimisation, it may require multiple FETCHes to get the rows; whereas with the optimisation, it will FETCH 100 rows at a time.

2. I think that the wording of the quiz was somewhat incorrect, because it used several times the term "implicit query" or even "single implicit query", while probably meaning "implicit cursor" and fetching all the rows at once. As far as I am aware, there exists no such thing as "implicit query". A FOR cursor loop using FOR rec IN (SELECT ... ) LOOP ... is already an implicit cursor, as opposed to using CURSOR my_cursor IS SELECT ... followed by FOR rec IN (my_cursor) LOOP ... which is an explicit cursor. So, if we interpret the entire quiz from the performance point of view, then the last choice (No. [9490]) can be interpreted also as: "The compiler will rewrite the cursor FOR loop to achieve a performance similar to that of an implicit cursor" and such an interpretation renders the choice as correct in the context of this quiz. The optimizer will "rewrite" the cursor FOR loop to use a BULK COLLECT of an array of size 100, which, for our case (returning one single row) will have the same performance as using an implicit cursor ( same as a SELECT ... BULK COLLECT INTO ... that returns all the rows at once ). Again, not an "implicit select" but an "implicit cursor", in what concerns performance.

As usual, a choice composed of words instead of code leads to issues of interpretation. I thought I'd worded this choice so that it was clearly not true. And I still believe that. OK, my response:

Regarding the comments in (1), you are right that the choice is not worded correctly...to warrant marking the choice as correct. Both players correctly understand that a cursor FOR loop is optimized to fetch up to 100 rows at a time, instead of single rows. Even putting aside the issue of what "implicit query" means (more on that below), the optimizer does not re-write that code

Regarding "implicit query": yes, that was sloppy language. I should have been more, ahem, explicit. The following is what I had I meant:

The PL/SQL optimizer "rewrites" the cursor FOR loop implementation (plch_use_cfl) so that the procedure executes a SELECT...INTO statement instead of a loop.

I hope everyone will agree that this is false. That is not what the compiler does. OK, so then is my original formulation ambiguous and in need of re-scoring? I don't think so...because:

a. I expect that most players did interpret my phrase "implicit query" to be a SELECT...INTO. In other words, while not as precise as it should have been, it was precise enough.

b. But what if you wanted to interpret that phrase ("implicit query") literally? The second player writes that "As far as I am aware, there exists no such thing as 'implicit query'." Actually, I did a search on the phrase and found this:

An implicit query is a component of a DML statement that retrieves data without using a subquery. An UPDATE, DELETE, or MERGE statement that does not explicitly include a SELECT statement uses an implicit query to retrieve rows to be modified. 

So Oracle does define the implicit query, though not in a way that I had expected, and clearly not in a way that would lead to one interpreting this choice to be correct.

Your thoughts?