09 February 2012

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?

08 February 2012

Winner Selected for Hierarchical Queries Challenge (8471)

In October 2011, we posted a competition regarding hierarchical queries, with this introduction:

Here at the PL/SQL Challenge, we recently encountered the need to accumulate counts of questions up through our topics tree (explained further below). With the help of Kim Berg Hansen, we came up with a solution. In the process, however, Kim realized that (a) the first pass may not be the most optimal solution and (b) he also had a more complex scenario, with which he could use some help. So we decided to offer up this challenge to our players. Remember: you can come back to this problem as often as you'd like. You do not need to submit an answer right away!

Several dozen players submitted solutions, many of them obviously taking lots of time and effort on their parts. Kim went through all the proposed solutions and evaluated them. His analysis is available in the answer for this competition, as well as the "All Submitted Answers" archive.

He chose Milan Vontorcik as offering the best solution for the "complex" scenario and he has been awarded a prize of an O'Reilly Media ebook.

Thanks to all players who submitted a solution to this puzzle.

Steven Feuerstein