创建百万级记录备份表及迁移TABLEA旧记录至TABLEA_AUDIT的技术问询

针对你要处理的百万级嵌套表数据迁移需求，我来帮你完善语句并给出实用的优化建议：

• 需求1：创建一张支持百万级记录的备份表TABLEA_AUDIT
• 需求2：将TABLEA中满足COLUMN1 IS NOT NULL且TRUNC(UPDATED_DT) < '01-MAR-18'的旧记录迁移至备份表，原表包含1557951条记录，数据量1024MB，带有两个嵌套表TABLEA_STAGES和TABLEA_MODELS

你的原始语句存在语法位置错误和拼写问题，以下是修正后的可执行版本：

CREATE TABLE TABLEA_AUDIT
  NESTED TABLE TABLEA_STAGES STORE AS AUDIT_TABLEA_STAGES
  NESTED TABLE TABLEA_MODELS STORE AS AUDIT_TABLEA_MODELS
  NOT SUBSTITUTABLE AT ALL LEVELS
AS
SELECT *
FROM TABLEA
WHERE COLUMN1 IS NOT NULL 
  AND TRUNC(UPDATED_DT) < TO_DATE('01-MAR-18', 'DD-MON-RR');

修正说明： #

• 把NOT SUBSTITUTABLE AT ALL LEVELS移至表级别（这是针对嵌套表的不可替代属性设置，不能跟在列声明后）
• 修正了拼写错误COULMN1为COLUMN1
• 用TO_DATE()显式转换日期字符串，避免依赖会话的NLS_DATE_FORMAT配置，防止隐式转换引发的错误

考虑到原表数据量较大，还有嵌套表结构，建议做以下优化提升操作效率：

• 并行执行加速：如果数据库支持并行操作，可以给主表和嵌套表都设置并行度，同时在查询中加入并行提示：

  CREATE TABLE TABLEA_AUDIT PARALLEL 4
    NESTED TABLE TABLEA_STAGES STORE AS AUDIT_TABLEA_STAGES PARALLEL 4
    NESTED TABLE TABLEA_MODELS STORE AS AUDIT_TABLEA_MODELS PARALLEL 4
    NOT SUBSTITUTABLE AT ALL LEVELS
  AS
  SELECT /*+ PARALLEL(4) */ *
  FROM TABLEA
  WHERE COLUMN1 IS NOT NULL 
    AND TRUNC(UPDATED_DT) < TO_DATE('01-MAR-18', 'DD-MON-RR');
  

  并行度建议根据服务器CPU核心数调整，通常设置为核心数的1-2倍即可，避免资源过载。
• 分批迁移（可选）：如果一次性迁移导致CPU/IO占用过高，或者锁表时间过长，可以先创建空表，再分批插入数据：

  -- 第一步：创建与原表结构一致的空备份表
  CREATE TABLE TABLEA_AUDIT
    NESTED TABLE TABLEA_STAGES STORE AS AUDIT_TABLEA_STAGES
    NESTED TABLE TABLEA_MODELS STORE AS AUDIT_TABLEA_MODELS
    NOT SUBSTITUTABLE AT ALL LEVELS
  AS
  SELECT * FROM TABLEA WHERE 1=0;
  
  -- 第二步：分批插入数据，示例按日期分段，每次插入一个月的数据
  INSERT /*+ APPEND PARALLEL(4) */ INTO TABLEA_AUDIT
  SELECT *
  FROM TABLEA
  WHERE COLUMN1 IS NOT NULL 
    AND TRUNC(UPDATED_DT) BETWEEN TO_DATE('01-JAN-00', 'DD-MON-RR') AND TO_DATE('28-FEB-18', 'DD-MON-RR');
  COMMIT;
  

  这种方式可以降低单次操作的资源消耗，减少对业务的影响。
• 索引补建：备份表创建完成后，根据后续查询需求，给主表和嵌套表补建必要的索引，比如针对UPDATED_DT或COLUMN1创建索引，提升后续查询效率。

操作完成后，一定要验证数据一致性：

• 对比主表记录数：

  -- 备份表记录数
  SELECT COUNT(*) FROM TABLEA_AUDIT;
  -- 原表中满足迁移条件的记录数
  SELECT COUNT(*) FROM TABLEA WHERE COLUMN1 IS NOT NULL AND TRUNC(UPDATED_DT) < TO_DATE('01-MAR-18', 'DD-MON-RR');
  

• 验证嵌套表数据完整性：

  -- 备份表嵌套表TABLEA_STAGES的总记录数
  SELECT COUNT(*) FROM TABLEA_AUDIT t, TABLE(t.TABLEA_STAGES) s;
  -- 原表中对应嵌套表的总记录数
  SELECT COUNT(*) FROM TABLEA t, TABLE(t.TABLEA_STAGES) s WHERE COLUMN1 IS NOT NULL AND TRUNC(t.UPDATED_DT) < TO_DATE('01-MAR-18', 'DD-MON-RR');
  

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