I am reading the Oracle concepts doc and have a doubt [about general cache mechanism ] :

PL/SQL function code can include a request to cache its results. Upon invocation of this function, the system checks the cache. If the cache contains the result from a previous function call with the same parameter values, then the system returns the cached result to the invoker and does not reexecute the function body. If the cache does not contain the result, then the system executes the function body and adds the result (for these parameter values) to the cache before returning control to the invoker

My doubt:

After a PL/SQL function is executed, its result set is stored in cache. Then a DDL statement changes the table/index/anything that the function uses...

If I run the same PL/SQL function now, will I get the same result or updated result?


cached results are invalidated and regenerated when transactions occur against the underlying data

I am fairly confident the same would be true for DDL changes but did you mean DML? The long and short is that Oracle is not going to allow an inconsistent result.

| improve this answer | |
  • 1
    +1 The PL/SQL Language Reference says it this way If changes to any of these data sources are committed, the cached result becomes invalid and must be recomputed. Since DDL does an implicit commit, this includes DDL. – Leigh Riffel Aug 31 '11 at 14:41

"There are only two hard problems in Computer Science: cache invalidation and naming things." -- Phil Karlton

Part of Oracle's built-in functionality is Data Change Notification. This mechanism is generally used for cache invalidation in all tiers, for example, if you cache a query result on the client side, DCN ensures that you are always routed to the closest but most recent copy.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.