从12.2开始,通过MODIFY TABLE可以在线实现普通表转分区表。
一、 创建测试表:
DROP TABLE t1 PURGE; CREATE TABLE t1 ( id NUMBER, description VARCHAR2(50), created_date DATE, CONSTRAINT t1_pk PRIMARY KEY (id) ); CREATE INDEX t1_created_date_idx ON t1(created_date); INSERT INTO t1 SELECT level, 'Description for ' || level, ADD_MONTHS(TO_DATE('01-JAN-2017', 'DD-MON-YYYY'), -TRUNC(DBMS_RANDOM.value(1,4)-1)*12) FROM dual CONNECT BY level <= 10000; COMMIT; |
二、查看表数据:
SELECT created_date, COUNT(*) FROM t1 GROUP BY created_date ORDER BY 1; CREATED_D COUNT(*) --------- ---------- 01-JAN-15 3340 01-JAN-16 3290 01-JAN-17 3370 |
三、我们使用ALTER table…将表转换为分区表。。。MODIFY命令。以下是此操作的一些基本示例。添加ONLINE关键字可以在线完成操作,这意味着大多数DML操作可以在转换过程中继续。
-- Online operation. ALTER TABLE t1 MODIFY PARTITION BY RANGE (created_date) ( PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')), PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')), PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) ) ONLINE; |
四、我们收集统计数据并检查表分区。我们可以看到数据已经按预期在分区之间进行了分割。
-- Gather statistics. EXEC DBMS_STATS.gather_table_stats(NULL, 'T1'); -- Check table partitions. SELECT table_name, partition_name, num_rows FROM user_tab_partitions ORDER BY 1,2; TABLE_NAME PARTITION_NAME NUM_ROWS -------------------- -------------------- ---------- T1 T1_PART_2015 3340 T1 T1_PART_2016 3290 T1 T1_PART_2027 3370 SQL> |
五、当我们检查索引时,我们会看到CREATED_DATE列上的索引已转换为本地分区索引。默认情况下,所有带前缀的索引(列列表中具有分区键的索引)都将转换为本地分区索引。操作结束时,所有索引都处于有效状态。
-- Check indexes. SELECT index_name, partitioned, status FROM user_indexes ORDER BY 1; INDEX_NAME PARTITIONED STATUS -------------------- ----------- -------- T1_CREATED_DATE_IDX YES N/A T1_PK NO VALID SQL> -- Check index partitions. SELECT index_name, partition_name, status FROM user_ind_partitions ORDER BY 1,2; INDEX_NAME PARTITION_NAME STATUS -------------------- -------------------- -------- T1_CREATED_DATE_IDX T1_PART_2015 USABLE T1_CREATED_DATE_IDX T1_PART_2016 USABLE T1_CREATED_DATE_IDX T1_PART_2017 USABLE SQL> |
六、脱机操作类似,但我们省略了ONLINE关键字。结果将与在线操作相同,但DML在操作期间不可用。
-- Offline operation. ALTER TABLE t1 MODIFY PARTITION BY RANGE (created_date) ( PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')), PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')), PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) ); |
七、我们可以使用UPDATE INDEXES子句来影响索引转换。这可以指示作为操作的一部分要重建的索引的类型,以及一些存储参数。在下面的示例中,我们希望CREATED_DATE列上的索引保持为全局索引。
-- Online operation with modification of index partitioning. ALTER TABLE t1 MODIFY PARTITION BY RANGE (created_date) ( PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')), PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')), PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) ) ONLINE UPDATE INDEXES ( t1_pk GLOBAL, t1_created_date_idx GLOBAL ); |
八、运行完上一个示例后,我们可以看到索引都是未分区的,因此没有索引分区。
-- Check indexes. SELECT index_name, partitioned, status FROM user_indexes ORDER BY 1; INDEX_NAME PARTITIONED STATUS -------------------- ----------- -------- T1_CREATED_DATE_IDX NO VALID T1_PK NO VALID SQL> -- Check index partitions. SELECT index_name, partition_name, status FROM user_ind_partitions ORDER BY 1,2; no rows selected SQL> |
九、原始表也可以使用ALTER table。。。MODIFY命令。在本例中,我们将原始表转换为范围哈希分区表。
ALTER TABLE t1 MODIFY PARTITION BY RANGE (created_date) SUBPARTITION BY HASH (id)( PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')) ( SUBPARTITION t1_sub_part_2015_1, SUBPARTITION t1_sub_part_2015_2, SUBPARTITION t1_sub_part_2015_3, SUBPARTITION t1_sub_part_2015_4 ), PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')) ( SUBPARTITION t1_sub_part_2016_1, SUBPARTITION t1_sub_part_2016_2, SUBPARTITION t1_sub_part_2016_3, SUBPARTITION t1_sub_part_2016_4 ), PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) ( SUBPARTITION t1_sub_part_2017_1, SUBPARTITION t1_sub_part_2017_2, SUBPARTITION t1_sub_part_2017_3, SUBPARTITION t1_sub_part_2017_4 ) ) ONLINE UPDATE INDEXES ( t1_pk GLOBAL, t1_created_date_idx LOCAL ); |
十、可以使用以下查询显示表的子分区和分区索引。
COLUMN table_name FORMAT A20 COLUMN partition_name FORMAT A20 COLUMN subpartition_name FORMAT A20 SELECT table_name, partition_name, subpartition_name FROM user_tab_subpartitions ORDER BY 1,2, 3; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME -------------------- -------------------- -------------------- T1 T1_PART_2015 T1_SUB_PART_2015_1 T1 T1_PART_2015 T1_SUB_PART_2015_2 T1 T1_PART_2015 T1_SUB_PART_2015_3 T1 T1_PART_2015 T1_SUB_PART_2015_4 T1 T1_PART_2016 T1_SUB_PART_2016_1 T1 T1_PART_2016 T1_SUB_PART_2016_2 T1 T1_PART_2016 T1_SUB_PART_2016_3 T1 T1_PART_2016 T1_SUB_PART_2016_4 T1 T1_PART_2017 T1_SUB_PART_2017_1 T1 T1_PART_2017 T1_SUB_PART_2017_2 T1 T1_PART_2017 T1_SUB_PART_2017_3 T1 T1_PART_2017 T1_SUB_PART_2017_4 SQL> COLUMN index_name FORMAT A20 COLUMN partition_name FORMAT A20 COLUMN subpartition_name FORMAT A20 SELECT index_name, partition_name, subpartition_name, status FROM user_ind_subpartitions ORDER BY 1,2; INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS -------------------- -------------------- -------------------- -------- T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_1 USABLE T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_2 USABLE T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_3 USABLE T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_4 USABLE T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_1 USABLE T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_2 USABLE T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_4 USABLE T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_3 USABLE T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_1 USABLE T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_3 USABLE T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_2 USABLE T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_4 USABLE SQL> |