as you can see my intention was not to store the complete statement in the result cache, but rather the column value selects.
I expect a better reuse that way because we have here in principle design driven sql statements that will be used in probably many selects.
For that reason I indented to map every singular scalar subquery against a result cached function.
Thus we will have the access to the "flex" columns result cached. This is quite a lot of result cache probably, but it is on vogue to use memory nowadays ;-).
Maybe my example does not make this quite explicit.
The effect of OLTP result caching on would be depending on how often exactly this "flex" columns will change.
I agree there is some risk in it, in particular the result cache latch can become an issue.
However it might solve your issue in a quite easy and transparent way. Why not give it a try?
BTW: I did use the function result cache because scalar subquery does not work with the result cache: https://oracle-base.com/articles/11g/query-result-cache-11gr1#scalar-subquery-caching.
On 13.09.2016 17:46, Rich J wrote:
(Sorry for forwarding to the list, Lothar, but my email was rejected by your spam filter!)
On 2016/09/13 09:10, Lothar Flatz wrote:
As I thought. Trying to be flexible, by adding column value rows
rather than extension Tables . Bad Idea, bad design.
How about caching? Result cache is your easiest, but not your only
Interesting! I haven't used result cache in anything production-worthy, but I think its strength is in dealing with relatively static data. This is an OLTP (hybrid) ERP system. The queries I've seen are for a specific document, item, order, etc. e.g.:
SELECT * FROM obnoxious_view WHERE order = 26542;
SELECT * FROM obnoxious_view WHERE order = 54699;
SELECT * FROM obnoxious_view WHERE order = 12811;
Oh how I wish the SELECTs on those views were this simple....
By the nature of the tasks this software is supposed to do, the norm would be that those order numbers in the SELECTs above would rarely be duplicated, which would negate the effectiveness of the result cache.
Thanks for the ideas! I wonder if my time would be better spent trying to explain why the vendor's software leaves a lot to be desired... [sigh]