Where clause predicate evaluation order

On my lectures I get quite often asked about how Oracle chooses evaluation order in where clause. It’s quite interesting question so I’ve decided to make today’s blog post about that. Officially Oracle says as follows:

Some of the things that influence the order of application of the predicates are:

• Their position in the WHERE clause
• The kind of operators involved
• Automatic Datatype conversion
• The presence of bind variables
• Joins
• The optimizer mode (Rule or Cost)
• The cost model (IO or CPU)
• The Query Rewrite feature
• Distributed operations
• View merging
• Query Un-nesting
• Other Documented and Undocumented Parameters
• Hints

In addition, the optimizer can also add, transform or remove predicates. One example being transitivity:

a=1 AND a=b

Seeing that, Optimizer knows that b must be equal 1 and adds that to where clause creating following result

a=1 AND a=b AND b=1

This all might seem little confusing. So let’s simplify all of this in one sentence. Basically Oracle is trying to figure out best order of evaluation of predicates so that he can as soon as possible AND as cheaply as possible find out that he can throw away current row. So in that context he wants to check most selective and cheapest predicates first so he can cheaply and right away find out that he does not want current row.

After all we have said here you’ll definitely deduce, that Oracle will very likely evaluate constant predicate 1st, functions second and he will try to keep those pesky subqueries to the last if possible.

Well that is logical, right? Yes, it is and a lot of developers know that and tend to count on that … and bad things happen as usual :)

Let’s make a little demonstration:

CREATE TABLE where_table AS
SELECT * FROM dba_objects;
UPDATE where_table SET object_type = NULL WHERE object_type != 'INDEX';
COMMIT;

CREATE OR REPLACE FUNCTION my_where_func(
    v VARCHAR2)
  RETURN VARCHAR2 DETERMINISTIC
IS
  d pls_integer;
BEGIN
  IF v='INDEX' THEN
    RETURN(v);
  ELSE
    RAISE_APPLICATION_ERROR(-20000, 'NULL not allowed');
  END IF;
END;
/

As you can see, we have a table which is copy of dba_objects and we make it so that object_type column has value NULL or INDEX. Our function is also very simple. When it gets INDEX it returns INDEX. On any other value it throws an error.

Ok. Now let’s have this simple query:

SELECT *
FROM where_table
WHERE owner                   IS NOT NULL
AND object_type                = 'INDEX'
AND my_where_func(object_type) = 'INDEX';

Logically, Oracle will evaluate if object_type is equal to INDEX 1st (its cheapest and most selective check), then he will check whether owner is not null (still very cheap but not so selective) and after all of that, IF row still survives, he’ll call our function (it costs him more CPU then previous predicates).

Now let us run our query and see what we get:

SQL Error: ORA-20000: NULL not allowed
ORA-06512: at "TESTDBA.MY_WHERE_FUNC", line 9

Now being very clever reader you are, you know that only way this can happen is that our function was called with NULL. But how is this even possible when object_type = 'INDEX' should be checked 1st. Ok, let us see the execution plan:

Plan hash value: 3066599507 -------------------------------------------------- | Id | Operation | Name | E-Rows | -------------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | TABLE ACCESS FULL| WHERE_TABLE | 48 | -------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("MY_WHERE_FUNC"("OBJECT_TYPE")='INDEX' AND "OBJECT_TYPE"='INDEX' AND "OWNER" IS NOT NULL))

Well, this is not what we were expecting at all. This is not even remotely right. But how is this possible? Well the reason is simple … I cheated. After the creation of my_where_func I ran this:

ASSOCIATE STATISTICS WITH FUNCTIONs my_where_func DEFAULT COST (1,0,0);

Basically I told Oracle that CPU cost of that function is 1, IO cost is 0 and network cost is 0. So he thinks it’s very very cheap. You might wonder what my point here is. Well, my point is that sometimes Oracle messes up (wrong stats, damaged stats) and will do things like this. You should take that into account when designing your functions. Just a month ago I was helping with issue when report ran for 1 year without any problem and on one day from 23:44 it started to crush with ORA-06502: PL/SQL: numeric or value error: NULL index table key value. Problem was same select as stated before and developer counted on Oracle to call his function last. He was using passed value as a key to associative array and didn’t check the input ‘cos he knew that all not null values must be in index of that array. But Oracle did change order of evaluation and null happened.

Now don’t forget that we are talking about filter predicates. Access predicates on indexes are of course used first.

You might ask youself what happens when Oracle can’t decide which of your two predicates is more expensive. Well he keeps them in order you placed them in your where clause. So sometimes it is good to do a little ordering in your where clause. BUT best think you can do is to TELL Oracle how much each of your functions costs so he knows. We’ll look at how to do that next time.