Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: sorting
Add ORDER BY to make ANY query faster
Yes it’s SCBT day here in Perth! SCBT = Silly Click Bait Title This post is just a cautionary tale that it is easy to get caught up judging SQL performance solely on a few metrics rather than taking a more common sense approach…
ROWNUM and ORDER BY
Just a quick revisit on an old topic so I could link to this post in a Stack Overflow discussion. SQL> create table t ( x int ); Table created. SQL> SQL> insert into t 2 select dbms_random.value(1,1000) 3 from dual 4 connect by…
Never rely on an assumed order
We’ve (hopefully) all had it drummed into us enough times – you cannot assume the order of results from queries unless you explicitly include an ORDER BY statement. Here’s another trivial example of this- I was doing a little demo script for an AskTom…
A little known ORDER BY extension
Within a CONNECT BY statement, you can order siblings, that is, under a particular branch of the hierarchy, the child entries can be ordered, but you still preserve the hierarchy. SQL> select lpad(‘*’, level, ‘*’ ) || ename ename 2 from emp 3 start…
Mr DISTINCT might not be your friend
Whenever you have the need to use the DISTINCT keyword, its worth just pausing for a second, and making sure that you are not hiding just a larger issue. It actually might represent either incorrect use of SQL or incorrect assumptions from the data…
