tag:blogger.com,1999:blog-8884584404576003487.post4348404949768244589..comments2024-02-29T09:43:12.251-05:00Comments on ORACLENERD: WHERE rownum = 1oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-8884584404576003487.post-5529259557419032362010-03-24T07:15:47.693-04:002010-03-24T07:15:47.693-04:00I'm with you guys. Definitely neavil. Has use ...I'm with you guys. Definitely neavil. Has use cases but when I see it in code my first reaction is "you don't CARE which record you get back? Why?!?!"<br /><br />LewisCLewisChttps://www.blogger.com/profile/14652008216013571362noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-82102244430527032832010-03-23T23:54:47.835-04:002010-03-23T23:54:47.835-04:00You had to bring up DISTINCT didn't you?
Anot...You had to bring up <a href="http://www.oraclenerd.com/2009/01/is-distinct-bug.html" rel="nofollow">DISTINCT</a> didn't you?<br /><br />Another one of these "little" indicators that something is amiss.<br /><br />STOPKEY = Optimization, that's the word I was looking for.oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-40006588160872172242010-03-23T23:03:09.439-04:002010-03-23T23:03:09.439-04:00Using Lewis C's new term:
ROWNUM=1 is not evi...Using Lewis C's new term:<br /><br />ROWNUM=1 is not evil, it's neavil.<br /><br />(I love that word!) ;)<br /><br />The thing to recall is that in earlier releases of Oracle, ROWNUM=1 and its variations were not guaranteed to return just one row from the inner query. <br /><br />In many cases, it returned all rows and filtered the result set for just the first one! <br /><br />Which of course achieved precisely nothing in terms of reducing workload!<br /><br />Nowadays of course it returns one row only, BEFORE the entire result set is evaluated. Ie: the "stop-key" optimisation works.<br /><br />Which of course has created a new kind of short-cut for bad design, as described. <br /><br />I do recall seeing in many instances "select DISTINCT ..." slapped into almost every join to avoid doing due dilligence in one's joins and query predicates. <br /><br />It's slowly being replaced by this one. <br /><br />Ah well, I'd rather have the ROWNUM=1 than the awfull ubiquitous DISTINCT!Noonshttps://www.blogger.com/profile/07694829378563989648noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-39571014094013147302010-03-23T22:26:27.702-04:002010-03-23T22:26:27.702-04:00No, I often use ROWNUM for quick EXISTS checks. It...No, I often use ROWNUM for quick EXISTS checks. It's also useful for a simple "TOP n" type query - where you sort in a subquery, then apply the STOPKEY optimisation outside of that - which often allows the optimiser to use less memory for sorting.Jeffrey Kemphttps://www.blogger.com/profile/08514743151986599227noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-70752836430696586532010-03-23T21:57:36.057-04:002010-03-23T21:57:36.057-04:00Would you qualify my example above (EXISTS) as one...Would you qualify my example above (EXISTS) as one of those uses?<br /><br />EAV? We've all come up with that idea at one point or another...thankfully I've read enough before hand to actually implement.<br /><br />WHEN others THEN - I don't like it. 99% of the time you can (or should) know what possible errors will be thrown. Then again, who does exhaustive unit testing?oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-25047992893626591072010-03-23T21:50:54.887-04:002010-03-23T21:50:54.887-04:00Same. These things are intended for use in certain...Same. These things are intended for use in certain situations, and the fact they are so easily abused does not IMO make them "evil".<br /><br />EAV data models, or empty WHEN OTHERS THEN triggers - now *that's* evil :)Jeffrey Kemphttps://www.blogger.com/profile/08514743151986599227noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-64918227051717024922010-03-23T21:13:14.934-04:002010-03-23T21:13:14.934-04:00You are right of course. I wonder if you could cl...You are right of course. I wonder if you could classify with Tom's autonomous transactions and triggers?<br /><br />It's not so much a bug as it <i><b>reveals</b></i> a bug...in the design or data model.oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-16265814055747694342010-03-23T20:53:41.879-04:002010-03-23T20:53:41.879-04:00ROWNUM is not evil, it's just the victim of a ...ROWNUM is not evil, it's just the victim of a large amount of abuse. I pity it.<br /><br />I can't count how many times I've seen queries like this:<br /><br />SELECT o.order_id,<br />(SELECT c.name<br /> FROM customers c<br /> WHERE c.cust_id = o.cust_id<br /> AND ROWNUM = 1) cn<br />FROM orders o;<br /><br />... because customers.cust_id is not unique, so the developer just fixed the "too many rows" "bug" with the "pick a record, any record" method.Jeffrey Kemphttps://www.blogger.com/profile/08514743151986599227noreply@blogger.com