Regarding the Query Result Cache hint: /*+ result_cache */.
Oracle 11g allows the results of SQL queries to be cached in the SGA and reused to improve performance.
I think I understand what that mechanism does. But I'm less sure about when it should be used.
Here's my uneducated guess...we can/should use the Query Result Cache when:
The query is used often.
The underlying data is not edited often.
We have a reason why we don't want to use the usual techniques for pre-computing results: materialized views, a trigger to populate a separate table, a scheduled job, etc.
A possible reason might be: the requirement is temporary and we want a quick solution to improve performance.
Or, we are reluctant to set up a custom mechanism like a trigger or a scheduled job, since that would require custom code to manage. And we don't want to manage additional tables or MVs if we can help it, since our system is already complicated enough.
In my case, I'm prototyping different solutions, and I'm not sure what solution we'll end up keeping, if any. I don't want to go to the trouble setting up a materialized view, a trigger to populate a separate table, or a scheduled job. At least not right now. If we end up keeping the solution, then we can migrate it to a more permanent mechanism if needed.
For example, this query extracts the startpoint from sidewalk geomety lines (14,000 rows; 20 seconds to execute — due to the slow OOTB functions: https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/a-quick-tour-of-sql-functions-used-with-st-geometry.htm).
The query would be used often.
The underlying data is not edited often.
It's a temporary solution. And the Query Result Cache is extremely easy to implement: just add the hint to the SQL.
--get sde.st_geometry line startpoint
select /*+ result_cache */
objectid,
sde.st_x(sde.st_startpoint(shape)) as startpoint_x,
sde.st_y(sde.st_startpoint(shape)) as startpoint_y
from
sidewalks --initial load takes 20 seconds due to slow st_geometry functions
The Query Result Cache hint seems to work well in that scenario. We can deal with the query being slow once and while — while it's re-computing the results due to a edit to the data (edits happen about once per week).
With that said, I'm a novice, and I've just made up the "intended use case" above.
What's the real intended use case for the Query Result Cache hint?
Thanks.