Oracle物化视图问题 [英] Oracle materialized view question

问题描述

我有一个表，其中包含有关不同事件的信息，例如

I have a table that holds information about different events, for example

CREATE TABLE events (
    id int not null primary key, 
    event_date date, ...
) 

我意识到90％的查询仅访问今天的事件；较旧的行将存储以供历史记录，并最终移至存档表.
但是，事件表仍然很大，我想知道是否可以通过创建具有类似WHERE event_date = trunc(sysdate)或可能在event_date上进行索引的物化视图来提高性能?完全允许吗?

I realized that 90% of all queries access only today events; the older rows are stored for history and eventually moved to an archive table.
However, events table is still large, and I wonder if I can improve the performance by creating a materialized view that has something like WHERE event_date = trunc(sysdate) and maybe index on event_date ? Is it allowed at all?

谢谢

推荐答案

是的，请参见主键实例化视图":

yes this is allowed see "primary key materialized view":

主键具体化视图可能包含一个子查询，以便您可以 在远程实例化视图站点中创建行的子集

Primary key materialized views may contain a subquery so that you can create a subset of rows at the remote materialized view site

和复杂的实体化视图"

and "complex materialized view"

如果您很少刷新并且想要更快的查询性能，请使用 方法A(复杂的实体化视图).
如果您定期刷新并可能牺牲查询性能，请使用方法B(简单的实例化视图).

If you refresh rarely and want faster query performance, then use Method A (complex materialized view).
If you refresh regularly and can sacrifice query performance, then use Method B (simple materialized view).

位于 http://download.oracle.com /docs/cd/B10500_01/server.920/a96567/repmview.htm

在您的示例中，恕我直言，这不是复杂的实体化视图"的可能性很高:

In your example chances are good IMHO that this is not a "complex materialized view":

CREATE MATERIALIZED VIEW events_today REFRESH FAST AS
SELECT * FROM EVENT WHERE event_date = trunc(sysdate);

只需尝试一下，看看Oracle是否通过REFRESH FAST子句接受它.

Just try it and see if Oracle accepts it with the REFRESH FAST clause.

编辑-另一种选择:

根据您的数据库版本(企业+分区)和版本(11gR2)，您可以使用称为INTERVAL分区的Oracle新功能在现有表中定义每日分区".这样，大多数查询都会更快地获得大量结果，而又不会有效地复制数据-请参见

Depending on your DB Edition (Enterprise + Partitioning) and Version (11gR2) you could use a new Oracle feature called INTERVAL partitioning to define "daily partitions" within the existing table. This way most of your queries get alot faster without effectively duplicating the data - see http://www.oracle.com/technetwork/database/options/partitioning/twp-partitioning-11gr2-2009-09-130569.pdf

这篇关于Oracle物化视图问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！