pl/sql中实现缓存层的解决方案
我有一个带有 1 个参数（日期）的函数，它封装了 1 个查询，例如
SELECT COUNT(*)
FROM tbl
WHERE some_date_field BETWEEN param_date - INTERVAL '0 1:00:00' DAY TO SECOND
AND param_date
我想要做的是将 ttl = 1 分钟的查询结果缓存在某处。缓存的结果应该在所有会话中共享，而不仅仅是当前会话。
有什么建议吗？
PS：是的，我知道oracle函数结果缓存，但它不符合要求。
PPS：是的，我们可以使用一些值创建第二个人工参数，例如日期格式为yyyymmddhh24mi，这样它每分钟都会改变，我们可以使用函数结果缓存，但我希望这是一个解决方案将允许我隐藏里面的缓存依赖项。
I have a function with 1 argument (date) which encapsulates 1 query like
SELECT COUNT(*)
FROM tbl
WHERE some_date_field BETWEEN param_date - INTERVAL '0 1:00:00' DAY TO SECOND
AND param_date
What I want to do is to cache somewhere the result of this query with ttl = 1 minute. The cached result should be shared across all sessions, not just current one.
Any proposals?
PS: Yes, I know about oracle function result cache, but it doesn't fit the requirements.
PPS: Yes, we can create 2nd artificial argument with some value like date in format of yyyymmddhh24mi so it changes each minute and we're able to use function result cache, but I hope it is a solution which will allow me to hide the caching dependencies inside.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我将使用全局应用程序上下文和刷新间隔为 1 分钟的作业来设置上下文。
PS：INTERVAL '1' HOUR 比 INTERVAL '0 1:00:00' DAY TO SECOND 更短且更有意义
I'd use a global application context, and a job with a refresh interval of 1 minute to set the context.
PS: INTERVAL '1' HOUR is shorter and more meaningful than INTERVAL '0 1:00:00' DAY TO SECOND
您想要缓存此查询的结果，并在所有会话之间共享缓存。我能想到的唯一方法是将查询包装在函数调用中，将结果存储在一个小表中。该函数将查询小表以查看最近 1 分钟内是否已存储计数，如果是，则返回它。
您可以通过定期运行作业来删除“缓存表”中早于 1 分钟的行来保持表较小，或者更好的是，也许可以截断它。
但是，只有当原始 SELECT COUNT(*) 是一个相对昂贵的查询时，我才能看到这样做的好处。
You want to cache the result of this query, and share the cache across all sessions. The only way I can think of is to wrap the query in a function call, store the result in a small table. The function will query the small table to see if the count has already been stored within the last 1 minute, and if so, return it.
You would keep the table small by running a job periodically to delete rows in the "cache table" that are older than 1 minute - or better still, perhaps truncate it.
However, I can only see this being of benefit if the original SELECT COUNT(*) is a relatively expensive query.