'Order of execution Oracle Select clause

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?

sqloracle


Solution 1:[1]

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.

Solution 2:[2]

The only difference in both the processing orders is among "group by" and "select" clause. We can settle this by this simple example.

I have a table "emp" with a column "emp_id". Now let me give incorrect column names in group by and select clause and let us see which is picked by Oracle first.

select max(select_test) from emp
 group by group_test;

The error returned 

ORA-00904: "GROUP_TEST": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 2 Column: 15

So this proves that "group by" is processed before "select". Hope this clears your doubt.

Solution 3:[3]

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!

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Utsav
Solution 3 David Aldridge

Related Questions