CODEWITHSUNDEEP
plsql
kirthi kumar
kirthi kumar

Reputation: 9

Oracle PL/SQL result cache / function

I have a set of validation upon metadata tables using SQL queries which are called in a loop, Can i use result cache hint/function to cache the result for all the validations occurred, as sometimes the data validations can have same result. Which one would be better approach? . and also i want to maintain the cache to stay within the session only.

Upvotes: 0

Views: 876

Answers (1)

RGruca
RGruca

Reputation: 204

For result cache, it doesn't matter if sometimes you have the same result. More important is if sometimes you have the same input parameters.

For example if you call your function 1 million times, but every time you call it with different value of input parameter, then it doesn't matter that in result you will have only a few possibilities. In this case result cache doesn't have sense.

Result cache have sense if for many calls we have only a few values of input parameter.

Important is also to check if in your db result cache is enabled

If parameter result_cache_mode is set to MANUAL you can use result_cache hint.

If you need caching in SQL then you can try also Scalar Subquery Caching

Upvotes: 2

Related Questions