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.
1 thought on “Order of Predicate Execution #1”