1

Consider a query with this structure:

select ..., ROWNUM
  from t
 where <where clause>
 group by <columns>
having <having clause>
 order by <columns>;

As per my understanding, the order of processing is

  1. The FROM/WHERE clause goes first.
  2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
  3. GROUP BY is applied.
  4. HAVING is applied.
  5. ORDER BY is applied.
  6. SELECT is applied.

I cant understand why this article in Oracle magazine by TOM specifies:

Think of it as being processed in this order:

  1. The FROM/WHERE clause goes first.
  2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
  3. SELECT is applied.
  4. GROUP BY is applied.
  5. HAVING is applied.
  6. ORDER BY is applied.

Can anyone explain this order?

David Balažic
  • 1,319
  • 1
  • 23
  • 50
cdummy
  • 455
  • 1
  • 4
  • 14
  • In your query `ROWNUM` pseudo-column is applied in the last as it is in the SELECT list. To know how ROWNUM works see http://stackoverflow.com/a/30321788/3989608 – Lalit Kumar B Aug 29 '15 at 15:38
  • I think I am correct with ROWNUM assignment. create table u (v number); insert into u values (600); insert into u values (60); commit; select rownum r, u.* from u order by r; R V --- --- 1 600 2 60 select rownum r, u.* from u order by v; R V --- --- 2 60 1 600 Can anyone please explain the select clause position? – cdummy Aug 29 '15 at 16:03
  • I convince myself TOM's answer is correct using this scenario (for now). select rownum r,v uu from u group by v,rownum order by v; R UU --- ---- 2 60 1 600 It kind of proves select is applied before group by. Ofcourse if the select has an aggregate function it is going to be evaluated later for each groups. Any other better insights are requested. – cdummy Aug 29 '15 at 16:21
  • https://www.periscopedata.com/blog/sql-query-order-of-operations – JustBeingHelpful May 30 '18 at 20:07

2 Answers2

0

There is not a direct relationship between the clauses in a SQL statement and the processing order. SQL queries are processed in two phases. In the first phase, the code is compiled and optimized. The optimized version is run.

For parsing purposes, the query is evaluated in a particular order. For instance, FROM is parsed first, then WHERE, then GROUP BY, and so on. This explains why a column alias defined in the SELECT is not available in the FROM.

Your description, however, is incorrect with regards to ROWNUM. ROWNUM is a special construct in Oracle . . . as explained in the documentation. It is processed before the ORDER BY.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I think I am correct with the rownum position. create table u (v number); insert into u values (600); insert into u values (60); commit; select rownum r, u.* from u order by r; R V --- --- 1 600 2 60 select rownum r, u.* from u order by v; R V --- --- 2 60 1 600 – cdummy Aug 29 '15 at 16:00
  • 1
    ROWNUM is in fact processed prior to GROUP BY and aggregation, hence "select count(*) from my_table where rownum <= 1" can only return 0 or 1. – David Aldridge Aug 30 '15 at 14:13
  • @DavidAldridge . . . What you say is borne out by the fact that `rownum` in an aggregation query fails, but `min(rownum)` or `max(rownum)` work. The Oracle documentation focuses on the `where` clause. I haven't found a good explanation for what goes on in the `select`. – Gordon Linoff Aug 30 '15 at 14:36
0

I think that it is not only difficult to identify an execution order for a SQL statement, it is actually harmful to your understanding of SQL to attempt to do so.

SQL is a declarative language, in which you define the result that you want, not the way in which that result is to be achieved (although it is possible to strongly affect that way). I have had many experiences of being asked, "So how does this SQL get executed?" by developers more familiar with conventional languages, and the truth is that the SQL doesn't tell you that at all, expect for very simplistic cases. As soon as the case is non-simplistic, you cannot afford to be thinking about SQL in the "wrong way".

It is possibly analogous to the difference between object-oriented and non-object oriented languages, or between functional programming and procedural programming -- there is a necessarily different way of thinking involved.

In SQL, the emphasis should be on understanding the syntax and how it defines the result set, and then on understanding the way that the SQL is processed by the database in the context of the schema to which it refers.

I would focus on reading the Oracle Concepts Guide on the subject, which explains that a query submitted to the system goes through various phases of (and this is a simplistic overview):

  1. Parsing
  2. Transformation
  3. Estimation
  4. Plan generation
  5. Execution

It's important to realise that the SQL that is executed may not actually be the SQL that you submitted, but that you can use various developer tools to get deep insight into just about all of these phases.

It's a very different world!

David Aldridge
  • 51,479
  • 8
  • 68
  • 96