sql查询中函数执行的顺序是什么? [英] What is the order of execution of a function in an sql query?

问题描述

如果我构建自己的函数"myfunction"并执行以下查询:

If I build my own function "myfunction" and this query:

select
myfunction(parameters)
from
mytable
where
a and b and c and d

如果mytable有100万行，但是在我的位置之后我只有100行. 当我执行此查询时，myfunction将执行100或100万行吗?

if mytable has 1 million lines, but after my where I have only 100 lines. When I had executed this query, myfunction will be executed for 100 or 1 million lines?

在这种情况下会发生什么?

and in this case what happens?

select
myfunction(parameters)
from
mytable
where
a and b and c and d and myfunction(parameters) == e

推荐答案

在第一个示例中，它将仅执行100次.您可以通过在函数中添加调试调用来验证这一点:

In the first example it will only be executed 100 times. You can verify that by adding a debugging call in the function:

create table mytable (a, b) as select mod(level, 10), level from dual connect by level <= 50;

create or replace function myfunction(p number)
return number as
begin
  dbms_output.put_line('In function for p=' || p);
  return mod(p,3);
end;
/

set serveroutput on

select myfunction(b)
from mytable
where a = 1;

MYFUNCTION(B)
-------------
            1
            2
            0
            1
            2

In function for p=1
In function for p=11
In function for p=21
In function for p=31
In function for p=41

仅对与where子句过滤器匹配的行调用该函数.但是，据我所知，这并不能保证.

The function is only called for the rows that match the where clause filter. However, as far as I know that isn't guaranteed.

在第二个示例中，它要复杂得多，并且在很大程度上取决于优化程序.对于我的简单演示，优化器(在本例中为11gR2)首先评估a，并且仅调用与之匹配的行的函数；但随后会再次调用它作为选择列表值:

It's rather more complicated in the second example, and is largely up to the optimiser. For my simple demo the optimiser (11gR2 in this case) is evaluating a first, and only calls the function for the rows that match that; but it then calls it again for the select-list value:

select myfunction(b)
from mytable
where a = 1
and myfunction(b) = 2;

MYFUNCTION(B)
-------------
            2
            2

In function for p=1
In function for p=11
In function for p=11
In function for p=21
In function for p=31
In function for p=41
In function for p=41

对于以前在a=1的五行中的每一行都调用该函数，对于在第二次调用myfunction(b) = 2的五行中的每一行都调用该函数，以获取结果集中的值.

The function is called for each of the five rows where a=1 as before, and for those where myfunction(b) = 2 it is called a second time, to get the value in the result set.

同样，在此示例中，您可能认为不会改变这种行为的事情.所有这些都获得完全相同的输出:

Again, for this example, things you might think would change this behaviour don't. All of these get exactly the same output:

select myfunction(b)
from mytable
where myfunction(b) = 2
and a = 1;

select x
from (
  select myfunction(b) as x
  from mytable
  where a = 1
)
where x = 2;

select x
from (
  select /*+ materialize */ myfunction(b) as x
  from mytable
  where a = 1
)
where x = 2;

with t (x) as (
  select myfunction(b)
  from mytable
  where a = 1
)
select x
from t
where x = 2;

优化器正在内部将它们全部重写为同一查询，并且您仍然会获得全部七个函数调用.添加未记录的提示会更改它:

The optimiser is internally rewriting them all into the same query, and you still get all seven function calls. Adding an undocumented hint does change it:

with t (x) as (
  select /*+ materialize */ myfunction(b)
  from mytable
  where a = 1
)
select x
from t
where x = 2;

         X
----------
         2
         2

In function for p=1
In function for p=11
In function for p=21
In function for p=31
In function for p=41

但是您不能(或不应该)真正使用或依赖它.

but you can't (or shouldn't) really use or rely on that.

索引，分区，优化器版本，统计信息等都会影响优化器对您的查询的行为.

The indexing, partitioning, the optimiser version, stats, etc. will all affect how the optimiser behaves for your query.

还有其他需要考虑的事情，您可以使用基于函数的索引或确定性函数...

And as other things to consider, you could have a function-based index, or a deterministic function...

所以...这取决于.

这篇关于sql查询中函数执行的顺序是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！