Oracle 如何处理 SQL 中的存储函数调用？

Question

伙计们。假设我有一个查询：

select t.value, my_stored_function(t.value)
  from my_table t
 where my_stored_function(t.value) = n_Some_Required_Value

我用以下方式重写了它：

select value, func_value
  from (select t.value, my_stored_function(t.value) func_value 
          from my_table t) subquery
 where subquery.func_value = n_Some_Required_Value

让我们将 my_stored_function 视为资源消耗型查询。我假设，在第二个查询中，它被调用的次数减少了两次，但在此更改之后我没有遇到任何显着的性能提升。

所以，我想，我的假设是错误的。那么Oracle实际上是如何处理这些函数调用的呢？

Answer 1

这是一个非常好的问题。

我首先尝试创建表并插入示例数据（仅五行）：

create table my_table(value number);
insert into my_table(value) values(1);
insert into my_table(value) values(2);
insert into my_table(value) values(3);
insert into my_table(value) values(4);
insert into my_table(value) values(5);

我制作了一个简单的测试包来测试这一点。

create or replace package my_package is
  g_counter_SELECT PLS_INTEGER := 0; -- counter for SELECT statement
  g_counter_WHERE  PLS_INTEGER := 0; -- counter for WHERE clause
  function my_function(number_in in number, type_in in varchar2) return number;
  procedure reset_counter;
end;
/

现在

create or replace package body my_package is
  function my_function(number_in in number, type_in in varchar2) return number is
  begin
    IF(type_in = 'SELECT') THEN
        g_counter_SELECT := g_counter_SELECT + 1;
    ELSIF(type_in = 'WHERE') THEN
        g_counter_WHERE := g_counter_WHERE + 1;
    END IF;
    return mod(number_in, 2);
  end;
  procedure reset_counter is
  begin
    g_counter_SELECT := 0;
    g_counter_WHERE := 0;
  end;
end;
/

，我们可以在 Oracle 9i 上运行测试（在 11g 上结果相同）：

-- reset counter
exec my_package.reset_counter();

-- run query
select t.value, my_package.my_function(t.value, 'SELECT')
  from my_table t
 where my_package.my_function(t.value, 'WHERE') = 1;

-- print result
exec dbms_output.put_line('Count (SELECT) = ' || my_package.g_counter_SELECT);
exec dbms_output.put_line('Count (WHERE) = ' || my_package.g_counter_WHERE);

结果是：

DBMS Output (Session: [1] SCOTT@ORA9i at: 08.09.2010 01:50:04): 
-----------------------------------------------------------------------
Count (SELECT) = 3
Count (WHERE) = 5

这是计划表：

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | MY_TABLE    |       |       |       |
--------------------------------------------------------------------

这意味着为表的每一行（在 WHERE 计算中）调用该函数（在 WHERE 计算中）全表扫描的情况）。在 SELECT 语句中，启动的次数与条件 WHERE my_function = 1

现在...测试您的第二个查询（Oracle9i 和 11g 上的结果相同）

结果是：

DBMS Output (Session: [1] SCOTT@ORA9i at: 08.09.2010 02:08:04): 
-----------------------------------------------------------------------
Count (SELECT) = 8
Count (WHERE) = 0

简单地解释如下（对于选择优化器模式）：

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | MY_TABLE    |       |       |       |
--------------------------------------------------------------------

问题是：为什么 Count (SELECT) = 8？

因为 Oracle 首先运行子查询（在我使用全表扫描的情况下，它是 5 行 = 5 在 SELECT 语句中调用 my_function）：

select t.value, my_package.my_function(t.value, 'SELECT') func_value from my_table t

并且比这个视图（子查询就像视图）运行3次（由于subquery.func_value = 1的条件）再次调用函数my_function。

个人不建议在WHERE子句中使用函数，但我承认有时这是不可避免的。

最糟糕的示例如下所示：

select t.value, my_package.my_function(t.value, 'SELECT')
  from my_table t
 where my_package.my_function(t.value, 'WHERE') = my_package.my_function(t.value, 'WHERE')
   and my_package.my_function(t.value, 'WHERE') = my_package.my_function(t.value, 'WHERE')
   and my_package.my_function(t.value, 'WHERE') = my_package.my_function(t.value, 'WHERE')
   and my_package.my_function(t.value, 'WHERE') = my_package.my_function(t.value, 'WHERE')
   and my_package.my_function(t.value, 'WHERE') = my_package.my_function(t.value, 'WHERE');

Oracle 9i 上的结果是：

Count (SELECT) = 5
Count (WHERE) = 50

Oracle 11g 上的结果是：

Count (SELECT) = 5
Count (WHERE) = 5

在本例中，这表明有时使用功能可能对性能至关重要。在其他情况下（11g），它解决数据库本身的问题。

Answer 2

在这两种情况下，都会为 my_table 中的每一行调用该函数一次。在第一种情况下，调用将作为 where 子句的结果，并且刚刚找到的值将被返回，而无需再次计算。在第二种情况下，所有计算值将从子查询返回，然后由外部查询的 where 子句进行过滤。

编辑： 显然不是基于 true马丁的测试。现在我必须回去找到几年前让我认为情况确实如此的测试，看看我做错了什么。关于联邦调查局的那句话仍然是正确的。我希望。

内存使用情况可能存在一些细微的差异，优化器可能使用的确切计划也可能存在一些细微的差异，但我认为两者都不会很重要。几乎可以肯定不会反对函数调用本身的成本。

我认为优化这一点的唯一方法是使用基于函数的索引。

Answer 3

一个简单的测试：

create or replace function print_function(v1 number) return number is
begin
   dbms_output.put_line(v1);
   return v1;
end;
/

select print_function(ASCII(dummy)) as test
  from dual
 where chr(print_function(ASCII(dummy))) = dummy;

结果（使用10g）：

      TEST
----------
        88

88
88

结论：该函数在SELECT和WHERE子句中分别执行。

Answer 4

您可以使用 PL/SQL 编译指示来影响 Oracle 优化查询的方式，请参阅 RESTRICT_REFERENCES 指令