In the previous post I talked about the order of predicate execution based on the predicate position and inline view.
As promised, in this post I’ll add statistics and see what happens.
Creating extended statistics
In this demo I’m using functions, so Oracle doesn’t really know what the output of the function is. That’s why any cardinality estimation is a pure guess. In Oracle 11g, Oracle introduced extended statistics for that (and for column group, but that’s another topic for another post). So let’s use that and see how it affects the execution. Note that the functions have to be deterministic for that to work.
SQL> select dbms_stats.create_extended_stats(user,'SQLEXEC','(nonuniq(num1))') from dual;
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'SQLEXEC','(NONUNIQ(NUM1))')
--------------------------------------------------------------------------------
SYS_STU4IGSRLHE1U3X36ZBN$A97XN
Elapsed: 00:00:00.04
SQL> select dbms_stats.create_extended_stats(user,'SQLEXEC','(nonuniq(num2))') from dual;
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'SQLEXEC','(NONUNIQ(NUM2))')
--------------------------------------------------------------------------------
SYS_STUKF44$O2HI$AMXRAQMVP3KAG
Elapsed: 00:00:00.02
SQL> select dbms_stats.create_extended_stats(user,'SQLEXEC','(uniq(num2))') from dual;
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'SQLEXEC','(UNIQ(NUM2))')
--------------------------------------------------------------------------------
SYS_STUJ65EGS3SC7AW4PX5OGIQTOB
Elapsed: 00:00:00.02
SQL> select dbms_stats.create_extended_stats(user,'SQLEXEC','(uniq(num1))') from dual;
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'SQLEXEC','(UNIQ(NUM1))')
--------------------------------------------------------------------------------
SYS_STUK6JZE7XT75L5H8#A9OT2EPH
Elapsed: 00:00:00.03
SQL> exec dbms_stats.gather_table_stats(user,'SQLEXEC');
nonuniq: 1
nonuniq: 1
uniq: 1
uniq: 1
nonuniq: 2
nonuniq: 2
uniq: 2
uniq: 2
nonuniq: 3
nonuniq: 3
uniq: 3
uniq: 3
nonuniq: 4
nonuniq: 4
uniq: 4
uniq: 4
nonuniq: 5
nonuniq: 5
uniq: 5
uniq: 5
nonuniq: 6
nonuniq: 6
uniq: 6
uniq: 6
nonuniq: 7
nonuniq: 7
uniq: 7
uniq: 7
nonuniq: 8
nonuniq: 8
uniq: 8
uniq: 8
nonuniq: 9
nonuniq: 9
uniq: 9
uniq: 9
nonuniq: 10
nonuniq: 10
uniq: 10
uniq: 10
PL/SQL procedure successfully completed.
Elapsed: 00:00:40.38
Executing queries
Now let’s execute some queries.
SQL> select * from sqlexec where uniq(num1)=0 and nonuniq(num2)=0;
no rows selected
uniq: 1
uniq: 2
uniq: 3
uniq: 4
uniq: 5
uniq: 6
uniq: 7
uniq: 8
uniq: 9
uniq: 10
Elapsed: 00:00:10.08
SQL> select * from sqlexec where nonuniq(num2)=0 and uniq(num1)=0;
no rows selected
uniq: 1
uniq: 2
uniq: 3
uniq: 4
uniq: 5
uniq: 6
uniq: 7
uniq: 8
uniq: 9
uniq: 10
Elapsed: 00:00:10.07
This is the same test case from the previous post. However, note that in both cases Oracle executes the “uniq” function and not the first predicate. This is because Oracle knows that the “uniq” function returns a different value each time, while “nonuniq” returns the same. Using “uniq(x)=y” will more likely return less rows than “nonuniq(x)=y”, so it’s better to execute “uniq” first.
Let’s compare function with non-function predicate:
SQL> select * from sqlexec where uniq(num2)=0 and num1>1;
no rows selected
uniq: 2
uniq: 3
uniq: 4
uniq: 5
uniq: 6
uniq: 7
uniq: 8
uniq: 9
uniq: 10
Elapsed: 00:00:09.06
This is interesting. based on the statistics, Oracle should know that uniq(num2) returns a single value, while num1>1 returns 9 values. So I would expect Oracle to run the function first, but it doesn’t (we don’t see “uniq: 1”). That means that Oracle probably gives some “overhead cost” to running the function, so it first executes the non-function predicate.
Footnote
As you can see, Oracle now uses statistics to decide which predicate to execute first. However, it’s important to remember that Oracle doesn’t know how much time or how much work it will take. Oracle uses statistics to decide which predicate to run in order to return the smallest result set. There might be cases where this won’t be a good approach.
For example, we have a 1M rows table and the query: select * from tab where <x> and <y> (where <x> and <y> are predicates). If <x> is a really heavy but returns a single row, while y is really light but returns 100, it might be better to run <y>, get 100 rows and then run <x> only on these rows. Oracle, however, will probably execute <x> first because it returns less rows.
Hi Liron,
It is interesting that after gathering statistics, for the query
SQL> select * from sqlexec where uniq(num1)=0 and nonuniq(num2)=0;
Oracle always decides to execute UNIQ first.
It’s true, the first condition is more selective, but knowing that NONUNIQ onlyreturns 1 value,
it could still be worth checking the second predicate first, because it is either always true or always false,
so, in case that it is false, the first predicate check can be bypassed.
But, in spite of having statistics, Oracle probably does not consider the “always true” or “always false”
advantage, so it will still execute NONUNIQ for each row that satisfies the first predicate,
and therefore it gives priority to the selectivity based on statistics.
Happy Hannukah & Best Regards,
Iudith Mentzel
Hi Iudith,
Thanks for the comment. I agree with you and wonder about that as well. With statistics (and no bind variable), Oracle should know then min and max values and should be able to estimate correctly. However, it never estimates 0 rows, the minimum is 1. So if both predicates return a single row, but one is more selective, maybe it’s better to execute it first.
Another thing, I’m also not aware of any way to control the order (without changing the query to use inline view and add NO_MERGE hint or something similar).
Liron
Liron,
You can use the /*+ ordered_predicates */ hint to make the predicates operate in the order they were supplied in the query – though there are some ways in which the optimizer can still transform the query to confuse the issue, and there are some precedence rules which override some features of your ordering.
In the simple case of “where nonuniq(num2)=0 and uniq(num1)=0” the hint should be valid and do what you need. (You didn’t say which version of Oracle you’re using, and there is at least one version where I think the hint seemed to be ignored)
Thanks Jonathan,
I didn’t know this hint, cool.
You are right, I forgot about the version. The reason I wrote this post is a function call issue that one of my clients has. They use 11.2.0.4. The tests posted here are 12.1.0.2 on my laptop.
Liron
Hello All,
It just happened that the ORDERED_PREDICATES hint was known to me, but, like many other hints,
it is not documented in the Oracle documentation, at least as far as I am aware.
Could this be because Oracle somehow “wants to discourage” the usage of hints,
or at least that of some of them ?
Maybe it could be useful to have a general hint, like for example /*+ FORCE */
that, when added to a hint list, would cause Oracle to obey all the specified hints,
which means in fact to prevent applying any query transformations that would make impossible
for a specified hint to be obeyed, of course, under the assumption that the different hints specified
do not contradict each other.
From my experience, using NO_MERGE or, in almost all cases, using the “ROWNUM specification” trick
with an inline view are indeed the most reliable ways to force a certain execution path.
Sometimes a WITH clause with a MATERIALIZE hint also works, but not always.
Regarding an “always true” or “always false” predicate, I think that Oracle probably considers that this fact
is only “true from the statistics point of view”, which is still used only for constructing a more efficient plan,
by trying to choose the most efficient order of applying the predicates,
but it cannot be used as a 100% sure criteria for eliminating completely an operation from the query plan,
(as for example, the presence of database constraints does allow)
that is, the NONUNIQ function will still be called for each row returned by the UNIQ predicate,
even if the MIN/MAX statistics value for the NONUNIQ expression shows that its value is never 0.
Thanks a lot & Best Regards,
Iudith Mentzel