对于具有为列定义的函数以及何时评估这些函数的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次,每次返回一次.正是我所期待的.
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子句中删除了一些不存在的语句.
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)| ----------------------------------------------------------------------------------------
解决方法
可以执行
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,"MY_PACKAGE"."MY_FUNCTION"("ID")[22] 5 - "ID"[NUMBER,22]
在执行过程中,使用FULL SCAN访问该表,即所有记录都是红色的.
优化在SORT操作中：SORT ORDER BY STOPKEY表示不是全部
对行进行排序,但只保留前25个行和sortet.
这里是案例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,"MY_PACKAGE"."MY_FUNCTION"("ID")[22]
相关的第4行显示,在SORT操作中调用该函数,因此每行都调用该函数. (排序获取所有行).
结论
我在11.2上的测试显示,在A(具有SORT ORDER BY STOPKEY的完全扫描)的情况下,调用视图函数
每排一次.
我想唯一的解决方法是只选择ID,限制结果,然后加入原始视图以获取函数值.
最后的笔记
我也在12.1中对此进行了测试,并在下面看到了列投影的变化.
该函数首先在VIEW(第3行)中计算,即两种情况都能正常工作.
Column Projection Information (identified by operation id):
-----------------------------------------------------------
...
3 - "A"."ID"[NUMBER,22]
5 - "ID"[NUMBER,22]
当然在12c中,可以使用OFFSET的新功能 – FETCH NEXT.
祝好运！
