October 31, 2012 at 5:37 am
craig 81366 (10/31/2012)
GilaMonster (10/31/2012)
You can't insert or delete a row multiple timesI beg to differ
...
that spool is essential to protecting against deleting more rows than expected
"Delete a row multiple times" and "Delete more rows than expected" – there is a big difference, I believe.
October 31, 2012 at 6:15 am
vk-kirov (10/31/2012)
craig 81366 (10/31/2012)
GilaMonster (10/31/2012)
You can't insert or delete a row multiple timesI beg to differ
...
that spool is essential to protecting against deleting more rows than expected
"Delete a row multiple times" and "Delete more rows than expected" – there is a big difference, I believe.
Yeah, it is all about using the exact SAME row more than once... If you INSERT the 'same' row more than once, it becomes 2 different rows in the database. If you DELETE the 'same' row more than once, you are essentially only deleting it once... Once the row is deleted, it is deleted 🙂
October 31, 2012 at 6:19 am
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too :hehe:No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.
This is a common misconception. Internally, all data-changing operations are referred to as updates. This includes insert, delete, update, and merge. Confusing for SQL people perhaps, but that's the way it is. This usage in Ian's post seems to have misled Steve today because Halloween Protection is most definitely required (and a strategy computed) for update plans (I/U/D/M) in general, not just updates. There were several bugs (in SQL Server 7/2000 I think) that related to incorrect HP for inserts and deletes with self-joins, for example.
Craig Freedman states this explicitly at the end of the following entry:
http://blogs.msdn.com/b/craigfr/archive/2008/02/27/halloween-protection.aspx
edit: Adding KB http://support.microsoft.com/kb/810026
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 31, 2012 at 6:29 am
Thanks for the great question. Had to do a little reading to get the answer.
October 31, 2012 at 6:30 am
vk-kirov (10/31/2012)
craig 81366 (10/31/2012)
GilaMonster (10/31/2012)
You can't insert or delete a row multiple timesI beg to differ
...
that spool is essential to protecting against deleting more rows than expected
"Delete a row multiple times" and "Delete more rows than expected" – there is a big difference, I believe.
It seems to me that you're quibbling about how a questionable statement by GilaMonster differs from mine.
Perhaps take a look at the very last sentence of Craig Freedman's (not me) blog post:
http://blogs.msdn.com/b/craigfr/archive/2008/02/27/halloween-protection.aspx
He says he's been working for the SQL Server team for quite a few years, so probably can be considered authoritative on the matter. 😉
Edit: To clarify... it's not the statement "cannot delete a row multiple times" that I consider questionable. (Obviously :-P) It's questionable to use that as the explanation why DELETE's don't apply to the Halloween Problem.
October 31, 2012 at 6:51 am
bitbucket-25253 (10/30/2012)
Hmm caught me on that one with Halloween coming up in the USA tomorrow thought this was one of those joke questions ... OH well so be it
Same here... I tried to think of what statement I'd want to protect against the most... and it was a toss up between update and delete.... sadly I picked the wrong one.
October 31, 2012 at 6:53 am
Nice interesting question Steve:-)
October 31, 2012 at 8:22 am
Happy Halloween All
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 31, 2012 at 8:35 am
craig 81366 (10/31/2012)
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too :hehe:No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.
I beg to differ.
You can differ as much as you like, but what is called the Halloween Problem (and its soluttion Halloween Protection) is a matter of real history which is on the record, not about what you choose to think it's about.
Consider a DELETE statement of the form:
DELETE TABLE_NAME
WHERE COL < CONST
as opposed to
DELETE TABLE_NAME
WHERE COL < (SELECT AVG(COL) FROM TABLE_NAME)
You'll see a spool in the second DELETE, and that spool is essential to protecting against deleting more rows than expected due to the fact that the AVG changes as a result of rows already deleted.
Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else. It is of course rather a good thing that the spool just happens to ensure that the Halloween Problem can't arise, so that no Halloween protection is needed in this case. One side effect of that good thing is that that 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only, because the delete issue didn't exist; and the Halloween Problem is the name that they gave to the problem they discussed that day, and that is a problem with update only, and Halloween Protectiopn means protectin from that update only problem, not anything else. At the 1995 System R/SQL reunion surviving members of the team talked about the early development of the language now called SQL, and Pat Selinger (she may still have been Pat Griffiths when she and Morton Astrahan identified this problem, or maybe she was already married by then) gave a description of this particular thing. Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available as an SRC technical note, which is currently accessible at his wesite and (in a Russian version) at CITForum.ru. The relevant passage is here.
I guess my point is that if it's not the problem that those two people brought to the design issues meeting on Halloween that year, or about the solution to those issues, it is not the Halloween Problem, or Halloween Protection. Calling it that would be about as accurate as calling the Battle of the Nile the Battle of the Plate - unhistorical nonsense.
Tom
October 31, 2012 at 9:07 am
All, have a scary Halloween!
Steve, thanks for the question!
October 31, 2012 at 9:30 am
L' Eomot Inversé (10/31/2012)
craig 81366 (10/31/2012)
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too :hehe:No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.
I beg to differ.
You can differ as much as you like, but what is called the Halloween Problem (and its soluttion Halloween Protection) is a matter of real history which is on the record, not about what you choose to think it's about.
You'll see a spool in the second DELETE, and that spool is essential to protecting against deleting more rows than expected due to the fact that the AVG changes as a result of rows already deleted.
Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else.
Really?
Then why when I run the following by itself, does the plan not spool?
SELECT AVG(COL) FROM TABLE_NAME
An average can be calculated and maintained incrementally without spooling data.
... 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only ... Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available ... The relevant passage is here.
Thanks for that interesting piece of history, and for the link. There's definitely some interesting reading there.
However, I didn't see anything that claimed the Halloween Problem was considered exclusive to the UPDATE statement; in spite of that being how it was discovered. In fact, Pat Selinger went on to say: "An interesting footnote is that we just discovered another one of these as sort of a variation on that, in the latest work that we did having to do with referential integrity and things like that, where the referential integrity relationships were going to trigger off the same kind of nonstop behavior."
In a nutshell, the problem boils down to: a data operation is expected to behave in a predetermined way, but is not due to a side effect of its work unexpectedly affecting its own behaviour recursively.
I guess my point is that if it's not the problem that those two people brought to the design issues meeting on Halloween that year, or about the solution to those issues, it is not the Halloween Problem, or Halloween Protection. Calling it that would be about as accurate as calling the Battle of the Nile the Battle of the Plate - unhistorical nonsense.
In contrast to your historical analogy, consider this:
If someone were to observe a ball and decide: Due to your properties of "roundedness", I dub thee sphere.
And later someone else observes that the Earth has the same properties of "roundedness", you're also a sphere. Would that be wrong?
October 31, 2012 at 12:11 pm
Well,
Happy Halloween!!!
-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."
October 31, 2012 at 1:22 pm
I stumbled across Freedman's explanation in my search on the subject and was thrown by the options as well. Either way, I selected the UPDATE choice as the most likely candidate.
UPDATE: Found the patent and the update is referring to an update plan (any data modification).
http://www.google.com/patents/US6122644
Further down the doc there is a section that states:
38. The method of claim 18 wherein said generating step includes: generating an update plan wherein said update operator is an insert operator.
39. The method of claim 18 wherein said generating step includes: generating an update plan wherein said update operator is a delete operator
Here is also a KB article that specifically indicates that an INSERT, UPDATE, or DELETE statement may need Halloween protection. http://support.microsoft.com/kb/294860
Aigle de Guerre!
October 31, 2012 at 9:33 pm
This probably a pointless argumnent, because it isn't about anything technical, just about historical and terminological accuracy, and it does appear that quite a few people have adopted historically inaccurate terminology (which is a pretty frequent phenomenon - for example we call Heath's normal form, published in 1971, "Boyce-Codd Normal Form" after a paper published several years later, although the earlier paper was certainly known to the senior of the authors of the later paper). But I'm going to make the argument for historical accuracy (especially since several of the people involved are still alive) anyway (if I were in touch with any of them I would ask them for their views, but unfortunately I'm not).
craig 81366 (10/31/2012)
L' Eomot Inversé (10/31/2012)
Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else.Really?
Then why when I run the following by itself, does the plan not spool?
SELECT AVG(COL) FROM TABLE_NAME
Because a displayed MS SQL query plan only shows spool operations when they are not blatantly obvious (which I think is eminently sensible); no other reason. If you think you can deliver a result for a query for AVG without first reading all the records, then think again, because you are horribly wrong. You can't deliver the first value of AVG until you have done that. Then, why read the records again? Sure, you could track changes in AVG as the table changed, if you wanted to and had an awful lot of compute power to spare, but poeple neither wanted to nor had that power to spare so in the early versions of SQL (or of SEQUEL, or whatever it was called before it became SEQUEL) AVG was not recomputed. It isn't today, not because some additional spool operation, beyond what is required to compute AVG in the first place, is introduced, but because AVG has to be computed.
An average can be calculated and maintained incrementally without spooling data.
It can't be computed for the first time without a spool (ie reading all the input before going to the next step).
... 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only ... Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available ... The relevant passage is here.
Thanks for that interesting piece of history, and for the link. There's definitely some interesting reading there.
However, I didn't see anything that claimed the Halloween Problem was considered exclusive to the UPDATE statement; in spite of that being how it was discovered. In fact, Pat Selinger went on to say: "An interesting footnote is that we just discovered another one of these as sort of a variation on that, in the latest work that we did having to do with referential integrity and things like that, where the referential integrity relationships were going to trigger off the same kind of nonstop behavior."
So "something that's "a sort of variation on that" is an instance of "that"? YG&LF, or perhaps GHU - what else can I say? Why did she say "a sort of variation on that" rather than just "another instance of that" or "another example of that", I wonder? Perhaps she though the various possible phrases had different interpretations, and chose one which can't reasonably nbe taken to mean what you want it to mean precisely because what you want it to mean is not what she intended to say? I don't know, I wasn't there at that meeting, but I'm not the one trying to attribute to her a meaning which what she actually said cleafrly doesn't imply.
In contrast to your historical analogy, consider this:
If someone were to observe a ball and decide: Due to your properties of "roundedness", I dub thee sphere.
And later someone else observes that the Earth has the same properties of "roundedness", you're also a sphere. Would that be wrong?
So all spheroids are spheres, are they? They are all round - any corners would make them not spheroids. Even on a technical, as opposed to historical, basis your argument doesn't fly. Of course if in your sphere example the properties of roundness concerned require both that there be a single focus and that all diameters be the same (either of those is a consequence of the other in some geometries, but not in all) then yes, the new thing would be a sphere. That's rather a big and nasty requirement on the properties, I guess the equivalent condition wrt the Halloween Problem would be that it involved an insert statement, so you are seem to be comparing tautologies with contradictions - not usually a useful passtime.
Tom
October 31, 2012 at 10:05 pm
Meow Now (10/31/2012)
I stumbled across Freedman's explanation in my search on the subject and was thrown by the options as well. Either way, I selected the UPDATE choice as the most likely candidate.UPDATE: Found the patent and the update is referring to an update plan (any data modification).
Isn't life wonderful. Here we have a problem identified by IBM back in the 70s, and solved by them back in the 70s, back in the days when the US sopreme Court was still sufficiently sane to assert that software patents were illegal. Then MS gets a patent on solving it in 2000, with a patent that has no non-obvious invention in it (at least nothing non-obvious to anyone familiar with the state of the art in, I think, the late 70s) after the law has been changed (by magic, or perhaps by the appointment of supreme court judges favoured by presidents whose campaign funds came from industrialists who wanted software patents).
We all knew the US patent system had become insane some time ago. What none of us outside the US know is why you pretend that your plutocracy is a democracy, claiming that the people rule when in fact only the money rules, as clearly evidenced by the changes in the last few decades to your copyright system and your patent system (amongst many other indicators).
Tom
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply