数据库里中间表频繁insert和delete可能导致大量碎片，比如t_mid里面插入大量数据，有status自动，然后处理按照status=0查找数据并处理后delete掉. ...这种频繁insert然后delete可能中间表只有几条数据，但是实际空间可能几个G，以oracle为例，如何避免这种中间表碎片问题，可以从业务流程设计和数据库侧考虑减少碎片，并且保证查找和处理的性能，比如这种中间表开发侧可能没有建索引，如何在有碎片情况下也能保障性能？

针对中间表频繁DML导致的碎片和性能问题，以下是Oracle数据库的综合解决方案：

一、业务流程优化方案

批量处理机制

采用批量提交（每1000-10000条提交一次）
使用DBMS_PARALLEL_EXECUTE进行并行处理
示例代码：

DECLARE
  CURSOR c_data IS 
  SELECT /*+ INDEX(t_mid idx_status) */ rowid 
  FROM t_mid 
  WHERE status = 0 
  FOR UPDATE SKIP LOCKED;
  
  TYPE rowid_table IS TABLE OF ROWID INDEX BY PLS_INTEGER;
  l_rowids rowid_table;
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data BULK COLLECT INTO l_rowids LIMIT 1000;
    EXIT WHEN l_rowids.COUNT = 0;
    
    FORALL i IN 1..l_rowids.COUNT
      DELETE FROM t_mid 
      WHERE rowid = l_rowids(i);
    
    COMMIT;
  END LOOP;
  CLOSE c_data;
END;

数据生命周期管理

采用时间分区策略（按小时/天分区）
使用时间戳字段+分区交换技术
处理流程图：

[新数据插入] -> [当前处理分区] 
-> [处理完成] -> [分区交换到历史表] 
-> [定期TRUNCATE历史分区]

二、数据库架构优化方案

表结构设计

-- 创建分区表
CREATE TABLE t_mid (
  id NUMBER,
  data CLOB,
  status NUMBER(1),
  create_time TIMESTAMP
)
PARTITION BY RANGE (create_time)
INTERVAL (NUMTODSINTERVAL(1, 'HOUR'))
(
  PARTITION p_init VALUES LESS THAN (TIMESTAMP '2023-01-01 00:00:00')
);
-- 创建复合索引
CREATE INDEX idx_t_mid ON t_mid(status, create_time) 
LOCAL COMPRESS 1;

存储参数优化

ALTER TABLE t_mid PCTFREE 5 PCTUSED 85;
ALTER TABLE t_mid STORAGE (INITIAL 64K NEXT 64K);
ALTER INDEX idx_t_mid REBUILD COMPRESS;

三、性能保障方案

智能索引策略

-- 创建函数索引处理热点数据
CREATE INDEX idx_hot_data ON t_mid(
  CASE WHEN status = 0 THEN 0 ELSE 1 END
);
-- 使用In-Memory选项
ALTER TABLE t_mid INMEMORY PRIORITY HIGH;

异步维护作业设计

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'night_maintenance',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN 
                       DBMS_REDEFINITION.can_redef_table(''SCHEMA'',''T_MID'');
                       DBMS_REDEFINITION.start_redef_table(...);
                       -- 在线重组逻辑
                       END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;BYHOUR=2',
    enabled         => TRUE);
END;

四、高级技术方案

In-Memory Column Store应用

ALTER TABLE t_mid 
INMEMORY 
MEMCOMPRESS FOR QUERY LOW 
PRIORITY CRITICAL;

混合分区策略

CREATE TABLE t_mid
(
  ...,
  active_flag VARCHAR2(1) GENERATED ALWAYS AS (
    CASE WHEN status = 0 THEN 'Y' ELSE 'N' END
  ) VIRTUAL
)
PARTITION BY LIST (active_flag)
SUBPARTITION BY RANGE (create_time)
(
  PARTITION p_active VALUES ('Y') 
  (SUBPARTITION sp_202301 VALUES LESS THAN (...)),
  PARTITION p_archive VALUES ('N') COMPRESS
);

五、监控维护方案

碎片监控脚本

SELECT 
  table_name,
  blocks,
  empty_blocks,
  num_rows,
  avg_row_len,
  ROUND((blocks*8192 - num_rows*avg_row_len)/1024/1024,2) fragment_mb
FROM dba_tables 
WHERE table_name = 'T_MID';

自动化维护策略

CREATE OR REPLACE TRIGGER maintain_trigger 
AFTER INSERT OR DELETE ON t_mid
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count 
  FROM v$session 
  WHERE program = 'AUTO_MAINTENANCE';
  
  IF v_count = 0 THEN
    DBMS_SCHEDULER.RUN_JOB('AUTO_MAINTENANCE_JOB');
  END IF;
END;

实施建议：

优先测试全局临时表方案，评估对现有业务的影响
实施分区策略前需评估数据量级和访问模式
索引方案建议配合SQL性能分析工具实施
维护操作建议在业务低峰期进行
建议使用Oracle 19c及以上版本的Auto Indexing特性

性能对比参考：

分区表 vs 普通表：TPS提升3-5倍
批量处理 vs 单条处理：处理速度提升10-20倍
In-Memory技术：查询性能提升10-100倍
合理压缩：存储空间节省40-70%

ITPub博客

oracle表频繁DML导致碎片如何保障性能

最新文章