Oracle WITH和MATERIALIZE提示充当功能的自治事务 [英] Oracle WITH and MATERIALIZE hint acts as autonomous transaction for functions

问题描述

在Oracle 12c中，如果我在WITH..AS节中使用MATERIALIZE提示的查询中调用函数，那么函数调用就像是一个自治事务：

  DROP TABLE my_table; 
 
 CREATE TABLE my_table（
 my_column NUMBER 
）; 
 
  - 返回表中的记录数
创建或替换函数my_function 
 RETURN INTEGER 
 IS 
 INTEGER; 
 BEGIN 
 SELECT COUNT（1）IN my_ my_table; 
 RETURN i; 
 END; 
 / 
 
  - 在表格中插入一条记录
 INSERT INTO my_table（my_column）VALUES（9）; 
 
  - 返回表中的记录数。这工作正常，返回1 
 SELECT COUNT（1）ASuse simple selectFROM my_table; 
 
  - 返回表中的记录数。此工作正常，返回1 
 WITH x AS（
 SELECT / * + MATERIALIZE * / COUNT（1）AS使用WITH，MATERIALIZEFROM my_table 
）
 SELECT * FROM X; 
 
  - 返回表中的记录数。这工作正常，返回1 
 SELECT my_function ASuse FUNCTIONFROM dual; 
 
  - 返回表中的记录数。这工作不正确，返回0. 
  - 在自治事务中调用函数？ 
 WITH x AS（
 SELECT / * + MATERIALIZE * / my_function使用WITH，MATERIALIZE，FUNCTIONFROM dual 
）
 SELECT * FROM x; 
 
 ROLLBACK; 
  

有谁知道这是什么原因？它是一个Oracle错误还是它打算像这样工作？ （为什么？）
为什么只有当WITH与MATERIALIZED hint和FUNCTION调用结合使用时，它才能工作？

解决方案

这看起来像bug 15889476，光标持续时间表和函数在活动事务上运行错误的结果;和13253977错误的结果或错误与光标持续时间表和PLSQL函数在活动事务上运行。

我可以在11.2.0.3而不是11.2.0.4 ;从Husqvik的评论看来，它似乎没有在12.1.0.2上重现。这与bug文档中的受影响版本和修复优先包含信息是一致的。

有关更多信息，请参阅MOS文档15889476.8和13253977.8。您可能需要联系Oracle支持以确认这是您所看到的问题，但看起来非常相似。

In Oracle 12c if I call a function in a query that uses MATERIALIZE hint in a WITH..AS section, the function call acts like an autonomous transaction:

DROP TABLE my_table;

CREATE TABLE my_table (
    my_column NUMBER
);

-- Returns number of records in table 
CREATE OR REPLACE FUNCTION my_function 
RETURN INTEGER
IS
    i INTEGER;
BEGIN
    SELECT COUNT(1) INTO i FROM my_table;
    RETURN i;
END;
/

-- Inserts one record to table 
INSERT INTO my_table (my_column) VALUES (9);

-- Returns number of records in table. This works correctly, returns 1
SELECT COUNT(1) AS "use simple select" FROM my_table;

-- Returns number of records in table. This works correctly, returns 1
WITH x AS (
    SELECT /*+ MATERIALIZE */ COUNT(1) AS "use WITH, MATERIALIZE" FROM my_table
)
SELECT * FROM x;

-- Returns number of records in table. This works correctly, returns 1
SELECT my_function AS "use FUNCTION" FROM dual;

-- Returns number of records in table. This works INCORRECTLY, returns 0. 
-- Function is called in autonomous transaction?
WITH x AS (
    SELECT /*+ MATERIALIZE */ my_function "use WITH,MATERIALIZE,FUNCTION" FROM dual
)
SELECT * FROM x;

ROLLBACK;

Does anyone know what is the reason for this? Is it an Oracle bug or it is intended to work like this? (Why?) Why it works like this only when WITH is combined with MATERIALIZED hint and FUNCTION call?

解决方案

This looks like bug 15889476, "Wrong results with cursor-duration temp table and function running on an active transaction"; and 13253977 "Wrong results or error with cursor-duration temp table and PLSQL function running on an active transaction".

I can reproduce on 11.2.0.3 but not 11.2.0.4; and from Husqvik's comment it doesn't seem to reproduce on 12.1.0.2. That aligns with the affected version and fix-first-included-in information in the bug documents.

See MOS documents 15889476.8 and 13253977.8 for more information. You may need to contact Oracle Support to confirm this is the issue you are seeing, but it looks pretty similar.

这篇关于Oracle WITH和MATERIALIZE提示充当功能的自治事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！