Oracle 有没有一种方法可以避免在更新父表时导致result_cache失效，因为我在缓存表中有FK

嗨，汤姆!

我有两个表T1和T2和一个从T2到T1的FK，删除时 “无操作”。我使用缓存函数 (RESULT_CACHE) 从t2返回记录。一切正常，直到T1发生一些变化。即使此更改不影响T2 (与此FK相关的T2中没有发生任何更改)，缓存也无效。如果我禁用FK-问题解决，但不幸的是我们需要它。

我通过互联网进行了搜索，发现了几个有类似问题的人的问题，但没有答案/解决方案。也许你可以建议一个？

致以最诚挚的问候，
阿列克谢·鲁班。

没有测试用例 .... 叹息。所以我只能说...“在我的机器上工作”

SQL> create table t1 as select * from scott.dept;

Table created.

SQL> alter table t1 add primary key (deptno);

Table altered.

SQL>
SQL> create table t2 as select * from scott.emp;

Table created.

SQL> alter table t2 add primary key (empno);

Table altered.

SQL> alter table t2 add constraint t2fk foreign key ( deptno) references t1 ( deptno );

Table altered.

SQL>
SQL> set autotrace on stat
SQL> select /*+ result_cache */ job, count(*)
  2  from t2
  3  group by job;

JOB         COUNT(*)
--------- ----------
CLERK              4
SALESMAN           4
PRESIDENT          1
MANAGER            3
ANALYST            2


Statistics
----------------------------------------------------------
         26  recursive calls
          8  db block gets
         33  consistent gets
          0  physical reads
       1040  redo size
        726  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL>
SQL> select /*+ result_cache */ job, count(*)
  2  from t2
  3  group by job;

JOB         COUNT(*)
--------- ----------
CLERK              4
SALESMAN           4
PRESIDENT          1
MANAGER            3
ANALYST            2


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        726  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL>
SQL> update t1 set dname = 'X' where rownum = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL>
SQL> select /*+ result_cache */ job, count(*)
  2  from t2
  3  group by job;

JOB         COUNT(*)
--------- ----------
CLERK              4
SALESMAN           4
PRESIDENT          1
MANAGER            3
ANALYST            2


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        726  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed