Oracle 11gR2 – 查看功能列评估

对于具有为列定义的函数以及何时评估这些函数的Oracle视图,我似乎有一个奇怪的问题.

假设我有以下视图和函数定义：

CREATE OR REPLACE VIEW test_view_one AS
SELECT column_one,a_package.function_that_returns_a_value(column_one) function_column
FROM   a_table;

CREATE OR REPLACE PACKAGE BODY a_package AS 
    FUNCTION function_that_returns_a_value(p_key  VARCHAR2) RETURN VARCHAR2 IS
       CURSOR a_cur IS
          SELECT value
            FROM table_b
           WHERE key = p_key;
      p_temp   VARCHAR2(30);
    BEGIN
    -- Code here to write into a temp table. The function call is autonomous.
      OPEN a_cur;
      FETCH a_cur INTO p_temp;
      CLOSE a_cur;

      RETURN p_temp;
    END function_that_returns_a_value;
END a_package;

通常,我希望如果function_column包含在查询中,那么对于该查询带回的每一行,该函数都将运行.这在某些情况下似乎是正确的,但对其他情况则不然.

例如,假设我有以下内容：

SELECT pageouter,* 
FROM(WITH page_query AS (SELECT * 
                           FROM test_view_one
                         ORDER BY column_one)
SELECT page_query.*,ROWNUM as innerrownum
FROM page_query
WHERE rownum <= 25) pageouter WHERE pageouter.innerrownum >= 1

在这种情况下,该内部查询(查询test_view_one)会带回大约90,000条记录.
如果我将函数定义为插入临时表,那么我可以告诉该函数运行25次,每次返回一次.正是我所期待的.

但是,如果我在该内部查询中添加重要的where子句,例如

SELECT pageouter,* 
  FROM(WITH page_query AS (SELECT * 
                             FROM test_view_one
                            WHERE EXISTS (SELECT 'x' FROM some_table WHERE ...)
                            AND NOT EXISTS (SELECT 'x' FROM some_other_table WHERE ...)
                            AND EXISTS (SELECT 'x' FROM another_table WHERE ...)
                           ORDER BY column_one)
  SELECT page_query.*,ROWNUM as innerrownum
  FROM page_query
  WHERE rownum <= 25) pageouter WHERE pageouter.innerrownum >= 1

然后内部查询返回的行数是60,000,如果我然后查询临时表,我可以告诉该函数已经运行了60,000次.不出所料,这几乎破坏了查询的性能.

上面的查询作为分页实现的一部分运行,这就是为什么我们只返回25行,这就是为什么我们只需要为这25行运行函数的原因.

我应该补充一下,如果我更改WHERE子句(即我删除了一些条件),那么查询将返回到自己的行为,只运行实际带回的25行的函数.

有没有人知道评估视图中的函数的时间？或者无论如何确定导致它的原因或者确定何时评估功能的方法(我已经检查了解释计划,并且那里似乎没有任何东西可以放弃它).如果我知道那么我可以找到问题的解决方案,但似乎除了“他们将为每一行带回来运行”之外的文档很少,这在某些情况下显然不是这种情况.

我完全理解,如果没有工作模式,很难弄清楚正在发生什么,但如果您需要更多信息,请随时询问.

非常感谢

要求的附加信息.

以下是我从生产环境中获得的实际解释计划.表名与上述查询不匹配(实际上涉及的表要多得多,但它们都是由WHERE子句中的NOT EXISTS语句加入的.)
DEMISE表相当于上述查询中的A_TABLE.

值得注意的是,在我运行解释计划之前收集统计数据以使其尽可能准确.

我对此的理解是VIEW行是评估函数的位置,这些函数在行被过滤之后发生.我的理解显然有缺陷！

所以这是一个糟糕的计划,将该功能称为60,000次…

Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------------------------
| Id  | Operation                              | Name        | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |             |     5 | 10230 |   984   (1)|
|   1 |  FAST DUAL                             |             |     1 |       |     2   (0)|
|   2 |  FAST DUAL                             |             |     1 |       |     2   (0)|
|*  3 |  VIEW                                  |             |     5 | 10230 |   984   (1)|
|*  4 |   COUNT STOPKEY                        |             |       |       |            |
|   5 |    VIEW                                |             |     5 | 10165 |   984   (1)|
|*  6 |     SORT ORDER BY STOPKEY              |             |     5 |   340 |   984   (1)|
|   7 |      COUNT                             |             |       |       |            |
|*  8 |       FILTER                           |             |       |       |            |
|*  9 |        HASH JOIN RIGHT OUTER           |             |  5666 |   376K|   767   (1)|
|* 10 |         INDEX RANGE SCAN               | USERDATAI1  |     1 |    12 |     2   (0)|
|* 11 |         HASH JOIN RIGHT ANTI           |             |  5666 |   309K|   765   (1)|
|* 12 |          INDEX FAST FULL SCAN          | TNNTMVINI1  |     1 |    17 |    35   (0)|
|* 13 |          HASH JOIN RIGHT ANTI          |             |  6204 |   236K|   729   (1)|
|* 14 |           INDEX RANGE SCAN             | CODESGENI3  |     1 |    10 |     2   (0)|
|* 15 |           INDEX FULL SCAN              | DEMISEI4    |  6514 |   184K|   727   (1)|
|  16 |            NESTED LOOPS                |             |     1 |    25 |     3   (0)|
|  17 |             NESTED LOOPS               |             |     1 |    25 |     3   (0)|
|* 18 |              INDEX RANGE SCAN          | PROPERTY_GC |     1 |    15 |     2   (0)|
|* 19 |              INDEX UNIQUE SCAN         | CODESGENI1  |     1 |       |     0   (0)|
|* 20 |             TABLE ACCESS BY INDEX ROWID| CODESGEN    |     1 |    10 |     1   (0)|
|  21 |        TABLE ACCESS FULL               | QCDUAL      |     1 |       |     3   (0)|
|* 22 |        INDEX RANGE SCAN                | DMSELEASI4  |     1 |    21 |     2   (0)|
|* 23 |        INDEX RANGE SCAN                | TNNTMVINI1  |     1 |    17 |     1   (0)|
|  24 |        TABLE ACCESS FULL               | QCDUAL      |     1 |       |     3   (0)|
-------------------------------------------------------------------------------------------

这是个好计划.这会将函数调用25次,但是从where子句中删除了一些不存在的语句.

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!