选择和删除在oracle存储过程中需要更多时间

我们在oracle表中拥有2000万条记录 . 我们正在尝试将1400万条记录转移到另一个模式的另一个表中 .

示例： Table1 在 schema1 上有2000万条记录，并且有一千四百万条记录应该在 schema2 上转移到 Table2 .

我们使用光标选择，插入和删除记录 . 将记录插入 table2 后，我们将删除 table1 中的同一组记录 .

我们使用以下存储过程来实现：

CREATE OR REPLACE PROCEDURE "SCHEMA1"."ARCHIVE"(FROM_ARCHIVE timestamp, TO_ARCHIVE timestamp,PROCESS_DATE_ARCHIVE timestamp)
AS
    CURSOR C_EXTRACT IS
        SELECT
            COL1,
            COL2,
            COL3,
            .
            .
            .
            .
            COL65
        FROM
            TABLE1
        WHERE 
            UPPER(COL5) ='XXX' AND COL8 >= FROM_ARCHIVE AND COL8 <= TO_ARCHIVE AND UPPER(COL24) NOT IN ('YYY','ZZZ'); 

    FOR C_EXTRACT_REC IN C_EXTRACT
    LOOP
        BEGIN
            INSERT
            INTO
                SCHEMA2.TABLE2
                (
                    COL1,
                    COL2,
                    COL3,
                    .
                    .
                    .
                    .
                    COL65
                )
                VALUES
                (
                    C_EXTRACT_REC.COL1,
                    C_EXTRACT_REC.COL2,
                    C_EXTRACT_REC.COL3,
                    .
                    .
                    .
                    .                   
                    C_EXTRACT_REC.COL65
                );
        END;
    END LOOP;


    FOR C_EXTRACT_REC_DEL IN C_EXTRACT
    LOOP
        BEGIN
            DELETE
            FROM
                TABLE1
            WHERE
                COL1 =C_EXTRACT_REC_DEL.COL1;
        END;
    END LOOP;

EXCEPTION
WHEN OTHERS THEN
    ROLLBACK;
    COMMIT;
END;

选择和删除需要更多时间来执行它 . 有没有办法调整性能，以便选择和删除查询执行时间更短 .

使用此存储过程，选择，插入和删除12条记录需要12分钟，并且选择，插入和删除1条lac记录需要30分钟 .

Note ：我们使用 partitioning 列和 sub-partitioning 列使用 TABLE1 中的列表 .

请帮助我们 .

2 回答

您正在使用28M个别语句来锤击数据库 . SQL是一种集处理语言 . 最好在程序中使用2个语句，如下所示：[未经测试]

create or replace procedure schema1.archive
( from_archive timestamp
, to_archive timestamp
, process_date_archive timestamp
)
as
begin
  insert into schema2.table2
  ( col1,
    col2,
    col3,
    .
    .
    .
    .
    col65
  )
  select col1
  ,      col2
  ,      col3
  ,      .
         .
         .
         .
         col65
  from   table1
  where  upper(col5) ='XXX'
  and    col8 >= from_archive
  and    col8 <= to_archive
  and    upper(col24) not in ('YYY','ZZZ')
  ; 
  delete table1
  where  upper(col5) ='XXX'
  and    col8 >= from_archive
  and    col8 <= to_archive
  and    upper(col24) not in ('YYY','ZZZ')
  ;
end;

这个过程实现了相同，但更快 .

问候，
抢 .

回复于 2024-05-13T09:14:42+08:00

好像你正在转移大部分数据 . 如果是，请执行以下操作，而不是删除：

create table3 as
  select * from table1
  where  not (upper(col5) ='XXX'
  and         col8 >= from_archive
  and         col8 <= to_archive
  and         upper(col24) not in ('YYY','ZZZ'));

drop table1;
alter table3 rename to table1;
-- Recreate indexes, foreign keys, etc.

同时从另一个答案中获取建议，并将insert2插入table2中 .

回复于 2024-05-13T09:14:42+08:00

选择和删除在oracle存储过程中需要更多时间 - Perfornamce调整

2 回答

相关问题