具有列聚合的物化视图
这是我在此处发布的另一个问题。请不要因为重复而关闭， 因为它走向另一个方向。
我想用另一列的聚合自动更新数据库列。 涉及三个表：
T_RIDER
RIDER_ID
TMP_PONYLIST
...
T_RIDER_PONY
RIDER_ID
PONY_ID
T_PONY
PONY_ID
PONY_NAME
...
T_RIDER 和 T_PONY 通过 T_RIDER_PONY 具有 n:m 关系。 T_RIDER 和 T_PONY 还有一些列，但只有 TMP_PONYLIST 和 PONY_NAME 与此处相关。
TMP_PONYLIST 是一个以分号分隔的 PONY_NAMES 列表，想象一下类似 “Twisty Tail;Candy Cane;Lucky Leaf” 的内容。 无论 T_RIDER_PONY 或 T_PONY 发生什么情况，我都希望使该字段保持最新状态。
所有应用程序仅在视图上工作，从不直接访问表，我需要使用物化视图来解决这个问题。物化是绝对的要求 由于性能原因，并且需要视图在提交时更新自身。
视图应该像这样创建
CREATE MATERIALIZED VIEW
V_TMP_PONYLIST
BUILD IMMEDIATE
REFRESH COMPLETE ON COMMIT
AS SELECT
...
对于...我尝试了以下聚合技术 这篇文章。
- WM_CONCAT->在我的 Oracle 用户定义聚合中不可用
- ->
ORA-12054 - ROW_NUMBER 和 SYS_CONNECT_BY_PATH ->
ORA-12054
我还没有尝试过：
- 特定函数
- 使用引用游标的
- 函数泛型函数COLLECT 函数
您认为有机会使这些中的任何一个与物化视图一起使用吗？或者它毫无意义。您知道其他可以与物化视图一起使用的技术吗？
我正在使用 Oracle 数据库 10g 企业版版本 10.2.0.4.0 - 64bi。
This is another stab into a problem I posted here. Please don't close as duplicate,
because it goes in another direction.
I'd like to automatically update a database column with an aggregate of another column.
There are three tables involved:
T_RIDER
RIDER_ID
TMP_PONYLIST
...
T_RIDER_PONY
RIDER_ID
PONY_ID
T_PONY
PONY_ID
PONY_NAME
...
T_RIDER and T_PONY have an n:m relationship via T_RIDER_PONY.T_RIDER and T_PONY have some more columns but only TMP_PONYLIST and PONY_NAME are relevant here.
TMP_PONYLIST is a semicolon spararated list of PONY_NAMES, imagine something like "Twisty Tail;Candy Cane;Lucky Leaf".
I'd like to keep this field up to date no matter what happens to T_RIDER_PONY or T_PONY.
All applications work only on views, the tables are never accessed directly and I need to solve this problem with a materialized view. Materialized is an absolute requirement
because of performance reasons, and it is required, that the view updates itself on commit.
The view should be created like this
CREATE MATERIALIZED VIEW
V_TMP_PONYLIST
BUILD IMMEDIATE
REFRESH COMPLETE ON COMMIT
AS SELECT
...
For ... I tried the following aggregation techniques from this article.
- WM_CONCAT -> not available in my Oracle
- User-Defined Aggregate ->
ORA-12054 - ROW_NUMBER and SYS_CONNECT_BY_PATH ->
ORA-12054
I didn't try yet:
- Specific Function
- Function Generic Function using Ref Cursor
- COLLECT function
Do you see any chance to get any of these working with a materialized view, or is it pointless. Do you know of other techniques that might work with a materialized view?
I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您想要创建一个 ON COMMIT REFRESH JOIN AGGREGATE MATERIALIZED VIEW。此类MV有很多限制。基本上，除了简单连接之外的任何内容、SUM、COUNT 和 AVG 都不会通过所有 DML 活动进行 ON COMMIT 刷新。
在我看来，你试图以错误的心态解决这个问题：在知道技术路径是否能从物理上解决你的问题之前，你已经选择了技术路径。相反，您应该研究所有可用的工具，并在能够满足您的要求的工具中选择最好的工具（最容易实现/维护的工具）。
您已经获得了已知有效的选项：复杂逻辑触发器、简单视图、过程方法（仅通过经过彻底测试和批准的 API 更新基表，该 API 已知可以很好地处理列逻辑）。
您已经说过，由于性能问题，简单的视图将无法工作。我建议研究其他选项：触发器可以让你保留现有的代码，但你可能会遇到很多不可预见的副作用（复杂的触发器很有趣）。过程逻辑是最容易编码/维护的，但您必须实际使用它并修改您的应用程序才能使用新的 API。您可能必须撤销更新基表的权限，以确保通过 API 更新表。
you want to create an ON COMMIT REFRESH JOIN AGGREGATE MATERIALIZED VIEW. This type of MV has lots of limitations. Basically anything beyond simple joins, SUM, COUNT and AVG won't be ON COMMIT-refreshable with all DML activity.
In my opinion you are trying to solve this issue in the wrong state of mind: you have already chosen the technical path before knowing if it will physically solve your problem. You should instead study every available tools, and choose among those that will answer your requirements the best one (the easiest to implement/maintain).
You have already been given options that are known to work: complex-logic triggers, simple views, procedural approach (only update the base tables through a thoroughly tested and approved API that is known to handle the column logic well).
You have already stated that a simple view won't work because of performance problem. I would suggest studying the other options: triggers will let you keep your existing code but you will probably have lots of unforeseen side effects (complex triggers are lots of fun). Procedural logic is the easiest to code/maintain but you will have to actually use it and modify your application to use the new API. You may have to revoke the rights to update the base table to make sure the tables are updated through the API.