让oracle12c不使用缓存,为什么Oracle 12.1.0.2会跳过结果缓存表上的函数调用？

我将现实生活中的问题减少到以下测试用例：

DROP TABLE test_users;

CREATE TABLE test_users (

user_id INTEGER,username VARCHAR2(32),first_name VARCHAR2(40),last_name VARCHAR2(40)

);

ALTER TABLE test_users ADD

(

CONSTRAINT test_users_pk

PRIMARY KEY (user_id)

USING INDEX

)

/

ALTER TABLE test_users ADD

(

CONSTRAINT test_users_uq

UNIQUE (username)

USING INDEX

)

/

INSERT INTO test_users VALUES (1,'A','Sneezy','Timon');

INSERT INTO test_users VALUES (2,'B','Dopey','Simba');

INSERT INTO test_users VALUES (3,'C','Happy','Nala');

INSERT INTO test_users VALUES (4,'D','Grumpy','Pumbaa');

COMMIT;

CREATE OR REPLACE FUNCTION test_function RETURN test_users.user_id%TYPE IS

identifier VARCHAR2(32);

user_id users.user_id%TYPE;

BEGIN

SELECT sys_context('userenv','client_identifier') INTO identifier FROM dual;

SELECT user_id INTO user_id FROM test_users WHERE upper(username) = upper(identifier);

dbms_output.put_line('TEST_FUNCTION called!');

RETURN user_id;

END test_function;

-- Testing with disabled result cache

ALTER TABLE test_users RESULT_CACHE (MODE DEFAULT);

DECLARE

f users.first_name%TYPE;

last_name users.last_name%TYPE;

identifier VARCHAR2(32);

l_user_id users.user_id%type;

BEGIN

dbms_output.put_line('setting the session identifier to A (Sneezy,Timon):');

dbms_session.set_identifier('A');

l_user_id := test_function();

dbms_output.put_line('function call in WHERE criteria:');

SELECT first_name,last_name INTO f,last_name FROM test_users WHERE user_id = test_function();

dbms_output.put_line(f || ' ' || last_name);

dbms_output.put_line('variable use in WHERE criteria:');

SELECT first_name,last_name FROM test_users WHERE user_id = l_user_id;

dbms_output.put_line(f || ' ' || last_name);

dbms_output.put_line('----');

dbms_output.put_line('setting the session identifier to B (Dopey Simba):');

dbms_session.set_identifier('B');

l_user_id := test_function();

dbms_output.put_line('function call in WHERE criteria:');

SELECT first_name,last_name FROM test_users WHERE user_id = l_user_id;

dbms_output.put_line(f || ' ' || last_name);

END;

/

-- Testing with enabled result cache

ALTER TABLE test_users RESULT_CACHE (MODE FORCE);

DECLARE

f users.first_name%TYPE;

last_name users.last_name%TYPE;

identifier VARCHAR2(32);

l_user_id users.user_id%type;

BEGIN

dbms_output.put_line('setting the session identifier to A (Sneezy,last_name FROM test_users WHERE user_id = l_user_id;

dbms_output.put_line(f || ' ' || last_name);

END;

/

索引可能是必要的,也可能不是必需的.这里的想法是当前用户的名字在会话标识符中.测试功能将会话标识符中的用户名转换为用户ID.用户名可以(理论上)更改,并用作登录名.用户ID永远不会改变,因此是表的PK.

困扰我的是,当打开结果缓存时,并不总是调用此语句的WHERE条件中的函数：

SELECT first_name,last_name FROM test_users WHERE user_id = test_function();

第一个PL / sql块产生以下结果：

setting the session identifier to A (Sneezy,Timon):

TEST_FUNCTION called!

function call in WHERE criteria:

TEST_FUNCTION called!

Sneezy Timon

variable use in WHERE criteria:

Sneezy Timon

----

setting the session identifier to B (Dopey Simba):

TEST_FUNCTION called!

function call in WHERE criteria:

TEST_FUNCTION called!

Dopey Simba

variable use in WHERE criteria:

Dopey Simba

第二个块产生这个：

setting the session identifier to A (Sneezy,Timon):

TEST_FUNCTION called!

function call in WHERE criteria:

TEST_FUNCTION called!

Sneezy Timon

variable use in WHERE criteria:

Sneezy Timon

----

setting the session identifier to B (Dopey Simba):

TEST_FUNCTION called!

function call in WHERE criteria:

Sneezy Timon

variable use in WHERE criteria:

Dopey Simba

如您所见,TEST_FUNCTION的调用次数减少,结果错误.我理解结果缓存的方式,用户表应该是一个完美的候选人.许多SELECT,很少DML.除非我将函数调用放在WHERE标准中,否则一切正常.如果我调用该函数,将结果保存在变量中并在WHERE标准中使用它,一切都很好.

这是为什么？这是一个错误还是一个功能？事实上该函数使用来自会话标识符的数据是主要问题吗？或者通常不会为整个表打开结果缓存？

编辑：

在阅读了一些答案之后,我尝试明确地将该函数声明为缓存结果,如下所示：

CREATE OR REPLACE FUNCTION test_function(identifier VARCHAR2 DEFAULT sys_context('userenv','client_identifier'))

RETURN test_users.user_id%TYPE result_cache relies_on(test_users) IS

user_id test_users.user_id%TYPE;

BEGIN

SELECT user_id INTO user_id FROM test_users WHERE upper(username) = upper(identifier);

dbms_output.put_line('TEST_FUNCTION called!');

RETURN user_id;

END test_function;

这与下面评论中的Oracle文档示例非常相似.

可悲的是,这没有帮助.使用或不使用parantheses调用函数对我没有任何影响(但请参阅下面的评论).我发现始终获得预期结果的唯一方法是禁用表的结果缓存.