如何提高大规模UPDATE from SELECT的性能

我想用另一个表的数据更新一个表。我目前的方法是这样的:

UPDATE items t1
SET (name, manufacturer_id, price) =
    (SELECT
        t2.item_name,
        t2.item_manufacturer_id,
        t2.item_price
    FROM staged_items t2
    WHERE t2.upgrade_version = 1234
      AND t2.operation = 'modification'
      AND t1.id = t2.item_id)
WHERE EXISTS (
SELECT 1
FROM staged_items t2
WHERE t2.upgrade_version = 1234
  AND t2.operation = 'modification'
  AND t1.id = t2.item_id)

问题是它花了太长时间，我不知道如何得到反馈或估计需要多长时间。我已经等了大约3个小时了，没有结果，v$session_longops上没有等待的操作。

我想知道如何提高更新的性能，或者另一种更有效的方法。另外，我想知道如何检查运行查询的状态。

一些评论

staged_items表预计至少包含3亿个表项，并在长期内增长到数十亿个
表项。
- 每次运行UPDATE时，预计只有"少数条目"(从0到~ 100万)符合条件(t2.upgrade_version = somenumber和t2.operation = 'modification')。
- 目前我正在测试一个有100万个条目的staged_items表，所有条目都符合条件。
items表预计有 2000万表项，并且长期保持在这个数量级。
- 目前我正在测试大约100万个条目，其中大多数与UPDATE匹配。
目前我没有索引，但我正在考虑在items.id, staged_items.item_id, staged_items.upgrade_version和staged_items.operation上创建它们
- 我不太确定哪些索引是真正需要和有用的
UPDATE预计不会修改任何索引列，无论如何，这可能会在未来发生变化，所以我非常感谢有关该场景如何对所提议的解决方案产生影响的评论。

Edit:最后，我选择使用rownum将查询分页为多个查询，而不是检查正在运行的查询的状态。这允许我等待每一个(较小的)查询，并检查和估计完成的百分比。

考虑到这一点，我的原始查询看起来像这样:

UPDATE items t1
SET (name, manufacturer_id, price) =
    (
      SELECT
        t2.item_name,
        t2.item_manufacturer_id,
        t2.item_price
      FROM
        (
          SELECT /*+ FIRST_ROWS(n) */ 
            a.*,
            ROWNUM rnum
          FROM
            (
              SELECT *          
              FROM staged_items t2
              WHERE t2.upgrade_version = 1234
                AND t2.operation = 'modification'
                AND t1.id = t2.item_id
              ORDER BY t2.id
            ) a
          WHERE ROWNUM <= MAX_ROW_TO_FETCH
        )
      WHERE rnum >= :MIN_ROW_TO_FETCH
    )
WHERE EXISTS (
SELECT 1
FROM staged_items t2
WHERE t2.upgrade_version = 1234
  AND t2.operation = 'modification'
  AND t1.id = t2.item_id)

(基于此链接的Pagination with ROWNUM部分)

无论如何，对于外部WHERE，我使用了Gordon Linoff的解决方案。

对于您的查询，您想要一个索引:

staged_items(item_id, upgrade_version, operation)

我也认为你可以重写外部where子句:

WHERE t1.id IN (SELECT t2.item_id
                FROM staged_items t2
                WHERE t2.upgrade_version = 1234 AND t2.operation = 'modification'
               )

然后，需要在staged_items(upgrade_version, operation, item_id)和items(id)上建立索引。注意，索引中键的顺序很重要，您仍然希望相关子查询的第一个索引获得值。

当您需要在表中执行大量的DML操作时，您可以使用BULK COLLECT和FORALL。为了获得操作的状态，我通常创建一个日志表来存储信息。而且，在此之上，您可以添加一些索引来加快查询。

我不会使用MERGE语句，因为它真的很慢。

所以，对你来说，一个可能的解决方案是这样的:

create table log_load(table_name varchar2(50), create_date date, message varchar2(500));
declare
cursor cur_upd is
SELECT rowid as row_id
FROM items t1
WHERE EXISTS (
  SELECT 1
  FROM staged_items t2
  WHERE t2.upgrade_version = 1234
  AND t2.operation = 'modification'
  AND t1.id = t2.item_id);
TYPE fetch_array IS TABLE OF cur_upd%ROWTYPE;
s_array fetch_array;
BEGIN
    insert into log_load values ('item',sysdate,'Start')
    commit;
    OPEN cur_upd;
    upd_ := 0;
    LOOP
         FETCH cur_upd BULK COLLECT INTO s_array LIMIT 50000;
         upd_ := upd_ + s_array.COUNT;
        FORALL i IN 1..s_array.COUNT
         UPDATE items t1
         SET (name, manufacturer_id, price) =
          (SELECT t2.item_name, t2.item_manufacturer_id, t2.item_price
           FROM staged_items t2
           WHERE t2.upgrade_version = 1234
           AND t2.operation = 'modification'
           AND t1.id = t2.item_id)
         WHERE t1.rowid = cur_upd[i].row_id;
    insert into log_load values ('item',sysdate,'50000 updated.')
    commit;
    EXIT WHEN cur_upd%NOTFOUND;
   END LOOP;
   CLOSE cur_upd;
   insert into log_load values ('item',sysdate,'end');
   commit;
END;

有一个关于一个男人走进医生办公室的老笑话。他把胳膊绕了一圈，说:"医生，我这样做的时候很疼!"医生看着他说:"好吧，那就别这么做!"

我认为你的主要问题是你的staging表是巨大的，但你真的只需要看一小部分数据。不要使用完整的staging表进行更新。也许您可以尝试创建一个物化视图，在运行更新之前将其刷新完成。您的mat视图将基于:

SELECT
        t2.item_name,
        t2.item_manufacturer_id,
        t2.item_price
    FROM staged_items t2
    WHERE t2.upgrade_version = 1234
      AND t2.operation = 'modification'

你也可以添加并行提示。如果您需要在每次运行更新时更改这些值，您可以选择通过cta(以select方式创建表)创建一个常规表，使用相同的SQL，但使用不同的值(upgrade_version = 5678或其他)。

你的另一个问题是跟踪。最干净的方法是使用pl/sql。它可能不像单个更新语句那么简单，但是您可以添加日志记录，并控制提交点(您的dba将对此感到感激)。

您的驾驶表将是mat视图(或CTAS表)。比如:

declare
  cursor sel_stage_mv is
  select * from my_stage_mv;
  l_cnt pls_integer := 0;
  l_upd_cnt pls_integer := 0;
begin
  for rec in sel_stage_mv
  loop
    l_cnt := l_cnt + 1;
    -- all needed indexes are on main table (id, etc...)
    update main_table
    set ...
    where id = rec.id;
    l_upd_cnt := l_upd_cnt + SQL%ROWCOUNT;
    if (mod(l_cnt, 10000) = 0) then
      -- insert to some log table via autonomous procedure
      ins_log(...l_upd_cnt ...);
      commit;
    end if;
  end loop;
  commit;
end;

当然是在业余时间运行。我还对您的环境和事务需求做了一些假设，但只有您知道什么可以满足您的设置和需求。

不妨试试这个:

UPDATE 
(SELECT t1.name, t1.manufacturer_id, t1.price,
   t2.item_name,
   t2.item_manufacturer_id,
   t2.item_price, 
   t1.id, t2.item_id
from items t1
   JOIN staged_items t2 on t1.id = t2.item_id
WHERE t2.upgrade_version = 1234
   AND t2.operation = 'modification')
SET name = item_name, 
   manufacturer_id = item_manufacturer_id, 
   price = item_price;

应该避免staged_items的double SELECT，因此可以更快。但是，我没有对它进行测试。

一些评论

相关内容

最新更新

热门标签：