gpt4 book ai didi

oracle - 为什么 Oracle 12.1.0.2 跳过结果缓存表上的函数调用?

转载 作者:行者123 更新时间:2023-12-04 08:56:28 25 4
gpt4 key购买 nike

我将现实生活中的问题简化为以下测试用例:

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 INTO f, 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 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 INTO f, 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, 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 INTO f, 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 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 INTO f, last_name FROM test_users WHERE user_id = l_user_id;
dbms_output.put_line(f || ' ' || last_name);

END;
/

索引可能是也可能不是必要的。这里的想法是当前用户的名称在 session 标识符中。测试函数将 session 标识符中的用户名转换为用户 ID。用户名(理论上)可以更改,并用作登录名。用户 ID 不应更改,因此是表的 PK。

困扰我的是,当打开结果缓存时,该语句的 WHERE 条件中的函数并不总是被调用:
SELECT first_name, last_name INTO f, 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 条件中使用它,一切都很好。

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

编辑:
在阅读了一些答案后,我尝试明确地将该函数声明为结果缓存,如下所示:
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 文档中的示例非常相似。

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

最佳答案

问题不在于您的 TEST_USERS 上的缓存 table 。问题是你的TEST_FUNCTION的结果函数正在缓存,更改 session 标识符不会使这些结果无效。

为了避免这个问题,首先改变TEST_FUNCTION的定义。对此:

CREATE OR REPLACE FUNCTION test_function ( identifier VARCHAR2 DEFAULT sys_context('userenv', 'client_identifier') ) RETURN test_users.user_id%TYPE IS
--identifier VARCHAR2(32);
user_id test_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;

然后,当您在 WHERE 中使用它时条款,这样称呼它:
SELECT first_name, last_name 
INTO f, last_name
FROM test_users WHERE user_id = test_function;

重要提示:注意我没有使用 test_function() (即,注意没有括号)。

为什么括号很重要?我不知道。我认为他们不应该。但这适用于我的 12.1.0.2 实例。

运行您发布的测试用例的变体,我在最后得到以下结果:
function call in WHERE criteria with no parens...: Dopey Simba
function call in WHERE criteria with parens...: Sneezy Timon

关于oracle - 为什么 Oracle 12.1.0.2 跳过结果缓存表上的函数调用?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50136984/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com