代码之家 › 专栏 › 技术社区 › NuCradle

如何在具有依赖关系的pl/sql oracle中执行批量更新

database-performance oracle12c sql-update plsql oracle

NuCradle · 技术社区 · 6 年前

目前,我正在pl/sql oracle 12.1中运行以下单独的更新,需要知道如何通过批量更新来提高性能,因为它们需要几个小时才能完成,或者任何其他策略。

问题是,我需要用一组相同的条件(那些 CASE WHEN 语句)从表中( LARGE_TBL )有几十万条记录( MAIN_TBL 它本身也有几十万张唱片。两个表都有索引 LT_ID 和 MT_ID )

还有很多其他的 UPDATES 具有不同的值 LT.IDX_2 和 MT.IDX_2 (为了简洁起见,我排除了它们)并且只显示 IDX_2 = G (还有其他相同的 UPDATE 与其他人 IDX_2 价值观)。

UPDATE MAIN_TBL MT
SET
    MT.STOP_FLAG = (  
        SELECT 
            CASE
                WHEN 
                    NOT EXISTS (SELECT 1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G')
                    OR (SELECT LT.COL_1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') IS NULL
                    OR (SELECT LT.COL_1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') <> 'Y'  
                THEN 'SF01'
                ELSE MT.STOP_FLAG
            END
        FROM DUAL
    ),
    MT.ES = (  
        SELECT 
            CASE
                WHEN 
                    NOT EXISTS (SELECT 1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G')
                    OR (SELECT LT.COL_1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') IS NULL
                    OR (SELECT LT.COL_1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') <> 'Y'
                THEN 'E'
                ELSE MT.ES
            END
        FROM DUAL
    ),
    MT.PW = (  
        SELECT 
            CASE
                WHEN 
                    NOT EXISTS (SELECT 1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G')
                    OR (SELECT LT.COL_1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') IS NULL
                    OR (SELECT LT.COL_1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') <> 'Y'
                THEN 'W'
                ELSE MT.PW
            END
        FROM DUAL
    ),
    MT.UPDATE_DT = SYSDATE
WHERE 
    MT.STOP_FLAG IS NULL
    AND MT.IDX_2 = 'G'
    AND MT.ES IS NULL
    AND MT.SS = 'C'
    AND MT.PW = 'A';


UPDATE MAIN_TBL MT
SET
    MT.STOP_FLAG = (  
        SELECT 
            CASE
                WHEN 
                    NOT EXISTS (SELECT 1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G')
                    OR (SELECT LT.COL_2 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') IS NULL
                    OR (SELECT LT.COL_2 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') <> 'Y'  
                THEN 'SF02'
                ELSE MT.STOP_FLAG
            END
        FROM DUAL
    ),
    MT.ES = (  
        SELECT 
            CASE
                WHEN 
                    NOT EXISTS (SELECT 1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G')
                    OR (SELECT LT.COL_2 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') IS NULL
                    OR (SELECT LT.COL_2 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') <> 'Y'
                THEN 'E'
                ELSE MT.ES
            END
        FROM DUAL
    ),
    MT.PW = (  
        SELECT 
            CASE
                WHEN 
                    NOT EXISTS (SELECT 1 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G')
                    OR (SELECT LT.COL_2 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') IS NULL
                    OR (SELECT LT.COL_2 FROM LARGE_TBL LT WHERE LT.LT_ID = MT.MT_ID AND LT.IDX_2 = 'G') <> 'Y'
                THEN 'W'
                ELSE MT.PW
            END
        FROM DUAL
    ),
    MT.UPDATE_DT = SYSDATE
WHERE 
    MT.STOP_FLAG IS NULL
    AND MT.IDX_2 = 'G'
    AND MT.ES IS NULL
    AND MT.SS = 'C'
    AND MT.PW = 'A';

问题是,例如在上面的例子中,第二个 更新 取决于第一个 更新 因为第二个 更新 只有在 MT.STOP_FLAG IS NULL . 所以如果 MT.STOP_FLAG 用第一个设置 更新 用 MT.STOP_FLAG = SF01 ),第二个 更新 不应执行,因为 WHERE 条款无法满足( mt.stop_标志为空 )换句话说,这些 更新 是应该执行的事情。

我还没有使用pl/sql的批量更新特性,所以我不知道如何实现这一点。我应该创建一个游标来从 大号 具有适当条件的表,例如 Idx_2=克 或 IDX_2 = R ,这是一个相对较大的表(几十万条记录),然后用 BULK COLLECT 分成几个定义 TYPE 最后用一个 FORALL 有多个独立的 更新 声明?或多重 福尔 的,每人一个 更新 ?

如果要使用游标,如何处理 案件发生时 我需要确认是否存在记录的语句?

2 回复 | 直到 6 年前

Boneist 6 年前

您的条件可以有效地重写为检查lt.col_1字段的值是否不等于“y”(即。 lt.col_1 is null or lt.col_1 != 'Y' )我已经构建了一个快速测试用例来演示这一点,使用您的旧检查方法和新方法:

WITH t1 AS (SELECT 1 mt_id, 10 val FROM dual UNION ALL
            SELECT 2 mt_id, 20 val FROM dual UNION ALL
            SELECT 3 mt_id, 30 val FROM dual UNION ALL
            SELECT 4 mt_id, 40 val FROM dual UNION ALL
            SELECT 5 mt_id, 50 val FROM dual),
     t2 AS (SELECT 2 lt_id, 'F' idx_2, NULL col_1 FROM dual UNION ALL
            SELECT 3 lt_id, 'G' idx_2, NULL col_1 FROM dual UNION ALL
            SELECT 4 lt_id, 'G' idx_2, 'N' col_1 FROM dual UNION ALL
            SELECT 5 lt_id, 'G' idx_2, 'Y' col_1 FROM dual)
SELECT 'new_way' qry,
       t1.mt_id,
       t1.val,
       CASE WHEN t2.col_1 is null or t2.col_1 != 'Y' THEN 'SF01' END new_stop_val
FROM   t1
       LEFT OUTER JOIN t2 ON t1.mt_id = t2.lt_id AND idx_2 = 'G'
UNION ALL
SELECT 'old_way' qry,
       t1.mt_id,
       t1.val,
       CASE WHEN NOT EXISTS (SELECT 1 FROM t2 WHERE t2.LT_ID = t1.MT_ID AND t2.IDX_2 = 'G')
                 OR (SELECT t2.COL_1 FROM t2 WHERE t2.LT_ID = t1.MT_ID AND t2.IDX_2 = 'G') IS NULL
                 OR (SELECT t2.COL_1 FROM t2 WHERE t2.LT_ID = t1.MT_ID AND t2.IDX_2 = 'G') <> 'Y'
                 THEN 'SF01'
       END new_stop_val
FROM   t1
ORDER BY mt_ID, qry;

QRY          MT_ID        VAL NEW_STOP_VAL
------- ---------- ---------- ------------
new_way          1         10 SF01
old_way          1         10 SF01
new_way          2         20 SF01
old_way          2         20 SF01
new_way          3         30 SF01
old_way          3         30 SF01
new_way          4         40 SF01
old_way          4         40 SF01
new_way          5         50 
old_way          5         50

现在,我们可以将大表上的检查折叠为单个检查,然后可以在单个case语句中检查大表中的其他列。这意味着您不再需要单独的update语句。您可以在单个merge语句中执行此操作,如下所示:

MERGE INTO main_table tgt
USING (SELECT mt.rowid, r_id,
              CASE WHEN lt.col_1 is null or lt.col_1 != 'Y' THEN 'SF01'
                   WHEN lt.col_2 is null or lt.col_2 != 'Y' THEN 'SF02'
                   ELSE mt.stop_flag -- null
              END new_stop_flag,
              CASE WHEN NVL(lt.col1, 'N') != 'Y' THEN 'E'
                   WHEN NVL(lt.col2, 'N') != 'Y' THEN 'E'
                   ELSE mt.es -- null
              END new_es,
              CASE WHEN NVL(lt.col_1, 'N') != 'Y' THEN 'W'
                   WHEN NVL(lt.col_2, 'N') != 'Y' THEN 'W'
                   ELSE mt.pw
              END new_pw
       FROM   main_table mt
              LEFT JOIN large_table lt ON (mt.mt_id = lt.lt_id AND lt.idx_2 = 'G')
       WHERE  mt.stop_flag IS NULL
       AND    mt.idx_2 = 'G'
       AND    mt.es IS NULL
       AND    mt.ss = 'C'
       AND    mt.pw = 'A') src
  ON (tgt.rowid = src.r_id)
WHEN MATCHED THEN
  UPDATE tgt.stop_flag = src.new_stop_flag,
         tgt.es = src.es,
         tgt.pw = src.pw;

Wernfried Domscheit 6 年前

您的更新语句看起来很奇怪,请尝试重新编写它。

如果你有类似的更新

UPDATE MAIN_TBL MT
SET MT.STOP_FLAG = (  
    SELECT 
        CASE 
            WHEN {whatever condition}
            THEN 'SF01'
            ELSE MT.STOP_FLAG
        END
    FROM DUAL
)

基本上是一样的

UPDATE MAIN_TBL MT
SET MT.STOP_FLAG = 'SF01'
WHERE {whatever condition}

下面的示例很可能不是一个有效的解决方案,但它们应该向您提示如何更好地编写此类更新。

UPDATE MAIN_TBL MT
SET
    MT.STOP_FLAG = 'SF01',
    MT.ES = 'E',
    MT.PW = 'W'
    MT.UPDATE_DT = SYSDATE
WHERE 
    MT.STOP_FLAG IS NULL
    AND MT.IDX_2 = 'G'
    AND MT.ES IS NULL
    AND MT.SS = 'C'
    AND MT.PW = 'A'
    AND NOT EXISTS (
        SELECT 1 
        FROM LARGE_TBL LT 
        WHERE LT.LT_ID = MT.MT_ID 
           AND (LT.IDX_2 = 'G' OR LT.COL_1 <> 'Y' OR LT.COL_1 IS NULL)
        );


UPDATE 
    (SELECT MT.*
    FROM MAIN_TBL MT
        JOIN LARGE_TBL LT ON LT.LT_ID = MT.MT_ID
    WHERE LT.IDX_2 = 'G' OR LT.COL_1 <> 'Y' OR LT.COL_1 IS NULL)
SET
    MT.STOP_FLAG = 'SF01',
    MT.ES = 'E',
    MT.PW = 'W'
    MT.UPDATE_DT = SYSDATE
WHERE 
    MT.STOP_FLAG IS NULL
    AND MT.IDX_2 = 'G'
    AND MT.ES IS NULL
    AND MT.SS = 'C'
    AND MT.PW = 'A'

如果你有类似的更新

更新main-tbl-mt
设置mt.stop_标志=(
选择
案例
当{任何条件}
然后是“SF01”
否则停止山标志
结束
从双重
)

基本上是一样的

更新main-tbl-mt
设置mt.stop_标志='sf01'
其中{任何条件}