从union中只查询需要的mview
手动分区了物化视图
create materialized view MV_MVNAME_201001 as
select MONTH_ID, AMOUNT, NAME
from TABLE_NAME_201001
我已按月份201002、201003、...、201112、2012、2009
现在我需要从这些视图中查询，仅获取所需的视图。
是否可以在不涉及客户端的情况下进行？
示例查询
select AMOUNT, NAME
from (
--union all mview
)
where month_id >= 201003
and month_id < 201101
应仅查看 MV_MVNAME_201003 .. MV_MVNAME_201012
I have materialized view manually partitioned by month
create materialized view MV_MVNAME_201001 as
select MONTH_ID, AMOUNT, NAME
from TABLE_NAME_201001
201002, 201003, ..., 201112, 2012, 2009
Now i need query from these views, take only the required views.
Is it possible, without involving the client side?
example query
select AMOUNT, NAME
from (
--union all mview
)
where month_id >= 201003
and month_id < 201101
should look only to the MV_MVNAME_201003 .. MV_MVNAME_201012
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
物化视图是“物化”。它是一个包含数据的物理表。
生成物化视图的查询仅在刷新数据时使用，而不是在查询中使用。
Oracle 不知道数据来自哪里（在您的情况下 - 来自多个不同表的联合），除非您以某种方式指定它，例如 - 列。
但是在您的具体情况下，您有month_id列，您可以对表进行分区。
当您指定月份或月份范围时，它将仅扫描相应的分区。
UDP日期：
现在我更好地理解你的问题，但我无法给你更好的答案。你的问题与mviews无关。 Mview 可以是表。你的问题是一样的。您只想从某些动态表中进行选择。为此创建了分区。也许老狗知道一个技巧......
The materialized view is "materialized". It is a phisical table with data within it.
The query that produce the materialized view is used only when you refresh data, not on querys.
Oracle doesn't know where data came from(in your case - a union from several distinct tables), unless you specify it somehow, for example - a column.
But in your specific case you have the column month_id, on witch you can partition the table.
When you specify the month or range of months, it will scan only the correspondent partitions.
UDPATE:
Now I understand better your question, but I cannot give you a better answer. Your question has nothing to do with mviews. Mviews can be tables. Your problem is the same. You want to select only from some tables, dynamic. For this was created partitioning. Probably old dogs know a trick...