Empty RELIES_ON for RESULT_CACHE

Question

I have a query inside the function with RESULT_CACHE.

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

What I want is to implement the function that depends only on input parameters, and doesn't depend on any implicit dependencies (like tables, etc).

Is it possible (without dynamic sql)?

Answer 1

a function that depends only on its parameters can be declared DETERMINISTIC. The results of this function will be cached in some cases. This thread on the OTN forums shows how deterministic function results get cached inside SQL statements.

As of 10gR2, the function results don't get cached across SQL statements nor do they get cached in PL/SQL. Still, this cache feature can be useful if you call a function in a SELECT where it might get called lots of time.

I don't have a 11gR2 instance available right now, so I can't test the RESULT_CACHE feature, but have you considered delaring your function relying on a fixed dummy table (a table that never gets updated for instance)?

Answer 2

The correct answer is NO. A solution in cases where things like result caches and materialized views won't work because of invalidations or too much overhead is the Oracle In-Memory Database Cache option. See result caches ..... what about heavily modified data It's a real smart option, not cheap.