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

Question

欢迎 Oracle 专业人士

在 Oracle 12 数据库中(已经安排了升级 ;-))，我们设置了不同的表，通过“更新后”触发器更新公共(public)基表，如下所示:

<表类="s-表"><头>Search_Flat<日> <日> <日> <正文>身份证字段A字段_B字段_C

现在 table1 包含 n 列，假设 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;

现在的问题是关于触发器本身的设置。哪种方法更好:

• search_flat 行的锁定时间
• 受影响组件(即 table_1、trigger 和 search_flat)的整体性能

在生产中，我们讨论的是 4 个表，每个表有 10 个字段，每个字段都在触发器中考虑。我们有独立的应用程序服务器访问共享数据库，同时更新 4 个表。我们有时会检测到以下错误，这就是我们不想优化触发器的原因:

ORA-02049: timeout: distributed transaction waiting for lock

旁注:由于性能原因，已选择此设置而不是 View 或实体化 View ，因为在 gui 中使用基表，要求立即更新，并且记录的数量4 个馈送表太高，无法在更新时更新物化 View 。

我期待着讨论和您的想法。

Answer 1

据我了解您的帖子，您有 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;

用户1的触发器，时间索引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;

此外，正如许多评论者指出的那样，为此使用物化 View 可能更好。如果您使用的是 12.2 或更高版本，实时物化 View (又名“启用查询计算”)为此类事情提供了很多希望。您的应用程序和实时搜索结果没有COMMIT 开销。只是如果基础表有很多最近的更新，搜索时间会稍微降低。