Empty RELIES_ON for RESULT_CACHE

Question

I have a query inside a function with RESULT_CACHE.

So, when the table is changed - my cache is invalid and the function is executed again.

I want to implement a function that depends only on the input parameters and is not dependent on any implicit dependencies (e.g. tables, etc.).

Is this possible (without dynamic sql)?

Answer 1

, , ​​DETERMINISTIC. . OTN , SQL.

10gR2 SQL- PL/SQL. , , SELECT, .

11gR2, , RESULT_CACHE, , (, , )?

Answer 2

. , , , - , Oracle Database Cache. . ..... , .

Answer 3

, , , .

, ; ( ), , , ..

, RELIES_ON 11gR2. , SQL . , , .

script, . "@myself" , , . .

--For testing, create a package that will hold a counter.
create or replace package counter is
    procedure reset;
    procedure increment;
    function get_counter return number;
end;
/

create or replace package body counter as
    v_counter number := 0;
    procedure reset is begin v_counter := 0; end;
    procedure increment is begin v_counter := v_counter + 1; end;
    function get_counter return number is begin return v_counter; end;
end;
/

--Create database link
create database link myself connect to <username> identified by "<password>"
using '<connect string>';

drop table test purge;
create table test(a number primary key, b varchar2(100));
insert into test values(1, 'old value1');
insert into test values(2, 'old value2');
commit;

--Cached function that references a table and keeps track of the number of executions.
drop function test_cache;
create or replace function test_cache(p_a number) return varchar2 result_cache is
    v_result varchar2(100);
begin
    counter.increment;
    select b into v_result from test@myself where a = p_a;
    return v_result;
end;
/

--Reset
begin
    counter.reset;
end;
/

--Start with 0 calls
select counter.get_counter from dual;

--First result is "value 1", is only called once no matter how many times it runs.
select test_cache(1) from dual;
select test_cache(1) from dual;
select test_cache(1) from dual;
select counter.get_counter from dual;

--Call for another parameter, counter only increments by 1.
select test_cache(2) from dual;
select test_cache(2) from dual;
select test_cache(2) from dual;
select counter.get_counter from dual;

--Now change the table.  This normally would invalidate the cache.
update test set b = 'new value1' where a = 1;
update test set b = 'new value2' where a = 2;
commit;

--Table was changed, but old values are still used.  Counter was not incremented.
select test_cache(1) from dual;
select test_cache(2) from dual;
select counter.get_counter from dual;

--The function is not dependent on the table.
SELECT ro.id           AS result_cache_id
,      ro.name         AS result_name
,      do.object_name
FROM   v$result_cache_objects    ro
,      v$result_cache_dependency rd
,      dba_objects               do
WHERE  ro.id = rd.result_id
AND    rd.object_no = do.object_id;

Answer 4

:

• - .

• - PL/SQL . , . .