Oracle 视图生成时的值缓存？
我有两个表 STATMENT 和 TRANSACTION，我想在其上创建视图。两个表都有 ID (VARCHAR2) 和 TS (NUMBER)。
对于每个 ID，每月都会根据 TRANSACTION 和其他表生成 STATEMENT。
该视图有 3 列：ID、TS 和 VALUE。视图中的每一行对应于 STATEMENT 中的一行，并且 VALUE 是一个相当复杂的聚合，依赖于 STATMENT 和 TRANSACTION。
具体来说，从 TRANSACTION 检索到的 VALUE 部分对于每个 ID 都是恒定的，与 TS 无关。
既然这个聚合值不会改变，那么Oracle会自动缓存它吗？如果没有，是否可以将这些值缓存在某处，例如为此值创建中间视图？
I have two tables STATEMENT and TRANSACTION, which I'd like to create a view on. Both tables have ID (VARCHAR2) and TS (NUMBER).
For each ID, STATEMENT is generated every month, based on TRANSACTION and other tables.
The view has 3 columns: ID, TS, and VALUE. Each row in the view corresponds to a row in STATEMENT, and VALUE is a rather complex aggregation depending on both STATEMENT and TRANSACTION.
Specifically, the portion of VALUE retrieved from TRANSACTION is constant for each ID, regardless of TS.
Since this aggregated value does not change, does Oracle automatically cache it? If not, is it possible to cache the values somewhere, for example, by creating an intermediate view for this value?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
非物化视图不会被缓存——它们只是包含在正在执行的语句中进行引用时运行的 SQL 语句。将它们视为视图包含的语句的宏或变量。因此，分层视图没有性能价值——让一个视图引用另一个视图。
Oracle 支持物化视图，但物化视图的限制性众所周知 -- 没有非确定性函数等。
为了区分差异，
CREATE VIEW创建一个非物化视图。 Oracle 需要 MATERIALIZED 关键字：CREATE MATERIALIZED VIEW ...才能实现视图。Non-materialized views are not cached -- they are merely contained SQL statements that are run when a reference is made in a statement that is being executed. Think of them like a macro, or variable for the statement the view contains. So there's no performance value to layering views -- having one reference another.
Oracle supports materialized views, but materialized views are notoriously restrictive -- no non-deterministic functions, etc.
To tell the difference,
CREATE VIEWcreates a non-materialized view. Oracle requires the MATERIALIZED keyword:CREATE MATERIALIZED VIEW ...for the view to be materialized.