Oracle普通表改造分区表的方法总结

Oracle普通表改造分区表的方法总结本文章主要讲述alter table modify改造方法在线重定义改造方式请参考文章《Oracle表在线重定义操作总结DBMS_REDEFINITION》CTAS方式请参考文章《Oracle CTASrename 表重建方法操作总结》一、改造方法总结1、DBMS_REDEFINITION在线重定义方式2、create table nologging parallel as select /* parallel(a 4) */ 并发快速创建新表分区形式然后表名互换3、expdp/impdp方法非在线的改造方式4、直接alter table xxx modify 方式将非分区表改为分区表12.2以上版本才有的功能。5、将表交换为一个分区表中的一个分区然后split这个分区然后表名互换二、五种改造方法对比方法核心原理优点缺点核心要点与注意事项方法一DBMS_REDEFINITION在线重定义创建一个新的分区表中间表通过内置包将源表数据在线拷贝至中间表并在切换时通过原子操作交换表名。真正的在线操作业务几乎无感知支持回退 (ABORT_REDEF_TABLE)可多次同步 (SYNC_INTERIM_TABLE) 控制切换时间支持列映射、列转换。需要额外1倍表空间的临时表空间源表必须有主键或可用ROWID操作步骤较多复杂度高主键列不能修改无法采用NOLOGGING完成后必须手动收集统计信息。需注意• 必须在同一用户下进行•SYS/SYSTEM用户下的表不支持• 不支持含LONG、BFILE、域索引的表• 不支持含物化视图日志的表需先删除• 重定义期间禁止FLASHBACK TABLE/QUERY• 表列的默认值需提前在中间表手工创建。方法二CREATE TABLE ... AS SELECT(CTAS)利用CREATE TABLE ... NOLOGGING PARALLEL AS SELECT并发创建一个新的分区表然后通过表名互换完成切换。创建速度快NOLOGGING PARALLEL产生的日志最少语法简单易于理解不受主键、物化视图日志等限制。业务需停止不能有DML必须先将表设为只读ALTER TABLE ... READ ONLY所有依赖对象索引、约束、触发器、权限、视图、存储过程等需全部手工重建工作量大且容易遗漏完成后需手动收集统计信息。建议• 在11g及以上版本可设置表为只读• 建议将重建依赖对象的脚本提前准备好并测试通过• 适合在维护窗口进行。方法三数据泵 (expdp/impdp)通过expdp导出源表数据再通过impdp导入到一个预先创建好的分区表中。速度相对较快语法简单不受主键、物化视图日志等限制。业务需停止不能有DML必须先将表设为只读所有依赖对象需全部手工重建工作量大完成后需手动收集统计信息。建议• 适合数据量极大的场景• 可配合NETWORK_LINK参数实现远程迁移• 与方法二类似属于非在线方式。方法四ALTER TABLE ... MODIFY(12.2)Oracle 12.2 及以上版本提供的原生在线DDL直接将普通表转换为分区表并可选择转换时对索引的处理方式。语法最简洁一条DDL完成依赖对象索引、约束、触发器、默认值、权限、视图等全部自动保留无需额外表空间支持ONLINE模式业务几乎无感知。仅限 Oracle 12.2不支持域索引不支持含物化视图日志的表大表会产生大量REDO日志10G以上大表需谨慎评估转换后需手动收集统计信息。关键技巧• 建议提前删除非必要索引转换后再重建以减少REDO并加快速度• 参考分区Reference Partitioning的在线转换需 19.12• 内部仍需移动数据大表需充分测试。方法五EXCHANGE SPLIT分区交换创建一个单分区表通过EXCHANGE PARTITION与普通表交换仅改数据字典再通过SPLIT PARTITION将这个大分区拆分为多个分区。EXCHANGE操作极快仅修改数据字典不物理移动数据不需要额外的数据复制。SPLIT大分区时耗时非常长需移动数据所有依赖对象需全部手工重建工作量大操作步骤多容易出错转换后需手动收集统计信息。现状• 此方法步骤繁琐且存在性能瓶颈•如今已基本不使用已被方法一或方法四取代。三、改造方法选择推荐推荐场景推荐方法生产环境业务不能停且数据库版本在 12.2方法四(ALTER TABLE ... MODIFY ONLINE) — 首选最省心生产环境业务不能停但数据库版本低于 12.2方法一(DBMS_REDEFINITION) — 唯一可靠的在线方式有维护窗口业务可停表数据量巨大 (TB级)方法二(CTAS) 或方法三(expdp/impdp) — 速度最快配合NOLOGGING 并行日志最少追求操作最简版本满足要求表数据量中等方法四— 一条命令搞定所有依赖对象临时测试环境快速验证分区效果方法二(CTAS) — 最直接方便四、alter table modify改造语法ALTER TABLE ... MODIFY命令是从Oracle 12c R212.2版本开始引入的特性用于直接将一个普通的堆组织表heap-organized table转换为分区表特别注意对于大表建议提前删除非必要索引转换后重建减少redo量加快整体速度ALTER TABLE table_name MODIFY PARTITION BY { RANGE (column_list) [ INTERVAL (expr) ] ( partition_definition [, partition_definition ]... ) | LIST (column) ( partition_definition [, partition_definition ]... ) | HASH (column) { PARTITIONS num | ( partition_definition [, partition_definition ]... ) } } [ SUBPARTITION BY ... ] -- 可选的子分区定义 [ ONLINE ] -- 可选允许在线转换 [ UPDATE INDEXES ( -- 可选用于精细控制索引转换 index_name { LOCAL | GLOBAL [ partition_definition ] } [, ...] ) ];语法组成部分详解PARTITION BY子句必需定义表的分区策略与CREATE TABLE语法类似。分区类型语法示例说明范围分区 (RANGE)PARTITION BY RANGE (id) (PARTITION p1 VALUES LESS THAN (100), ...)最常用适用于按日期、ID等有序字段分区。间隔分区 (INTERVAL)PARTITION BY RANGE (created_date) INTERVAL (NUMTODSINTERVAL(1,DAY)) (PARTITION p_init VALUES LESS THAN (DATE 2023-01-01))RANGE分区的扩展可自动创建新分区。列表分区 (LIST)PARTITION BY LIST (region) (PARTITION p1 VALUES (EAST,WEST), ...)适用于枚举值字段。哈希分区 (HASH)PARTITION BY HASH (id) PARTITIONS 4适用于数据均匀分布的场景。SUBPARTITION BY子句可选用于创建复合分区表对每个主分区再分子分区常见组合有RANGE-HASH、RANGE-LIST等。ALTER TABLE sales MODIFY PARTITION BY RANGE (sale_date) ( PARTITION p1 VALUES LESS THAN (DATE 2023-01-01), PARTITION p2 VALUES LESS THAN (DATE 2023-02-01) ) SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 8; -- 每个主分区再分为8个哈希子分区ONLINE关键字强烈推荐ONLINE关键字允许在不阻塞DML操作的情况下在线转换表。其内部机制复杂会创建日志表记录变更并通过批量迁移完成转换。ALTER TABLE your_table MODIFY PARTITION BY RANGE (id) (...) ONLINE;UPDATE INDEXES子句关键此子句用于精细控制表上现有索引的转换方式。如果不指定Oracle会按默认规则转换一定要慎重尽量避免这样默认不可控。如果确实有索引需要转换强烈建议明确指定索引的转换方式UPDATE INDEXES ( idx_name1 LOCAL, -- 转为本地分区索引 idx_name2 GLOBAL, -- 转为非分区全局索引 idx_name3 GLOBAL PARTITION BY RANGE (col) (PARTITION p_idx VALUES LESS THAN (MAXVALUE)) )简单示例1修改为普通分区类型类似如下 ALTER TABLE employees_convert MODIFY PARTITION BY RANGE (employee_id) INTERVAL (100) ( PARTITION P1 VALUES LESS THAN (100), PARTITION P2 VALUES LESS THAN (500) ) ONLINE -online 表示在线不指定为offline锁表 UPDATE INDEXES ( IDX1_SALARY LOCAL, IDX2_EMP_ID GLOBAL PARTITION BY RANGE (employee_id) ( PARTITION IP1 VALUES LESS THAN (MAXVALUE)) ); 2修改为subpartition分区类似如下 alter table TEST_TAB modify partition by range (created_date) subpartition by hash (id)( partition TEST_TAB_2021 values less than (to_date(01-jan-2022,dd-mon-yyyy)) ( subpartition TEST_TAB_SUB_PART_2021_1, subpartition TEST_TAB_SUB_PART_2021_2, subpartition TEST_TAB_SUB_PART_2021_3, subpartition TEST_TAB_SUB_PART_2021_4 ), partition TEST_TAB_2022 values less than (to_date(01-jan-2023,dd-mon-yyyy)) ( subpartition TEST_TAB_SUB_PART_2022_1, subpartition TEST_TAB_SUB_PART_2022_2, subpartition TEST_TAB_SUB_PART_2022_3, subpartition TEST_TAB_SUB_PART_2022_4 ), partition TEST_TAB_2023 values less than (to_date(01-jan-2024,dd-mon-yyyy)) ( subpartition TEST_TAB_SUB_PART_2023_1, subpartition TEST_TAB_SUB_PART_2023_2, subpartition TEST_TAB_SUB_PART_2023_3, subpartition TEST_TAB_SUB_PART_2023_4 ) ) online update indexes ( test_tab_pk global, test_tab_created_date_idx local );五、测试案例1. 实验环境准备1.1 创建测试表并加载大量数据-- 创建测试表 t包含 500 万行数据 CREATE TABLE t ( id NUMBER, col1 NUMBER, col2 NUMBER, col3 NUMBER, col4 NUMBER, padding VARCHAR2(100) ); -- 插入 200 万行 INSERT /* APPEND */ INTO t SELECT LEVEL, MOD(LEVEL, 1000), MOD(LEVEL, 500), ROUND(DBMS_RANDOM.VALUE(1, 10000)), ROUND(DBMS_RANDOM.VALUE(1, 10000)), LPAD(X, 100, X) FROM DUAL CONNECT BY LEVEL 2000000; COMMIT; -- 收集统计信息 EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, T);1.2 创建多种类型的索引-- 1. 前缀索引索引列包含分区键 id CREATE INDEX idx_prefix ON t(id); -- 2. 非前缀普通索引索引列不包含分区键 CREATE INDEX idx_nonprefix ON t(col1, col2); -- 3. 普通索引 CREATE INDEX idx_col3 ON t(col3); -- 4. 唯一索引包含分区键用于观察全局唯一性约束 CREATE UNIQUE INDEX idx_unique ON t(id, col4);1.3 检查索引初始状态set linesize 200 pagesize 999 col INDEX_NAME format a15 col INDEX_TYPE format a15 col UNIQUENESS format a15 SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS, PARTITIONED FROM USER_INDEXES WHERE TABLE_NAME T; INDEX_NAME INDEX_TYPE UNIQUENESS PARTITION --------------- --------------- --------------- --------- IDX_UNIQUE NORMAL UNIQUE NO IDX_COL3 NORMAL NONUNIQUE NO IDX_NONPREFIX NORMAL NONUNIQUE NO IDX_PREFIX NORMAL NONUNIQUE NO2. 场景一默认行为省略UPDATE INDEXES2.1 执行分区转换-- 不指定 UPDATE INDEXES由 Oracle 自动决定索引转换方式 set timing on ALTER TABLE t MODIFY PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (1000000), PARTITION p2 VALUES LESS THAN (2000000), PARTITION p3 VALUES LESS THAN (3000000), PARTITION p4 VALUES LESS THAN (4000000), PARTITION p5 VALUES LESS THAN (MAXVALUE) );2.2 检查转换后的索引状态-- 查看索引是否分区以及类型 SELECT INDEX_NAME, PARTITIONED, STATUS FROM USER_INDEXES WHERE TABLE_NAME T; INDEX_NAME PAR STATUS --------------- --- -------- IDX_PREFIX YES N/A IDX_NONPREFIX NO VALID IDX_COL3 NO VALID IDX_UNIQUE YES N/A -- 查看分区索引的各分区状态 col partition_name format a30 SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME IN (IDX_PREFIX, IDX_UNIQUE) ORDER BY INDEX_NAME, PARTITION_NAME; INDEX_NAME PARTITION_NAME STATUS --------------- ------------------------------ -------- IDX_PREFIX P1 USABLE IDX_PREFIX P2 USABLE IDX_PREFIX P3 USABLE IDX_PREFIX P4 USABLE IDX_PREFIX P5 USABLE IDX_UNIQUE P1 USABLE IDX_UNIQUE P2 USABLE IDX_UNIQUE P3 USABLE IDX_UNIQUE P4 USABLE IDX_UNIQUE P5 USABLE2.3 结果不指定省略UPDATE INDEXESOracle会按默认规则转换表上的索引索引名分区状态说明IDX_PREFIXYES因为包含分区键id自动转换为本地分区索引IDX_NONPREFIXNO不包含分区键自动保留为非分区全局索引IDX_COL3NO不包含分区键自动保留为非分区全局索引IDX_UNIQUEYES唯一索引包含分区键自动转换为本地分区索引但若包含唯一约束需注意是否有跨分区唯一性3. 场景二各种选项下的redo日志产生量主要考虑online和非online模式以及是否更新索引这几种情况下的redo日志产生量0、准备工作重建初始表与场景一完全一致为避免干扰重新创建相同的表结构和数据或者将表改回普通表并重新创建索引。-- 回退方法若允许 DROP TABLE t PURGE; -- 重新执行 1.1 和 1.2 步骤重建 -- 索引是否创建根据场景来决定1、有索引非online模式redo日志产生量-- 不指定 UPDATE INDEXES由 Oracle 自动决定索引转换方式 set timing on ALTER TABLE t MODIFY PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (1000000), PARTITION p2 VALUES LESS THAN (2000000), PARTITION p3 VALUES LESS THAN (3000000), PARTITION p4 VALUES LESS THAN (4000000), PARTITION p5 VALUES LESS THAN (MAXVALUE) ); col name format a20 select a.name,b.value from v$statname a,v$mystat b where a.statistic# b.statistic# and a.nameredo size; 非online模式下有索引 NAME VALUE -------------------- ---------- redo size 19081802、无索引非online模式redo日志产生量set timing on ALTER TABLE t MODIFY PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (1000000), PARTITION p2 VALUES LESS THAN (2000000), PARTITION p3 VALUES LESS THAN (3000000), PARTITION p4 VALUES LESS THAN (4000000), PARTITION p5 VALUES LESS THAN (MAXVALUE) ); col name format a20 select a.name,b.value from v$statname a,v$mystat b where a.statistic# b.statistic# and a.nameredo size; 非online模式下无索引 NAME VALUE -------------------- ---------- redo size 6830443、无索引online模式redo日志产生量set linesize 200 pagesize 999 col INDEX_NAME format a15 col INDEX_TYPE format a15 col UNIQUENESS format a15 col name format a20 select a.name,b.value from v$statname a,v$mystat b where a.statistic# b.statistic# and a.nameredo size; ALTER TABLE t MODIFY PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (1000000), PARTITION p2 VALUES LESS THAN (2000000), PARTITION p3 VALUES LESS THAN (3000000), PARTITION p4 VALUES LESS THAN (4000000), PARTITION p5 VALUES LESS THAN (MAXVALUE) ) online; select a.name,b.value from v$statname a,v$mystat b where a.statistic# b.statistic# and a.nameredo size; online模式下无索引 NAME VALUE -------------------- ---------- redo size 13270324、有索引online模式redo日志产生量set linesize 200 pagesize 999 col INDEX_NAME format a15 col INDEX_TYPE format a15 col UNIQUENESS format a15 col name format a20 select a.name,b.value from v$statname a,v$mystat b where a.statistic# b.statistic# and a.nameredo size; ALTER TABLE t MODIFY PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (1000000), PARTITION p2 VALUES LESS THAN (2000000), PARTITION p3 VALUES LESS THAN (3000000), PARTITION p4 VALUES LESS THAN (4000000), PARTITION p5 VALUES LESS THAN (MAXVALUE) ) online UPDATE INDEXES ( idx_prefix LOCAL, idx_nonprefix GLOBAL, IDX_COL3 LOCAL, idx_unique GLOBAL ); select a.name,b.value from v$statname a,v$mystat b where a.statistic# b.statistic# and a.nameredo size; online模式下有索引 NAME VALUE -------------------- ---------- redo size 2528856结果对比modify改造模式redo日志量执行速度非online模式下有索引1908180第三非online模式下无索引683044第一online模式下无索引1327032第二online模式下有索引2528856第四结论如果条件允许建议提前删除非必要索引转换后再重建以减少REDO并加快整体速度