按小时分区优化ETL存储过程资源占用问题咨询

针对全量ETL导致数据库资源占用过高的问题，按小时分区实现增量同步是完全可行的优化方案，以下是具体分析和实操建议：

一、分区规划说明 #

• 执行时段8:00-23:59对应16个小时区间，但无需手动预创建16个小时分区。使用数据库的自动区间分区（如Oracle的INTERVAL分区），系统会在插入对应小时数据时自动生成分区，更灵活且减少维护成本。
• 不需要在过程结束后删除当日分区：这些分区是当日数据的存储载体，保留才能保证数据完整性；后续可通过定期清理策略（比如保留7天数据）删除历史分区，避免存储冗余。

二、核心优化逻辑 #

将原有的「每日全量清空-插入」改为「按小时增量同步」：

1. 每次执行仅处理当前时间往前推1小时内的增量数据（适配每5分钟的执行频率，确保覆盖所有新增数据）
2. 对SOFT_CALLS的对应小时分区做增量更新（先删该小时旧数据，再插新数据，避免重复）
3. SAS端同步时，仅同步对应小时的分区数据，而非全表

三、具体改造步骤 #

1. 将SOFT_CALLS改为小时分区表 #

以Oracle为例，创建自动区间分区表的DDL示例：

CREATE TABLE SOFT_CALLS (
    CALLID VARCHAR2(100),
    START_TIME DATE,
    DURATION NUMBER,
    FIRST_QUESTION VARCHAR2(200),
    SECOND_QUESTION VARCHAR2(200),
    CLIENT_ID VARCHAR2(100),
    CONTRACT_ID VARCHAR2(100),
    CLIENT_DWH_ID VARCHAR2(100)
)
PARTITION BY RANGE (START_TIME)
INTERVAL (NUMTODSINTERVAL(1, 'HOUR'))
(
    -- 初始化起始分区（覆盖当日8点前的历史数据，可按需调整）
    PARTITION P_INIT VALUES LESS THAN (TO_DATE('2024-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'))
);

2. 修改ETL存储过程逻辑 #

改造后的过程仅处理最近1小时的增量数据，避免全量操作：

CREATE OR REPLACE PROCEDURE ETLT#SOFT_CALLS AS
    p_start_time DATE;
    p_end_time DATE;
BEGIN
    -- 定义当前处理的1小时时间区间
    p_end_time := TRUNC(SYSDATE, 'HH24');
    p_start_time := p_end_time - INTERVAL '1' HOUR;

    -- 1. 删除SOFT_CALLS中当前小时的旧数据
    DELETE FROM SOFT_CALLS
    WHERE START_TIME >= p_start_time AND START_TIME < p_end_time;
    COMMIT;

    -- 2. 插入当前小时的增量数据到对应分区
    INSERT /*+ append enable_parallel_dml parallel(8)*/
    INTO SOFT_CALLS(CALLID,
                START_TIME,
            DURATION,
            FIRST_QUESTION,
            SECOND_QUESTION,
            CLIENT_ID,
            CONTRACT_ID,
            CLIENT_DWH_ID)
    SELECT /*+ parallel(8)*/ 
        a.CALLID as CALLID,
        a.START_TIME as START_TIME,
        a.DURATION AS DURATION,
        b.FIRST_QUESTION AS FIRST_QUESTION,
        b.SECOND_QUESTION AS SECOND_QUESTION,
        a.CLIENT_ID AS CLIENT_ID,
        a.CONTRACT_ID AS CONTRACT_ID,
        sch.CLIENT_DWH_ID AS CLIENT_DWH_ID
    FROM CALL_DETAIL a
    LEFT JOIN DIALOGE_ONLINE b
      ON b.CALL_ID = a.CALL_ID
    LEFT JOIN MINT_HIST sch
      ON sch.DATE_INS >= TRUNC(SYSDATE)
      AND sch.ID = a.CONTRACT_ID
    WHERE a.START_TIME >= p_start_time AND a.START_TIME < p_end_time;
    COMMIT;

    -- 3. 同步到SAS端：增量更新对应小时数据
    DELETE FROM SASUSER.SOFT_CALLS@DWHSAS
    WHERE START_TIME >= p_start_time AND START_TIME < p_end_time;
    COMMIT;

    INSERT /*+ append enable_parallel_dml parallel(8)*/
    INTO SASUSER.SOFT_CALLS@DWHSAS
    SELECT * FROM SOFT_CALLS
    WHERE START_TIME >= p_start_time AND START_TIME < p_end_time;
    COMMIT;
END;
/

四、额外注意事项 #

• 并行度调整：小时级增量数据量远小于全量，建议降低并行度（如从16改为8），避免资源浪费。
• 分区维护：可创建定时任务，定期删除N天前的历史分区（比如每周执行一次），控制存储占用。
• 数据校验：新增分区后，需验证每小时的数据完整性，确保增量同步无遗漏。
• SAS端优化：若SAS端表支持分区，建议同步改为小时分区，进一步提升跨库同步效率。

内容的提问来源于stack exchange，提问作者Andrey Romanov