This is not in the same category as the group by
, I never used any of them reasoning that you mention. It is not laziness to type, or what I call premature optimization
, nor belief in pixie dust. Sorry to disappoint you Chuck ... but, if you want proof of how pervasive the issue actually is, take a look at this answer here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137577300346084930#1141584300346481219
Isn't an order by
missing there before one could declare those two queries equivalent
? And that is Sep 5, 2008.
I would hypothesize that the majority of requirements for top-n or pagination queries actually need sorted results ... and yet almost every answer on this forum, or in the manuals for that matter, fails to mention or caveat for it. Arguing that it should be obvious is hardly persuasive.
Here is another, older, example: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:32812348052#2026966612220
and look for the first run_stats comparison:
9 for x in ( select *
10 from ( select * from t order by object_name )
11 where rownum < 10 )
14 end loop;
23 for x in ( select a.*, rownum r
24 from ( select * from t order by object_name ) a )
26 exit when ( x.r >= 10 );
27 end loop;
Given enough rows in t, the first pl/sql loop iterates 9 times, guranteed, nobody would dispute that! The second query is not sorted and hence the pl/sql loop based on it can iterate anywhere between 0 and 9 times ... at least, in theory ;) ... presumption of the SQL being procedural
But people do have the right to revise their understanding, Tom Kyte or I included.
Believe or not ... I want to understand
this and not just save keystrokes.
So, here is my
theory:"Unless and until you have an order by ...""To be guaranteed to get to Rome you need to take a road to Rome""But, if all roads lead to Rome, then take a road and you'll get to Rome ... guaranteed"
By "all roads lead to Rome"
I do not mean what I have always observed with ROWNUM
or the past, current or future internal implementations of ROWNUM
(which I have no way of knowing or predicting) ... I mean its very definition
:For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.select rownum, x.* from (select ... order by ... ) x
My query is sorted ... and, as the rows are coming out, the ROWNUM labels are applied ... sequentially
I do have a guarantee
and it is this: ROWNUM will not interfere with the pre-sorted result set.
To work through a simple example: if the inner query returns 3 rows sorted by the values A, B and C then:
Rownum 1 is associated with row A
Rownum 2 is associated with row B
Rownum 3 is associated with row C
I hope everyone agrees so far. I said it in my first review: "The rownum sequencing will always be consistent with that order by
Your contention is that the final result set may be returned like this:
not ordered anymore. That is, maybe this is false: "... and the rows will come back sequentially by rownum ...
And my contention is that, by its own definition
, ROWNUM will not interfere with the result set, sorted or not sorted.
And I did register the critique:You presume SQL to be procedural - "aftwards" - having some explicit order of operation. It doesn't have to be that way.
is used as an antonym for set-based
. Most of the time I've seen you use this argument, that SQL is not procedural
, it is appropriate. But just because you've used it, appropriately, most of the time it does not mean it is universally true
The moment you throw ROWNUM or CONNECT BY in a query you are out of the realm of sets ... there is no order in a set ... the moment you have features operating on sorted sets the set-based
argument fades away. Put those things in a query and it isn't I
who presume the SQL to be procedural ... the SQL is
procedural in those instances.
The very reason a top-n or pagination query or something like "select ... from ... where rownum > 1"
works the way they do is because, conceptually, the SQL is operating sequentially
for a while ... it has briefly shifted to procedural
ROWNUM operates on sets, sorted or not ... ROWNUM does not sort ... ROWNUM does not interfere with the set it is given ... but it operates procedurally by definition. ROWNUM 1 will always come before ROWNUM 2 and so on ... it just is.
So, I'm still at:The rownum sequencing will always be consistent with that order by ... and the rows will come back sequentially by rownum ... and hence, by the order by.
I think will both remain convincingly unconvinced with each other's argument. Fine ... better than lazy or naïve, I suppose!