时间戳

首页 > 解决方案 > 截断表表最优解

java - 截断表表最优解

问题描述

我创建了一个 spring 集成工作流，将数据从 csv 加载到 oracle 数据库。这是一个集群环境，每个节点处理一个 csv 文件并将数据加载到临时表中。

临时表的结构：（索引上AccountNumber）

ID
AccountNumber
ItemId
Value

我有一个将文件名发布到队列的 spring 集成 rabbitmq 配置。集群中的每个节点只提取一个文件，从文件系统（共享文件系统和 oracle 数据库）中读取 csv 并将数据加载到 TEMP 表中。（每个 csv 的大小为 2GB）。

在加载临时表中的所有数据后，一个节点应该将数据从临时表移动到与临时表具有相同结构的主表。

主表：（索引Account Number）

Id
AccountNumber
ItemId
Value

我创建了一个存储过程，它从主表中删除现有帐号并在主表中加载帐户。

将数据移至主表后，我将在过程结束时从临时表中删除数据。

我的问题是截断此表的最佳方法是什么。

问题：假设我的主表中有这条记录。

主表：

Account Number   ItemId   ItemValue
-----------------------------------
123456             5        XYZ
123456             6        ABC
123456             7        DEF

现在我从 csv 获取这个条目到临时表：

AccountNumber    ItemId    ItemValue
------------------------------------
123456             5        FGH

现在我的主表应该只有一个值。应删除 ItemId 为 6 和 7 的行。

Account Number   ItemId    ItemValue
-------------------------------------
123456             5        FGH

我可以通过合并来实现这一点吗？

方案 1：

在将数据加载到 TEMP 表之前截断该表会更好吗？（两个独立的数据库事务，一个用于截断表，另一个用于数据移动）。

（这会在发布队列中的文件名之前调用）一个在加载前批量清理临时表的过程。

步骤1：

create or replace procedure CleanTempTable
IS
v_numberRows int :=20000;

BEGIN
loop
Delete from TEMP where rownum <= v_numberRows;
EXIT WHEN SQL%ROWCOUNT = 0;
commit;
END LOOP;
END;
/

将数据从 temp 移动到 main 的一种过程。

这要求在巩固阶段结束。

CREATE OR REPLACE PROCEDURE LOAD_DATA_TO_CONSOLIDATE (updatecount OUT NUMBER )
IS
  cnt number := 0;
  account_num MAIN_TABLE.ACCOUNT_NO%TYPE;
  CURSOR account_cursor IS
    SELECT distinct ACCOUNT_NO from TEMP_TABLE;
BEGIN
OPEN account_cursor;
    LOOP
        FETCH account_cursor INTO account_num;
        EXIT WHEN account_cursor%NOTFOUND;
        delete from MAIN where ACCOUNT_NO = account_num;
    insert into MAIN(ID,ACCOUNT_NO,FACT_ID,FACT_VALUE) select HIBERNATE_SEQUENCE.nextval,temp.ACCOUNT_NO,temp.VALUE from TEMP temp
    where ACCOUNT_NO = account_num;
        cnt := cnt + sql%rowcount;
    commit;
    END LOOP;
    updatecount := cnt;
    CLOSE account_cursor;
END LOAD_DATA_TO_CONSOLIDATE;

场景二：

将数据从 TEMP 表移动到主表后截断该表会更好吗？（一个存储过程中的所有内容（在一个 DB 事务中））

CREATE OR REPLACE PROCEDURE LOAD_DATA_TO_CONSOLIDATE (updatecount OUT NUMBER )
IS
  cnt number := 0;
  account_num MAIN_TABLE.ACCOUNT_NO%TYPE;
  CURSOR account_cursor IS
    SELECT distinct ACCOUNT_NO from TEMP_TABLE;
BEGIN
OPEN account_cursor;
    LOOP
        FETCH account_cursor INTO account_num;
        EXIT WHEN account_cursor%NOTFOUND;
        delete from MAIN where ACCOUNT_NO = account_num;
    insert into MAIN(ID,ACCOUNT_NO,FACT_ID,FACT_VALUE) select HIBERNATE_SEQUENCE.nextval,temp.ACCOUNT_NO,temp.VALUE from TEMP temp
    where ACCOUNT_NO = account_num;
        cnt := cnt + sql%rowcount;
    commit;
    END LOOP;
    updatecount := cnt;
    CLOSE account_cursor;
delete from TEMP; //removing all data
END LOAD_DATA_TO_CONSOLIDATE;

标签： javaoracleperformanceplsqlspring-integration

解决方案

从我的 Oracle 角度来看，您建议的选项都不是。原因如下：

  • 通过首先将数据加载到临时表中，然后将其复制到主表中，您将工作加倍
  • 逐行处理是缓慢的
  • 使用循环提交可能会导致 ORA-01555 错误
  • 删除（DELETE命令）总是比截断（TRUNCATE）慢

一个建议：为了避免临时表，使用外部表功能，它使用您的 CSV 文件，就好像它是一个普通的 Oracle 表一样。

这意味着一切都可以在两个语句中完成：

-- Delete rows from the MAIN table whose ACCOUNT_NO exists in the CSV file
delete from main m
where exists (select null 
              from external_table t
              where t.account_no = m.account_no
             );

-- Insert rows into the MAIN table
insert into main (col1, col2, ...)
select col1, col2 from external_table;

或者-这可能会表现最好-您可以为表中已经存在的UPDATE值而仅不存在的行；使用单个快速语句（也称为upsert）代替两个命令ACCOUNT_NOINSERTMERGE

merge into main m
  using (select t.account_no, t.col1, t.col2, ...
         from external_table t
        ) x
on m.account_no = x.account_no
when matched then update set m.col1 = x.col1,
                             m.col2 = x.col2, ...
when not matched then insert (account_no, col1, col2, ...)
                      values (x.account_no, x.col1, x.col2, ...);

使用MERGE，您无需在此处加载、从那里删除、从此处插入到那里……非常整洁，正如我所说，速度很快。根本不需要 PL/SQL。

推荐阅读