在 Oracle 中保持表同步 [英] Keeping tables synchronized in Oracle

问题描述

我们即将进行并行测试，以将旧系统与新的闪亮版本进行比较.我们有一个 Oracle 数据库表 A，用于存储遗留系统的数据，以及一个等效的表 B，用于存储新系统的数据，因此在测试期间，数据库是非规范化的.(此外，旧系统和表 A 是固定的 - 不允许更改)

We're about to run side-by-side testing to compare a legacy system with a new shiny version. We have an Oracle database table, A, that stores data for the legacy system, and an equivalent table, B, that stores data for the new system, so for the duration of the test, the database is denormalized. (Also, the legacy system and table A are fixed - no changes allowed)

我想要做的是允许 A 上不频繁的 DML 操作传播到 B，反之亦然.我开始使用一对触发器来执行此操作，但遇到了一个明显的问题，即当触发器运行时，表正在发生变化，并引发异常.

What I want to do is to allow the infrequent DML operations on A to propagate to B, and vice-versa. I started with a pair of triggers to do this, but hit the obvious problem that when the triggers run, the tables are mutating, and an exception is thrown.

有处理这个问题的标准方法吗?我已经阅读了关于使用 dbms_scheduler 是否可行的不同报告......

Is there a standard way of handling this issue? I've read differing reports on whether or not using dbms_scheduler is the way to go...

谢谢，

安迪

更新:我最终解决了整个问题，并确保所有更新 A 的存储过程也更新 B，反之亦然.

Update: I've ended up chickening out of the whole issue and ensured that all stored procedures that update A, also update B, and vice versa.

我已将 Quassnoi 的回答标记为已接受，因为如果将来遇到同样的问题，我会遵循他的建议.

I've marked Quassnoi's answer as accepted, because I'd follow his suggestions if faced with the same issue in the future.

我已经标记了 JosephStyon 的答案，因为我通过在表 A 和 B 上添加两个插入/更新语句级别的触发器，然后使用 A 或 B 作为主表来执行他的合并过程，这取决于哪个触发器跑了(虽然首先我检查了目标表是否会因合并而改变，否则就提前退出).

I've marked up JosephStyon's answer, because I briefly got things working by adding two insert/update statement level triggers on tables A and B, then doing his merge procedure using A or B as the master table, depending on which trigger ran (although first I checked that the target table would be changed by the merge, earlying out if not).

推荐答案

我将创建 A 和 B 作为对单个规范化(或非规范化)表的视图，并在这些视图上创建了一个 INSTEAD OF 触发器来处理 DML 操作.

I'd create A and B as views over a single normalized (or denormalized) table, and created an INSTEAD OF trigger over these views to handle DML operations.

如果查询计划很重要，最好保留两个表副本:A_underlying 和 B_underlying 并像这样创建视图:

If the query plans matter, it's better to keep two copies of tables: A_underlying and B_underlying and create the views just like this:

CREATE VIEW A
AS
SELECT  *
FROM    A_underlying

CREATE VIEW B
AS
SELECT  *
FROM    B_underlying

谓词将被推送到视图中，实际表和视图的查询计划将相同.

The predicates will be pushed into the views, and the query plans for actual tables and views will be the same.

在两个视图上的 INSTEAD OF 触发器中，您应该将数据放入两个基础表中.

In INSTEAD OF triggers over both view, you should put the data into both underlying tables.

这篇关于在 Oracle 中保持表同步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！