Re: Order of execution of Select clauses

Pria wrote:
> Hi All,
>
> Please correct me if i am wrong.
>
> Internally, the order of execution for a SELECT statement is as
> follows:
> FROM clause
> WHERE clause
> SELECT clause
> ORDER BY clause
> and Oracle allows Order by the table columns that are not included in
> the SELECT clause.
>
> If Order By clause is executed after select clause in select statement,
> then how does Oracle Order by columns that are not included in the
> SELECT clause internally.
>
> Thanks in Advance
> Pria
>

What you describe is correct EXTERNALLY (i.e. semantically). Internally the DBMS is free to do whatever it pleases as long as the result stays the same.
Most importantly it will push the WHERE clause down into the FROM clause (called predicate pushdown) exploit the natural provide by an index).

Of course I can't tell with certainty how Oracle handles columns not present in the select list for order by, but a likely approach is to add them as hidden columns to the select list and supress passing the values to the resultset of the cursor.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab