CREATE TABLE parts_copy
AS
SELECT * FROM parts
/
BEGIN
UPDATE parts
SET part_name = part_name || '1';
FOR rec IN (SELECT part_number, part_name FROM parts
MINUS
SELECT part_number, part_name FROM parts_copy)
LOOP
DBMS_OUTPUT.put_line (rec.part_number);
END LOOP;
END;
/
Vitaliy wrote to say that "I suspect that you marked choice containing MINUS as correct. I spent reasonable time staring at it. At last I intentionally marked it as incorrect. Although choice displays what it updates, that is just a visual coincidence. It would be worst practice: - MINUS perform DISTINCT and table doesn't have a unique constraint; - in multi-session concurrent environment new data can arrive."
After you saw my scoring, you followed up with: "The choice is not just inefficient solution. It is indeed delayed-action mine....As advanced PL/SQL developer I couldn't force myself to mark it as correct Maybe I already have outgrew the challenge's boundary?"
Vitaliy, I admire your principles. You took a stand against code that would solve the quiz but could lead to big problems in one's application. I will change the explanation of that choice to point out the more serious dangers with this approach.
I certainly hope you do not stop playing the Challenge and offering your insights.
Cheers, SF
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
16 October 2010
15 October: MINUS "works" but it is "minus" common sense (1503)
The 15 October quiz offered this choice as a correct, but "overkill" solution:
Subscribe to:
Post Comments (Atom)
I, too, had difficulty deciding whether or not to select the "MINUS" choice. As Vitally pointed out, this solution requires that no other sessions commits changes the table’s data between the time that the previous contents are captured and the "MINUS" query is executed. The list of quiz assumptions does not preclude concurrent sessions. I selected the "MINUS" option based on past experience with the quiz rather than on the technical merits of this specific answer. Newer players may have rejected this choice because they only had the technical aspects and explicitly stated assumptions to draw on. Even though I chose the intended answer, I think a rescoring to give everyone credit for this choice is in order.
ReplyDeleteI do not think it prudent to add an assumption prohibiting concurrent sessions. While at first glance such an assumption might seem to be analogous to the "available objects" assumption, there is a fundamental difference: the ability to create schema objects is not a privilege granted to most users, but the ability to insert, update, or delete data is often granted to many users. Oracle is inherently a concurrent session environment. PL/SQL code that can fail without raising an exception in such an environment is invalid no matter how well it might work in a single session environment. Some might see this as a "best-practice" issue, but it is actually a question of correctness: either it works in all normal environments or it doesn’t.
Hi Steven.
ReplyDeleteI totally disagree with Vitally and jhall62. The question implies that this is not being done in a multi session concurrent environment. You begin your question with "I create and populate the following table:" indicating that you are the only one creating and using the table at the time. Therefore I see no reason to do anything about this question.
Regards,
Ingimundur K. Gudmundsson
Agreed with Ingimundur. If we have to consider any potential concurrent activity from other users, not explicitly described in the question, I'm sure there have been quite a few questions in the past which should have been scored differently.
ReplyDeletePlus, the issue of coding correctly to take account of concurrent activity was not the topic of the question, and therefore not relevant.
Perhaps there's an argument for a small addition to the assumptions list, but I don't think this should be rescored.
Another point to make is that who's to say that the code might be valid in some scenario? What if you wanted to do an update, then check to see if any concurrent activity has caused the update to miss some rows? This is one way you could solve it - not the best way, certainly, but it does work correctly for this scenario.
Thanks for all the feedback! I do plan to add to Assumptions that we are talking about a single session, unless otherwise noted.
ReplyDeleteI will publish a proposal for a new assumption and you can all comment on that.
Cheers, SF