我可以在提交时仅对其 select 语句中的三个表中的两个表进行物化视图刷新吗？
我想使用刷新ON COMMIT的物化视图。我的支持视图通过 DB Link 连接本地数据库中的两个表和远程数据库中的一个表。如何仅在对两个本地表之一进行更改时才刷新视图？
还有其他方法可以解决这个问题吗？我可以让物化视图只连接两个本地表并为远程数据库中的列设置 NULL，然后在插入/更新物化视图时使用触发器来填充这些字段吗？或者物化视图的更新是否传播回源表？
我正在做这样的事情：
SELECT LOC1.ID, LOC1.NAME, LOC2.PRICING_TYPE, REM1.PURCHASING_ID
FROM LOCAL_TABLE_A LOC1, LOCAL_TABLE_B LOC2, [email protected] REM1
WHERE LOC1.ID = LOC2.MASTER_ID
AND LOC1.REM_ID = REM1.ID
AND LOC2.YEAR = REM1.YEAR
REMOTE_TABLE只是一个查找表，用于与两个本地表相关的信息。它不应该在这里驱动任何东西，我只希望物化视图在LOCAL_TABLE_A或LOCAL_TABLE_B发生变化时更新。
I would like to use a materialized view that refreshes ON COMMIT. My backing view joins two tables in the local database and one table in a remote database via DB Link. How can I have the view refresh only when changes are made to one of the two local tables?
Are there any other ways to solve this problem? Can I have the materialized view just join the two local tables and put NULLS for the columns from the remote database, and then have a trigger on insert/update to the materialized view that would fill in those fields? Or do updates to a materialized view propagate back to the source tables?
I'm doing something like this:
SELECT LOC1.ID, LOC1.NAME, LOC2.PRICING_TYPE, REM1.PURCHASING_ID
FROM LOCAL_TABLE_A LOC1, LOCAL_TABLE_B LOC2, [email protected] REM1
WHERE LOC1.ID = LOC2.MASTER_ID
AND LOC1.REM_ID = REM1.ID
AND LOC2.YEAR = REM1.YEAR
The REMOTE_TABLE is only a lookup table for information related to the two local tables. It should not drive anything here, and I only want the materialized view to update if LOCAL_TABLE_A OR LOCAL_TABLE_B CHANGE.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以对远程表使用中间物化视图。该 MV 将使用 REFRESH ON DEMAND 在您的本地数据库中创建，以便您可以手动刷新它。您的 MV 将使用本地表代替远程表。
You could use an intermediate Materialized View for the remote table. This MV would be created in your local DB with REFRESH ON DEMAND so that you can refresh it manually. Your MV would use the local table in place of the remote table.