更新特定列触发器的最佳实践。

Question

更新特定列触发器的最佳实践。

3

欢迎来到Oracle专家的世界

在一个Oracle 12数据库中（升级已经安排好了;-)），我们通过“after update”触发器设置不同的表更新一个共同的基本表，如下所示：

Search_Flat
ID	Field_A	Field_B	Field_C

现在table1包含n列，其中假设有2列与Search_Flat表相关。由于table1的更新可能仅涉及对Seach_Flat无关的列，因此我们想要在触发器中添加检查。因此，我们的第一种方法如下：

CREATE OR REPLACE TRIGGER tr_tbl_1_au_search
    AFTER UPDATE OF
        field_a,
        field_b
    ON schemauser.search_flat
FOR EACH ROW
    BEGIN
        IF :new.field_a <> :old.field_a THEN 
            UPDATE schemauser.search_flat SET field_a = :new.field_a WHERE id = :new.ID;
        END IF;
        IF :new.field_b <> :old.field_b THEN 
            UPDATE schemauser.search_flat SET field_b = :new.field_b WHERE id = :new.ID;
        END IF;
    END;

或者，我们还可以像以下这样设置触发器：

CREATE OR REPLACE TRIGGER tr_tbl_1_au_search
    AFTER UPDATE OF
        field_a,
        field_b
    ON schemauser.search_flat
FOR EACH ROW
    BEGIN
        IF :new.field_a <> :old.field_a OR :new.field_b <> :old.field_b THEN 
            UPDATE schemauser.search_flat 
            SET field_a = :new.field_a, 
                field_b = :new.field_b 
            WHERE id = :new.ID;
        END IF;
    END;

现在的问题是关于触发器本身的设置。哪种方法更好，与以下方面有关：

锁定搜索平面行的时间
受影响组件的整体性能（即table_1、trigger和search_flat）

在生产中，我们考虑到触发器中涉及了每个表的10个字段。同时，我们有独立的应用服务器访问共享数据库，同时更新这4张表。我们不时会发现以下错误，这也是我们希望优化触发器的原因：

ORA-02049：超时：分布式事务等待锁定

注：由于基础表用于GUI并要求立即更新，而4张供应表的记录数量对于更新材料化视图来说太多了，因此选择了这种设置。

期待您的讨论和想法。

- Arno

1

代码在SEARCH_UPDATE上有一个触发器和一个更新操作；其中一个应该是TABLE1吗？此外，更新语句只会一次更改一行，还是可以同时更新多行？如果每个语句更新多行，则复合触发器可以帮助提高性能。 - Jon Heller

嗨，@ChrisSaxon，我们正在讨论数百万条记录乘以4，包括多个连接。提交时的刷新需要几分钟，而更新触发器大约在毫秒级别内完成98％。只要只有一个应用服务器更新基本表，性能就绝对没问题。我们还与我们的DB专家讨论了这个问题，我认为MV方法不会成功。 - Arno

@Arno 抱歉，我的意思是 TABLE1 是如何更新的？如果一个 SQL 语句更改了 TABLE1 中的一百行，复合触发器可以收集所有必要的 SEARCH_FLAT 更改，然后在语句结束时一次性应用它们，而不是每行都应用一次。 - Jon Heller

@Arno - 这个过程会改变 x 百万行数据，然后触发 4x 百万次更新吗？如果是这样，触发器在这种情况下表现良好让我感到惊讶。正如 Jon 所问的那样，全面解释整个过程将有助于理解。 - Chris Saxon

2

你说你正在使用Oracle 12数据库，是12.1还是12.2？在12.2中，你可以使用实时物化视图来实现这一点。（“这”是指“维护一个扁平表以进行搜索而不引入任何延迟/过期的结果”）。另外，顺便说一下，你提出的第二个选项（一个更新而不是多个更新）更好。 “锁定时间”是无关紧要的，因为记录将被锁定，直到调用触发器的事务提交。在你的方法中，你还需要更好地处理NULL比较并注意死锁。总之，我认为你应该放弃触发器。 - Matthew McPeak

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Matthew McPeak · Accepted Answer

根据您的帖子，我了解到您有4个实时表（名为“table1”，“table2”等），您想要在这些表中进行搜索，但是查询速度太慢，因此您希望维护一个单独的扁平化表格来进行搜索，并拥有触发器以保持该扁平化表格始终最新。您想知道哪种触发器方法更好。

我认为答案是“两者都不好”，因为两者都容易出现死锁。想象一下以下情况：

用户1 -

UPDATE table1 
SET field_a = 500 
WHERE <condition effecting 200 distinct IDs>

大约在同一时间，用户2 -

UPDATE table1 
SET field_b = 700 
WHERE <condition effecting 200 distinct IDs>

触发器开始处理。您无法控制更新行的顺序。可能会像这样：

用户1的触发器，时间索引100 ->

UPDATE search_flat SET field_a = 500 WHERE id = 90;

用户2的触发器，时间索引101 ->

UPDATE search_flat SET field_b = 700 WHERE id = 91;

User 1's trigger, time index 102 ->

UPDATE search_flat SET field_a = 500 WHERE id = 91;  (waits on user 2's session)

用户2的触发器，时间索引103 ->

UPDATE search_flat SET field_b = 700 WHERE id = 90;  (deadlock error)

用户2的原始更新失败并回滚。

您有多个并发进程同时更新具有相同行集的search_flat，并且无法控制处理顺序。这是死锁的配方。

如果您想要安全地执行此操作，则应考虑不使用您概述的任何FOR EACH ROW触发器方法之一。而是创建一个复合触发器来执行此操作。

以下是一些示例代码，以说明这个想法。请务必阅读注释。

-- Aside: consider setting this at the system level if on 12.2 or later
--   alter system set temp_undo_enabled=false;

CREATE GLOBAL TEMPORARY TABLE table1_updates_gtt (
  id          NUMBER,
  field_a     VARCHAR2(80),
  field_b     VARCHAR2(80)
) ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE table2_updates_gtt (
  id          NUMBER,
  field_a     VARCHAR2(80)
) ON COMMIT DELETE ROWS;

-- .. so on for table3 and 4.

CREATE OR REPLACE TRIGGER table1_search_maint_trg
  FOR INSERT OR UPDATE OR DELETE ON table1  -- with similar compound triggers for table2, 3, 4.
    COMPOUND TRIGGER

  AFTER EACH ROW IS
  BEGIN
    -- Update the table-1 specific GTT with the changes.
    CASE WHEN INSERTING OR UPDATING THEN
      -- Assumes ID is immutable primary key
      INSERT INTO table1_updates_gtt (id, field_a) VALUES (:new.id, :new.field_a);
         WHEN DELETING THEN
      INSERT INTO table1_updates_gtt (id, field_a) VALUES (:old.id, null);  -- or figure out what you want to do about deletes.
    END CASE;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    -- Write the data from the GTT to the search_flat table.
    -- NOTE: The ORDER BY in the next line is what saves us from deadlocks.
    FOR r IN ( SELECT id, field_a, field_b FROM table1_updates_gtt ORDER BY id ) LOOP
      -- TODO: replace with BULK processing for better performance, if DMLs can affect a lot of rows
      UPDATE search_flat sf
      SET    sf.field_a = r.field_a,
             sf.field_b = r.field_b
      WHERE  sf.id = r.id
      AND    ( sf.field_a <> r.field_a 
               OR (sf.field_a IS NULL AND r.field_a IS NOT NULL) 
               OR (sf.field_a IS NOT NULL AND r.field_a IS NULL)
               OR sf.field_b <> r.field_b 
               OR (sf.field_b IS NULL AND r.field_b IS NOT NULL) 
               OR (sf.field_b IS NOT NULL AND r.field_b IS NULL)
             );
    END LOOP;             
          
  END AFTER STATEMENT;

END table1_search_maint_trg;

另外，正如许多评论者所指出的那样，最好使用物化视图来实现此目的。如果您使用的是12.2或更高版本，则实时物化视图（又称“启用查询计算”）为此类事情提供了很多帮助。对于您的应用程序没有COMMIT开销，并且可以获得实时搜索结果。只是当底层表有大量最近更新时，搜索时间会略微降低。