When i have a function in where clause like this
select * from <table_name> where column1 = 'x' and column2 = 'y' and my_function(column3) = 'z'
how oracle evaluates this SQL. I want to know how the filtering is applied. There is no function based index.
i.e. if the my_function is directly applied it has to be applied on all the rows of the table. But if
first 2 filter condition are evaluated then the number of rows on which my_function needs to be applied
is reduced.
I also want to know in general how the where clause is processed. Does the statistics that has been
gathered on the table play any role in determining the order in which the where clause is evaluated.
Thanks,
Karthick.
.. how oracle evaluates this SQL. I want to know how the filtering is applied. There is no function based index.
...
it depends, you cannot rely on it doing it one way or the other.
... I also want to know in general how the where clause is processed. ...
In 10g, we use a cpu cost assigned to the various bits of the where clause. In all likelihood - the predicate is evaluated using column1 and column2 first in some order AND THEN my_function.
IN ALL LIKELIHOOD. No guarantees, SQL is declarative, the optimizer ultimately decides and statistics could impact this (like assigning statistics to the function itself - assigning a selectivity)
And no, using parenthesis won't change anything
select *
from (select * from t where column1='x' and column2 = 'y')
where my_function(column3) = 'z'
is IDENTICAL to your query, they are not any different.
I always recommend to wrap function calls like that in a select from dual if you are not using them as an index access, i.e.
select * from <table_name> where column1 = 'x' and column2 = 'y' and (select my_function(column3) from dual) = 'z'
that lets us use scalar subquery caching (search for that phrase on this site for more info) to reduce the number of calls we make to my_function()