Oracle 11gR2 - 查看函数列评估

Question

我似乎有一个关于 Oracle View 的奇怪问题，该 View 具有为列定义的函数以及何时评估这些函数。

假设我有以下 View 和函数定义:

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 行的函数。

有没有人知道何时评估 View 中的函数？或者无论如何确定导致它的原因或确定何时评估函数的方法(我已经检查了解释计划并且那里似乎没有任何东西可以放弃它)。如果我知道这一点，那么我希望能找到解决问题的方法，但除了“他们会为带回的每一行运行”之外，似乎几乎没有文档，这在某些情况下显然并非如此。

我完全理解在没有工作模式的情况下很难弄清楚发生了什么，但是如果您需要更多信息，请随时询问。

非常感谢

根据要求提供附加信息。

下面是我从生产环境中得到的实际解释计划。表名与上述查询不匹配(实际上涉及的表要多得多，但它们都由 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 子句中删除了一些不存在的语句。

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

----------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |    25 | 54200 |   144   (0)|
|   1 |  FAST DUAL                           |            |     1 |       |     2   (0)|
|   2 |  FAST DUAL                           |            |     1 |       |     2   (0)|
|*  3 |  VIEW                                |            |    25 | 54200 |   144   (0)|
|*  4 |   COUNT STOPKEY                      |            |       |       |            |
|   5 |    VIEW                              |            |    26 | 56030 |   144   (0)|
|   6 |     COUNT                            |            |       |       |            |
|*  7 |      FILTER                          |            |       |       |            |
|   8 |       NESTED LOOPS ANTI              |            |    30 |  3210 |   144   (0)|
|   9 |        NESTED LOOPS OUTER            |            |    30 |  2580 |   114   (0)|
|  10 |         NESTED LOOPS ANTI            |            |    30 |  2220 |    84   (0)|
|  11 |          NESTED LOOPS ANTI           |            |    32 |  1824 |    52   (0)|
|  12 |           TABLE ACCESS BY INDEX ROWID| DEMISE     |   130K|  5979K|    18   (0)|
|  13 |            INDEX FULL SCAN           | DEMISEI4   |    34 |       |     3   (0)|
|* 14 |           INDEX RANGE SCAN           | CODESGENI3 |     1 |    10 |     1   (0)|
|* 15 |          INDEX RANGE SCAN            | TNNTMVINI1 |     1 |    17 |     1   (0)|
|* 16 |         INDEX RANGE SCAN             | USERDATAI1 |     1 |    12 |     1   (0)|
|* 17 |        INDEX RANGE SCAN              | DMSELEASI4 |     1 |    21 |     1   (0)|
|  18 |       TABLE ACCESS FULL              | QCDUAL     |     1 |       |     3   (0)|
----------------------------------------------------------------------------------------

我完全理解第二个计划做得更少，但这并不能解释为什么没有评估这些功能......至少不是我可以解决的。

Answer 1

Pagination with ROWNUM可以执行
有两种方式:

A) 使用优化排序全扫描行源(仅限于前 N 行)或

B)行源的索引访问，根本没有排序

这里是 的简化示例案例 A

 SELECT *
 FROM
   (SELECT a.*,
     ROWNUM rnum
   FROM
     ( SELECT * FROM test_view_one ORDER BY id
     ) a
   WHERE ROWNUM <= 25
   )
 WHERE rnum >= 1

对应的执行计划如下(注意我还预了部分
列投影 - 我很快会解释原因):

 -----------------------------------------------------------------------------------------
 | Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
 -----------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT         |      |    25 |   975 |       |  1034   (1)| 00:00:01 |
 |*  1 |  VIEW                    |      |    25 |   975 |       |  1034   (1)| 00:00:01 |
 |*  2 |   COUNT STOPKEY          |      |       |       |       |            |          |
 |   3 |    VIEW                  |      | 90000 |  2285K|       |  1034   (1)| 00:00:01 |
 |*  4 |     SORT ORDER BY STOPKEY|      | 90000 |   439K|  1072K|  1034   (1)| 00:00:01 |
 |   5 |      TABLE ACCESS FULL   | TEST | 90000 |   439K|       |   756   (1)| 00:00:01 |
 -----------------------------------------------------------------------------------------


 Column Projection Information (identified by operation id):
 -----------------------------------------------------------
 ... 
    3 - "A"."ID"[NUMBER,22], "A"."FUNCTION_COLUMN"[NUMBER,22]
    4 - (#keys=1) "ID"[NUMBER,22], "MY_PACKAGE"."MY_FUNCTION"("ID")[22]
    5 - "ID"[NUMBER,22]     

在执行过程中，使用 FULL SCAN 访问该表，即所有记录都是红色的。
优化在 SORT 操作中: 按STOPKEY排序 意味着并非所有
行已排序，但仅保留前 25 行并进行排序。

这里是 的执行计划案例B

 --------------------------------------------------------------------------------
 | Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT    |          |    25 |   975 |     2   (0)| 00:00:01 |
 |*  1 |  VIEW               |          |    25 |   975 |     2   (0)| 00:00:01 |
 |*  2 |   COUNT STOPKEY     |          |       |       |            |          |
 |   3 |    VIEW             |          |    26 |   676 |     2   (0)| 00:00:01 |
 |*  4 |     INDEX RANGE SCAN| TEST_IDX |    26 |   130 |     2   (0)| 00:00:01 |
 --------------------------------------------------------------------------------

这里只访问了所需的 25 行，因此该函数的调用次数不能超过 N 次。

现在重要的考虑， 在情况 A 中，可以但不需要为每一行调用该函数 .我们怎么看？

答案在解释计划中的列投影中。

    4 - (#keys=1) "ID"[NUMBER,22], "MY_PACKAGE"."MY_FUNCTION"("ID")[22]

相关的第 4 行显示，该函数在 SORT 操作中被调用，因此对于每一行。 (排序获取所有行)。

结论

我在 11.2 上的测试表明，如果 A(使用 SORT ORDER BY STOPKEY 进行全扫描)调用了 View 函数
每行一次。
我想唯一的解决方法是只选择 ID，限制结果，然后连接回原始 View 以获取函数值。

最后说明

我在 中对此进行了测试12.1 同样，请参阅下面的列投影偏移。
该函数首先在 VIEW(第 3 行)中计算，即两种情况都可以正常工作。

 Column Projection Information (identified by operation id):
 -----------------------------------------------------------
 ...
    3 - "A"."ID"[NUMBER,22], "A"."FUNCTION_COLUMN"[NUMBER,22]
    4 - (#keys=1) "ID"[NUMBER,22]
    5 - "ID"[NUMBER,22]        

当然，在 12c 中可以使用 OFFSET - FETCH NEXT 的新功能。

祝你好运!