tag:blogger.com,1999:blog-4818542164384221282.post5754101880616662563..comments2024-03-25T17:53:49.828+08:00Comments on Grassroots Oracle: Beyond NVLScott Wesleyhttp://www.blogger.com/profile/18106937181788036683noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-4818542164384221282.post-2483580505724458922012-04-12T09:54:10.673+08:002012-04-12T09:54:10.673+08:00textbooks indeed!
I agree, Jeff - although I'...textbooks indeed!<br /><br />I agree, Jeff - although I'm sure I saw a more valid reason in the past!Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.comtag:blogger.com,1999:blog-4818542164384221282.post-32485759893263799352012-04-12T09:51:08.478+08:002012-04-12T09:51:08.478+08:00I've always understood Lazy Evaluation = Short...I've always understood Lazy Evaluation = Short-circuit Evaluation.<br /><br />Also, I'm very dubious about there being any actual performance difference between <> and !=. Reading the linked posts I'd suspect a cached query plan, that gets reparsed when the operator is changed; or, perhaps a stored outline.Jeffrey Kemphttps://www.blogger.com/profile/04255101699328756710noreply@blogger.comtag:blogger.com,1999:blog-4818542164384221282.post-2124015534485579392012-04-12T07:16:52.558+08:002012-04-12T07:16:52.558+08:00depends on the textbook :)
http://en.wikipedia.or...depends on the textbook :)<br /><br />http://en.wikipedia.org/wiki/Lazy_evaluation<br /><br />If I really wanted to be technical I would say "call by name" since lazy implies sharing to avoid duplicate calculations which I do not think coalesce does.llaylandhttps://www.blogger.com/profile/10115891248529540679noreply@blogger.comtag:blogger.com,1999:blog-4818542164384221282.post-84383166573862993972012-04-12T06:54:02.678+08:002012-04-12T06:54:02.678+08:00Never heard the word 'lazy' used to descri...Never heard the word 'lazy' used to describe that ;-)<br /><br />"Short circuit evaluation" perhaps - or is that too textbook?Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.comtag:blogger.com,1999:blog-4818542164384221282.post-20459049995597052922012-04-12T01:33:17.476+08:002012-04-12T01:33:17.476+08:00"anonymous has it"
Lazy means it only ..."anonymous has it" <br /><br />Lazy means it only evaluates its arguments if it needs to.<br /><br />coalesce(null, expensive_function()) returns quickly because it does not call expensive_functionllaylandnoreply@blogger.comtag:blogger.com,1999:blog-4818542164384221282.post-30322445182164160272012-04-11T22:31:45.565+08:002012-04-11T22:31:45.565+08:00@llayland Correct, if the values were equal, then ...@llayland Correct, if the values were equal, then these functions would not return the same result (added example in post). However, there was an unmentioned assertion that those values would never be equal.<br /><br />Additionally, but final comment about "referencing each field once only" - this is a reference to performance issues about the true expression behind these generic examples.<br /><br />@Peter Coalesce certain has advantages, it certainly has <a href="http://www.grassroots-oracle.com/search/label/COALESCE" rel="nofollow">performance benefits</a>.<br />However, in this case it is not appropriate (included SQL in post above)<br /><br />@anonymous (Jan) Elegant, but I think it's more an off the backboard shot, whose mechanics isn't immediately clear.<br />On a tangent, I have seen mutterings of potential issues with using <> over != Found <a href="http://www.freelists.org/post/oracle-l/Performance-Difference-Between-and" rel="nofollow">this</a> after a quick search, but I've seen further investigations in the past.<br /><br />@llayland "has it" ?<br /><br />What do you mean by a "lazy" function? No comparison between its arguments?<br /><br />**<br /><br />In my examples the 'mixer' won't occur thanks to a check constraint, nor will the 'lost sock' appear... get it? <br /><br />;-)Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.comtag:blogger.com,1999:blog-4818542164384221282.post-48895361034665834242012-04-11T20:51:51.920+08:002012-04-11T20:51:51.920+08:00has it.
@Peter - coalesce is like a lazy cascadin...has it.<br /><br />@Peter - coalesce is like a lazy cascading nvl; it does no comparison between its arguments. I often use coalesce instead of nvl because of its laziness.llaylandhttp://llayland.wordpress.comnoreply@blogger.comtag:blogger.com,1999:blog-4818542164384221282.post-89186844078566665282012-04-11T17:00:42.013+08:002012-04-11T17:00:42.013+08:00CASE WHEN custom_value<>normal_value THEN no...CASE WHEN custom_value<>normal_value THEN normal_value <br />END<br /><br />JanAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4818542164384221282.post-33316516975470928322012-04-11T16:32:35.061+08:002012-04-11T16:32:35.061+08:00...and to complete the confusion there is another ......and to complete the confusion there is another function, which does quite the same but better: try coalesce (http://docs.oracle.com/cd/E11882_01/server.112/e17118/functions030.htm#i1001341)Peterhttps://www.blogger.com/profile/09156124143223667798noreply@blogger.comtag:blogger.com,1999:blog-4818542164384221282.post-91386424405093435572012-04-11T14:34:57.529+08:002012-04-11T14:34:57.529+08:00I believe your functions are not equivalent. Here...I believe your functions are not equivalent. Here is a counter example:<br /><br />CASE<br /> WHEN NVL(1, 1) = 1 THEN<br /> NULL<br /> ELSE<br /> 1<br /> END AS alt_value <br /><br />--> null<br /><br />nvl2(1,1,null)<br /><br />--> 1<br /><br />I like to verify simplifications like this with a small plsql block consisting of a nested loops for each of the variables and a check for equality of the expressions. I usually include a dbms_output of the variables and the resultsllaylandhttp://llayland.wordpress.comnoreply@blogger.com