Oracle物化视图与同一数据库服务器上的复制(Oracle Materialized Views Vs Replication on the same DB server)

Oracle物化视图与同一数据库服务器上的复制(Oracle Materialized Views Vs Replication on the same DB server)

我们需要在当天的预定时间运行报告。 该应用程序运行24 * 7，因此没有“非高峰”时间。

因此，运行报告不应该在系统上添加过多的负载。

该应用程序在WebSphere v6.1上运行，数据库是Oracle 10g R2。

我可以使用以下方法

一套旨在报告的非标准化表格。 创建实体化视图并将其用于报告。 我们可以每天更新一次视图。 我们可以使用Oracle的Data Guard创建另一个模式并实时复制表。

（1）由于我们的某些内部约束而不可行。

从性能的角度来看，我需要知道哪个更好，（2）还是（3）？

我从许多人那里听说，物化视图最初运作良好但随着数据量的增加，性能非常差。

任何人都有在同一个DB服务器中复制表的经验（但是diff实例或模式）。

We need to run reports at a scheduled time of the day. The application runs 24*7 and so there is not "off-peak" time as such.

Therefore, running the reports should not add undue load on the system.

The application runs on WebSphere v6.1 and the database is Oracle 10g R2.

I have the following approaches at my disposal

A set of de-normalized tables aimed at reporting. Creating Materialized views and using them for reports. We can update the views once a day. We can create another schema and replicate the tables realtime using Oracle's Data Guard.

(1) is not feasible due to certain internal constraints we have.

I need to know, from a performance point of view, which is better, (2) or (3) ?

I hear from many ppl that Materialized views initially work well but as data volumes increase, the performance is very poor.

Anyone has experience with Replication of tables within the same DB server (but diff instances or schemas).

最满意答案

在某种程度上，物化视图是非规范化表 - 非规范化是您可以在SELECT语句中定义的任何内容，例如连接，聚合和分析函数。 在MV的原始定义之后，您可以向底层MV表添加任何索引，以获得所需的性能。

话虽如此，我认为您的选择是：

在同一个数据库中使用非规范化表，这些表以某种方式由您编写的代码维护 - 此选项将使您能够最大程度地控制加载过程，但代价是必须编写和维护代码。 您还将消除单独实例的基础架构开销。 非规范化过程和报告查询将添加到活动/事务数据库的资源需求，并且必须调整大小以处理此问题。 使用此选项，您还可以将报告应用程序的可用性与事务系统的可用性联系起来。 在相同的数据库中使用MV - 以上关于基础架构和资源开销的注释适用，但您可以利用Oracle的MV功能进行调度（通过DBMS_JOB实现）和事务读取一致性（“旧”数据仍然可见，直到新的SELECT已解决并已提交）。 在同一主机上的另一个数据库/实例中使用MV - 使用此选项可获得一些边际分离和潜在可用性，但您仍然会影响数据库主机的整体资源。 更高版本的Oracle允许您将实例中的资源使用控制到细粒度级别，因此在我看来，没有充分的理由在同一主机上运行单独的数据库。 在另一个主机上的另一个数据库中使用MV - 您可以设置到事务系统的db链接，并在链接上执行MV刷新。 您仍然会有MV刷新/“加载”过程影响源系统上的资源，但所有查询活动都将被隔离，并且在源系统停机期间您将获得一定程度的报告可用性。 您必须使用此选项购买额外的Oracle许可证。 使用Data Guard实例 - 缺点：附加许可证，设置和管理的复杂性增加。 优点：对源系统的影响最小，系统的真实副本，如果使用逻辑而不是物理复制，则可以在Data Guard数据库中创建其他结构（视图，索引等）。

To some degree, materialized views are denormalized tables - the denormalization is whatever you can define in a SELECT statement, e.g. joins, aggregations, and analytic functions. After the original definition of the MV, you can add whatever indexes to the underlying MV table that are necessary to gain the performance you need.

Having said that, I think your options are:

Use denormalized tables in same database that are somehow maintained by code you write - This option will give you the greatest control over the loading process at the expense of having to write and maintain the code. You'll also eliminate the infrastructure overhead of a separate instance. The denormalization process and the reporting queries will add to the resource requirements of the active/transactional database and you must be sized to handle this. WIth this option you also have tied the availability of the reporting application to the availability of the transactional system. Use MV's in the same database - The above comments about infrastructure and resource overhead apply, but you gain the leverage of using Oracle's MV functionality for scheduling (implemented via DBMS_JOB) and transactional read consistency (the "old" data is still visible until the new SELECT is resolved and committed). Use MV's in another database/instance on the same host - You gain some marginal separation and potential availability with this option, but you are still affecting the overall resources of the database host. The later versions of Oracle let you control resource usage within the instance to a fine-grained level, so in my opinion there's no good reason to run a separate database on the same host. Use MV's in another database on a different host - You can set up a db link to the transactional system and perform the MV refresh across the link. You'll still have the MV refresh/"load" process affecting the resources on the source system, but all query activity will be isolated and you'll have some degree of availability of reports during down time for the source system. You'll have to buy additional Oracle licenses with this option. Use a Data Guard instance - Disadvantages: additional licenses, increased complexity to set up and administer. Advantages: lowest impact on the source system, true copy of system and if you use logical as opposed to physical replication you can create additional structures (views, indexes, etc.) in the Data Guard Database.