Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Thursday November 02, 2006
03:21 PM

Oracle gem of the day: DISTINCT is not ORDER BY

[ #31491 ]

I'm in a training class and just heard the following:

Right now, when you do a SELECT DISTINCT, Oracle performs a SORT internally in order to accomplish the DISTINCT. This means your data happens to come out ordered. I say happens to, because Oracle (and, I presume, any other RDBMS vendor) has always made it clear that unless you use ORDER BY, the order that your query results are returned in is not guaranteed. Of course, people always seem to respond to such warnings by ignoring them. Heh.

So, word on the street now is that Oracle is going to start using some kind of hash join internally instead of a SORT to accomplish the DISTINCT. Meaning your results will no longer be ordered. If you've followed what you've been told ever since somebody first taught you SQL, you'll have no problem, because you don't assume your results to be ordered unless you use ORDER BY. But apparently not everybody does that.

I'm honestly a little entertained at the thought of people who don't listen suddenly being bitten by this. It's like 19100 all over again.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • ..why postgres requires that anything distinct in a distinct clause must also be in the order which is bloody annoying when you want a whole bunch of columns.
    --

    @JAPH = qw(Hacker Perl Another Just);
    print reverse @JAPH;
    • Ew, that's wrong. A definite strike against PostgreSQL.

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers