Order of Predicate Execution #2

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.

Advertisements

5 thoughts on “Order of Predicate Execution #2

  1. 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

    Like

    • 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

      Like

      • 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)

        Liked by 1 person

  2. 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

    Like

  3. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s