Oracle 如何处理 SQL 中存储的函数调用? [英] How does Oracle process stored function calls in SQL?

问题描述

伙计们.说，我有一个查询:

guys. Say, I have a query:

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

我用以下方式重写了它:

I have rewritten it in the following way:

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 视为消耗资源的函数.我假设，在第二个查询中，它的调用次数减少了两次，但在此更改后我没有遇到任何显着的性能提升.

Let's think of my_stored_function as of resource-consuming one. I assume, in the second query it is called twice less, but I didn't experience any significant performance increase after this change.

所以，我想，我的假设是错误的.那么 Oracle 是如何处理这些函数调用的呢?

So, I guess, my assumption was wrong. How does Oracle actually process these function calls then?

推荐答案

这是一个很好的问题.

我首先尝试创建表并插入示例数据(仅五行):

I first tried create table and insert sample data (five rows only):

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

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

I made a simple test package for testing this.

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 上结果相同):

Now, we can run test on Oracle 9i (on 11g are same results):

-- 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 calues 中)(在 FULL TABLE SCAN 的情况下).在 SELECT 语句启动时，只要符合条件 WHERE my_function = 1

Which means that the function (in WHERE calues) is called for every row of the table (in the case of FULL TABLE SCAN). In the SELECT statement is launched just as many times comply with condition WHERE my_function = 1

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

Now... test your second query (same results on Oracle9i and 11g)

结果是:

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

像这样解释简单的外观(对于选择优化器模式):

Explain plain look like this (for CHOOSE optimizer mode):

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

问题是:为什么计数 (SELECT) = 8?

因为 Oracle 首先运行子查询(在我使用 FULL TABLE SCAN 的情况下，它是 5 行 = 5 次在 SELECT 语句中调用 my_function):

Because Oracle first run subquery (in my case with FULL TABLE SCAN, it's 5 rows = 5 calls my_function in SELECT statement):

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

对于这个视图(子查询就像视图)运行 3 次(由于 subquery.func_value = 1 的条件)再次调用函数 my_function.

And than for this view (subquery is like view) run 3 times (due to the condition where subquery.func_value = 1) again call function my_function.

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

Personally not recommend to use function in the WHERE clause, but I admit that sometimes this is unavoidable.

最坏的例子如下所示:

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)它解决了数据库本身.

Which in this case shows that sometimes the use of functions may be critical for performance. In other cases (11g) it solves the database itself.

这篇关于Oracle 如何处理 SQL 中存储的函数调用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！