oracle 大表（1亿以上）迁移笔记一、二

sql
CREATE OR REPLACE PROCEDURE PRO_EMR_BL_DLNR_TOLS AS
    V_DATE          DATE;
    -- 全局控制参数：单次任务最大处理条数
    V_ONCE_MAX     PLS_INTEGER := 1000000;
    -- 每批批量处理提交条数
    V_BATCH_SIZE   CONSTANT PLS_INTEGER := 1000;
BEGIN
    -- 设定保留近90天热数据
    V_DATE := TRUNC(SYSDATE) - 90;

    -- 迁移期间临时关闭日志，提升插入删除性能
    BEGIN EXECUTE IMMEDIATE 'ALTER TABLE emr_bl_dlnr NOLOGGING'; EXCEPTION WHEN OTHERS THEN NULL; END;
    BEGIN EXECUTE IMMEDIATE 'ALTER TABLE emr_bl_dlnr_ls NOLOGGING'; EXCEPTION WHEN OTHERS THEN NULL; END;

    DECLARE
        -- 游标筛选需归档的主键数据：住院+门诊超期数据
        CURSOR C_DATA IS
            SELECT rid AS RID
            FROM (
                -- 住院超期数据筛选
                SELECT r.jlxh AS RID
                  FROM emr_bl_dlnr r
                 WHERE r.zymz = 1
                   AND EXISTS (select 1
                          from zy_brry y
                         where y.zyh = r.jzhm
                           and y.cypb = 8
                           and y.cyrq < v_date )
                UNION ALL
                -- 门诊超期数据筛选
                SELECT r.jlxh AS RID
                  FROM emr_bl_dlnr r
                 WHERE r.zymz = 2
                   AND EXISTS (select 1
                          from ys_mz_jzls l
                         where l.jzxh = to_number(r.jzhm)
                           and l.kssj < v_date )
            )
            WHERE ROWNUM <= V_ONCE_MAX;

        TYPE T_RIDS IS TABLE OF C_DATA%ROWTYPE INDEX BY BINARY_INTEGER;
        V_RIDS   T_RIDS;
    BEGIN
        OPEN C_DATA;