Oracle大表数据清理优化与注意事项详解

一、性能优化策略

1. 批量处理优化

• 批量大小选择：

  • 小批量(1,000-10,000行)：减少UNDO生成，但需要更多提交次数
  • 中批量(10,000-100,000行)：平衡性能与资源消耗
  • 大批量(100,000+行)：适合高配置环境，但需监控资源使用

• 批量删除示例：

BEGIN
  FOR i IN 1..100 LOOP
    EXECUTE IMMEDIATE 'DELETE /*+ PARALLEL(4) */ FROM 大表 
                      WHERE ROWNUM <= 100000 
                      AND 条件 AND MOD(id,100)=:i' USING i;
    COMMIT;
    DBMS_LOCK.SLEEP(0.1); -- 控制速度
  END LOOP;
END;

2. 并行处理优化

• 并行查询设置：

  ALTER SESSION ENABLE PARALLEL DML;
  ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
  

• 并行删除示例：

  DELETE /*+ PARALLEL(大表 8) */ FROM 大表 
  WHERE 创建时间 < ADD_MONTHS(SYSDATE, -36)
  AND ROWNUM <= 1000000;
  

3. 资源控制优化

• UNDO表空间管理：

  • 增大UNDO表空间：ALTER TABLESPACE undo_ts ADD DATAFILE size 10G
  • 设置UNDO保留期：ALTER SYSTEM SET UNDO_RETENTION=900 (秒)

• 临时表空间优化：

  -- 检查临时表空间使用
  SELECT tablespace_name, file_name, bytes/1024/1024 MB 
  FROM dba_temp_files;
  
  -- 添加临时文件
  ALTER TABLESPACE temp ADD TEMPFILE '/path/to/temp02.dbf' SIZE 20G;
  

4. 索引优化策略

• 删除前禁用索引：

  -- 查询表索引
  SELECT index_name FROM user_indexes WHERE table_name='大表';
  
  -- 禁用索引
  ALTER INDEX idx_name UNUSABLE;
  
  -- 删除后重建索引
  ALTER INDEX idx_name REBUILD TABLESPACE index_ts;
  

• 选择性重建索引：

  -- 只重建碎片化严重的索引
  SELECT index_name, blevel, leaf_blocks, 
         (leaf_blocks*8)/1024/1024 "Size(GB)",
         (select count(*) from 大表) "Table_Rows"
  FROM user_indexes 
  WHERE