Oracle SQL Clause Evaluation Order

Oracle SQL clause evaluation order

The select list cannot always be evaluated last because the ORDER BY can use aliases that are defined in the select list so they must be executed afterwards. For example:

SELECT foo+bar foobar FROM table1 ORDER BY foobar

I'd say that in general the order of execution could be something like this:

FROM
WHERE
GROUP BY
SELECT
HAVING
ORDER BY

The GROUP BY and the WHERE clauses could be swapped without changing the result, as could the HAVING and ORDER BY.

In reality things are more complex because the database can reorder the execution according to different execution plans. As long as the result remains the same it doesn't matter in what order it is executed.

Note also that if an index is chosen for the ORDER BY clause the rows could already be in the correct order when they are read from disk. In this case the ORDER BY clause isn't really executed at all.

Execution order of conditions in SQL 'where' clause

Are you sure you "don't have the authority" to see an execution plan? What about using AUTOTRACE?

SQL> set autotrace on
SQL> select * from emp
  2  join dept on dept.deptno = emp.deptno
  3  where emp.ename like 'K%'
  4  and dept.loc like 'l%'
  5  /

no rows selected

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    62 |     4   (0)|
|   1 |  NESTED LOOPS                |              |     1 |    62 |     4   (0)|
|*  2 |   TABLE ACCESS FULL          | EMP          |     1 |    42 |     3   (0)|
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     1   (0)|
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0042912 |     1 |       |     0   (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMP"."ENAME" LIKE 'K%' AND "EMP"."DEPTNO" IS NOT NULL)
   3 - filter("DEPT"."LOC" LIKE 'l%')
   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

As you can see, that gives quite a lot of detail about how the query will be executed. It tells me that:

the condition "emp.ename like 'K%'" will be applied first, on the full scan of EMP
then the matching DEPT records will be selected via the index on dept.deptno (via the NESTED LOOPS method)
finally the filter "dept.loc like 'l%' will be applied.

This order of application has nothing to do with the way the predicates are ordered in the WHERE clause, as we can show with this re-ordered query:

SQL> select * from emp
  2  join dept on dept.deptno = emp.deptno
  3  where dept.loc like 'l%'
  4  and emp.ename like 'K%';

no rows selected

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    62 |     4   (0)|
|   1 |  NESTED LOOPS                |              |     1 |    62 |     4   (0)|
|*  2 |   TABLE ACCESS FULL          | EMP          |     1 |    42 |     3   (0)|
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     1   (0)|
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0042912 |     1 |       |     0   (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMP"."ENAME" LIKE 'K%' AND "EMP"."DEPTNO" IS NOT NULL)
   3 - filter("DEPT"."LOC" LIKE 'l%')
   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

Execution order of WHEN clauses in a CASE statement

The value that is returned will be the value of the THEN expression for the earliest WHEN clause (textually) that matches. That does mean that if your line 2 conditions are met, the result will be A2.

But, if your THEN expressions were more complex than just literal values, some of the work to evaluate those expressions may happen even when that expression is not required.

E.g.

 WHEN r.code= '00'                        then 'A1'
 WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2'
 WHEN r.code ='0120'                      then 1/0
 WHEN r.code ='01'                        then 'A4'

could generate a division by zero error even if r.code isn't equal to 0120, and even if it's equal to 00, say. I don't know what the standard has to say on this particular issue but I know that it is true of some products.

Order of evaluation of expression in the update-set-clause in oracle database

You can find this in SQL standard, which defines general rules.

Oracle certainly conforms to this standard.

See here - SQL 92:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Page 393, chapter "13.9 <update statement: positioned>", point 6)

6) The <value expression>s are effectively evaluated before updat-
ing the object row. If a contains a reference
to a column of T, then the reference is to the value of that
column in the object row before any value of the object row is
updated.

Consider a general update syntax:<

UPDATE .... 
   SET <object column 1> = <value expression 1>,
       <object column 2> = <value expression 2>,
       ......
       <object column N> = <value expression N>;

The rule #6 says that all expressions on right side are evaluated first, before updating of any column in the row.

Only old row's values (before the update) are considered while evaluating all expressions.

Select where clause evaluation order

There are no guarantees for evaluation order. The optimizer will try to find the most efficient way to execute the query, using available information.

In your case, since c is indexed and d isn't, the optimizer should look into the index to find all rows that match the predicate on c, then retrieve those rows from the table data to evaluate the predicate on d.

However, if it determines that the index on c isn't very selective (although not in your example, a gender column is rarely usefully indexed), it may decide to do the table scan anyway.

To determine execution order, you should get an explain plan for your query. However, realize that that plan may change depending on what the optimizer thinks is the best query right now.

SQL Conceptual Order Evaluation

A select query is evaluated, conceptually, in the following order:

The from clause
The where clause
The group by clause
The having clause
The select clause
The order by clause

This is "conceptual" processing and explains some of the scoping rules of SQL. The way queries are executed in practice may differ.

SQL Server documentation explains this ordering here.

What is the order of execution of a function in an sql query?

In the first example it will only be executed 100 times. You can verify that by adding a debugging call in the function:

create table mytable (a, b) as select mod(level, 10), level from dual connect by level <= 50;

create or replace function myfunction(p number)
return number as
begin
  dbms_output.put_line('In function for p=' || p);
  return mod(p,3);
end;
/

set serveroutput on

select myfunction(b)
from mytable
where a = 1;

MYFUNCTION(B)
-------------
            1
            2
            0
            1
            2

In function for p=1
In function for p=11
In function for p=21
In function for p=31
In function for p=41

The function is only called for the rows that match the where clause filter. However, as far as I know that isn't guaranteed.

It's rather more complicated in the second example, and is largely up to the optimiser. For my simple demo the optimiser (11gR2 in this case) is evaluating a first, and only calls the function for the rows that match that; but it then calls it again for the select-list value:

select myfunction(b)
from mytable
where a = 1
and myfunction(b) = 2;

MYFUNCTION(B)
-------------
            2
            2

In function for p=1
In function for p=11
In function for p=11
In function for p=21
In function for p=31
In function for p=41
In function for p=41

The function is called for each of the five rows where a=1 as before, and for those where myfunction(b) = 2 it is called a second time, to get the value in the result set.

Again, for this example, things you might think would change this behaviour don't. All of these get exactly the same output:

select myfunction(b)
from mytable
where myfunction(b) = 2
and a = 1;

select x
from (
  select myfunction(b) as x
  from mytable
  where a = 1
)
where x = 2;

select x
from (
  select /*+ materialize */ myfunction(b) as x
  from mytable
  where a = 1
)
where x = 2;

with t (x) as (
  select myfunction(b)
  from mytable
  where a = 1
)
select x
from t
where x = 2;

The optimiser is internally rewriting them all into the same query, and you still get all seven function calls. Adding an undocumented hint does change it:

with t (x) as (
  select /*+ materialize */ myfunction(b)
  from mytable
  where a = 1
)
select x
from t
where x = 2;

         X
----------
         2
         2

In function for p=1
In function for p=11
In function for p=21
In function for p=31
In function for p=41

but you can't (or shouldn't) really use or rely on that.

The indexing, partitioning, the optimiser version, stats, etc. will all affect how the optimiser behaves for your query.

And as other things to consider, you could have a function-based index, or a deterministic function...

So... it depends.

Does the order of where clauses matter in SQL?

No, that order doesn't matter (or at least: shouldn't matter).

Any decent query optimizer will look at all the parts of the WHERE clause and figure out the most efficient way to satisfy that query.

I know the SQL Server query optimizer will pick a suitable index - no matter which order you have your two conditions in. I assume other RDBMS will have similar strategies.

What does matter is whether or not you have a suitable index for this!

In the case of SQL Server, it will likely use an index if you have:

an index on (LastName, FirstName)
an index on (FirstName, LastName)
an index on just (LastName), or just (FirstName) (or both)

On the other hand - again for SQL Server - if you use SELECT * to grab all columns from a table, and the table is rather small, then there's a good chance the query optimizer will just do a table (or clustered index) scan instead of using an index (because the lookup into the full data page to get all other columns just gets too expensive very quickly).

Oracle SQL Clause Evaluation Order