Oracle 11gR2 – 查看功能列评估
发布时间：2020-12-12 12:44:54 所属栏目：百科 来源：网络整理
导读：对于具有为列定义的函数以及何时评估这些函数的Oracle视图,我似乎有一个奇怪的问题. 假设我有以下视图和函数定义： CREATE OR REPLACE VIEW test_view_one ASSELECT column_one,a_package.function_that_returns_a_value(column_one) function_columnFROM a_
|
对于具有为列定义的函数以及何时评估这些函数的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条记录. 但是,如果我在该内部查询中添加重要的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语句加入的.) 值得注意的是,在我运行解释计划之前收集统计数据以使其尽可能准确. 我对此的理解是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访问该表,即所有记录都是红色的. 这里是案例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的完全扫描)的情况下,调用视图函数 最后的笔记 我也在12.1中对此进行了测试,并在下面看到了列投影的变化. Column Projection Information (identified by operation id):
-----------------------------------------------------------
...
3 - "A"."ID"[NUMBER,22]
5 - "ID"[NUMBER,22]
当然在12c中,可以使用OFFSET的新功能 – FETCH NEXT. 祝好运！ （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |