基于上一个结束日期的大容量更新开始日期

Question

我试图在一个包含700 K记录的表上进行批量更新。我需要更新生效的开始日期与前一个记录的有效结束日期。在使用子查询时，我在update语句上的性能有问题。它需要一个多小时的运行，即使有一个过滤器的日期(7/1/2016-7/15/2016，约2k记录)。我把它作为一个简单的update语句、一个insert语句和一个循环来尝试。使用ROWID而不是account_dim_key (表上的PK)的解释计划是更理想的，但是，我得到一个错误，子查询返回多个行。我不知道为什么ROWID会出现这种情况。

ID是表上的自然键，account_dim_key是PK和唯一的。两者都有索引。表是类型2的SCD。

1. 如何使用ROWID修改update语句
2. 使用FORALL更新会更好吗?如果是的话，我将如何编写它(新的pl sql和不知名的数组)

Update语句使用ROWID返回错误，单行子查询返回多行，但具有最佳解释计划

UPDATE DEXWHS.D_ACCOUNT_VEEVA
   SET effective_end_dt =
          (SELECT prev_dt
             FROM (SELECT LAG (
                             effective_end_dt,
                             1,
                             effective_start_dt)
                          OVER (PARTITION BY account_dim_key
                                ORDER BY effective_start_dt)
                             AS prev_dt,
                          ROWID AS rid
                     FROM dexwhs.d_account_veeva ac2) a
            WHERE a.rid = ROWID)

使用非最优解释计划的acocunt_dim_key更新语句

UPDATE DEXWHS.D_ACCOUNT_VEEVA
   SET effective_end_dt =
          (SELECT prev_dt
             FROM (SELECT LAG (
                             effective_end_dt,
                             1,
                             effective_start_dt)
                          OVER (PARTITION BY id
                                ORDER BY effective_start_dt, account_dim_key)
                             AS prev_dt,
                          account_dim_key AS rid
                     FROM dexwhs.d_account_veeva ac2) a
            WHERE a.rid = account_dim_key)

用循环更新

CREATE OR REPLACE PROCEDURE PREV_UPDT
IS
   CURSOR c1
   IS
        SELECT account_dim_key,
               id,
               active_flag,
               effective_end_dt,
               effective_start_dt,
               created_date,
               last_modified_date,
               (SELECT prev_dt
                  FROM (SELECT LAG (
                                  effective_end_dt,
                                  1,
                                  effective_start_dt)
                               OVER (
                                  PARTITION BY id
                                  ORDER BY effective_start_dt, account_dim_key)
                                  AS prev_dt,
                               account_dim_key AS rid
                          FROM dexwhs.d_account_veeva ac2) a
                 WHERE a.rid = src.account_dim_key)
          FROM dexwhs.d_account_veeva src
      ORDER BY id, effective_start_dt, account_dim_key;
   r1   c1%ROWTYPE;
BEGIN
   OPEN c1;

   LOOP
      FETCH c1 INTO r1;

      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE ('id=' || r1.id);

      UPDATE dexwhs.D_ACCOUNT_VEEVA trgt
         SET trgt.effective_start_dt = r1.prev_date,
             trgt.audit_last_update_dt = SYSDATE,
       WHERE trgt.account_dim_key = r1.account_dim_key;

      DBMS_OUTPUT.PUT_LINE ('r1.id_found');
   END LOOP;

   CLOSE c1;
END

Answer 1

如果account_dim_key是主键，则尝试MERGE

MERGE INTO dexwhs.d_account_veeva a
USING (
   SELECT  account_dim_key,
           LAG ( effective_end_dt, 1, effective_start_dt)
           OVER (PARTITION BY account_dim_key
                 ORDER BY effective_start_dt)
           AS prev_dt
   FROM dexwhs.d_account_veeva
) b
ON (a.account_dim_key  = b.account_dim_key )
WHEN MATCHED THEN UPDATE SET a.effective_end_dt = b.prev_dt

查询必须花费一些时间，因为它正在更新整个表。

也许您可以使用对LAG ... (PARTITION BY account_dim_key ORDER BY effective_start_dt)列的复合索引来加快(account_dim_key, effective_start_dt)部件的速度。

CREATE INDEX some_name 
ON dexwhs.d_account_veeva(account_dim_key, effective_start_dt)

但是，Oracle可以忽略此索引，而更喜欢全表扫描，因为子查询针对整个表。