CODEWITHSUNDEEP
sqloracle-database
cdummy
cdummy

Reputation: 455

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?

Upvotes: 1

Views: 13501

Answers (2)

David Aldridge
David Aldridge

Reputation: 52336

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!

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 0

Related Questions