Order of Predicate Execution #1

In my previous post, I wrote about the parsing operation and what happens first. In the footnote I said that the order doesn’t affect performance, the cost based optimizer doesn’t care about the order of stuff in the query, right? Well, not quite.

Preparing the environment

In order to check that, I’ll create a table with a two numeric columns. And the main thing will be two functions. One function will return a unique value (the value it gets), the other will return a constant value. But each function will sleep for 1 second before returning the value, imitating a hard work that needs to be done before returning the result.

This is the code:


create table sqlexec (num1 number, num2 number);

begin
  for i in 1..10
  loop
    insert into sqlexec values(i,i);
  end loop;
  commit;
end;
/

create or replace function uniq (x number)
return number deterministic
is
begin
  dbms_lock.sleep(1);
  dbms_output.put_line('uniq: '||x);
  return x;
end;
/

create or replace function nonuniq (x number)
return number deterministic
is
begin
  dbms_lock.sleep(1);
  dbms_output.put_line('nonuniq: '||x);
  return 1;
end;
/

exec dbms_stats.gather_table_stats(user,'SQLEXEC');

Running queries

It’s quite obvious that Oracle doesn’t know what the functions do. So now let’s simply run a few queries.

First, a query that doesn’t return any rows:


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

So Oracle didn’t execute both functions for each value, it executed only uniq(num1). What about changing the order?


SQL> select * from sqlexec where nonuniq(num2)=0 and uniq(num1)=0;

no rows selected

nonuniq: 1
nonuniq: 2
nonuniq: 3
nonuniq: 4
nonuniq: 5
nonuniq: 6
nonuniq: 7
nonuniq: 8
nonuniq: 9
nonuniq: 10
Elapsed: 00:00:10.11

Order does make a difference! Oracle now executed nonuniq(num2).

How about non functions?


SQL> select * from sqlexec where nonuniq(num2)=0 and num1=0;

no rows selected

Elapsed: 00:00:00.00
SQL> select * from sqlexec where num2=0 and uniq(num1)=0;

no rows selected

Elapsed: 00:00:00.00

OK, this is smarter. In both cases Oracle checks the column itself before executing any function.

How about inline views?


SQL> select *
2 from (select * from sqlexec where uniq(num2)=0)
3 where nonuniq(num1)=0;

no rows selected

nonuniq: 1
nonuniq: 2
nonuniq: 3
nonuniq: 4
nonuniq: 5
nonuniq: 6
nonuniq: 7
nonuniq: 8
nonuniq: 9
nonuniq: 10
Elapsed: 00:00:10.10

In this case, Oracle merges the view into a simple query and runs the nonuniq function. If we’d like, we can force it to run the inline view first using a hint:


SQL> select *
2 from (select /*+ no_merge */ * from sqlexec where uniq(num2)=0)
3 where nonuniq(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.14

What’s next?

An important thing that affects the optimizer is statistics. So in the next post I’ll show how statistics affect the behavior of the execution.

Advertisements

One thought on “Order of Predicate Execution #1

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