签到成功

知道了

CNDBA社区CNDBA社区

Oracle 12c 新特性 --- 将非分区表在线转换到分区表

2017-08-26 18:27 2834 0 原创 Oracle 12c
作者： leo
本文链接：http://www.cndba.cn/leo1990/article/2176

概念 http://www.cndba.cn/leo1990/article/2176

http://www.cndba.cn/leo1990/article/2176

http://www.cndba.cn/leo1990/article/2176 

Nonpartitioned tables can be converted to partitioned tables online. Indexes are maintained as part of this operation and can be partitioned as well. The conversion has no impact on the ongoing DML operations.
可以将非分区表转换为在线分区表。索引作为该操作的一部分被维护，也可以被分区。转换对正在进行的DML操作没有影响。

The online conversion of a nonpartitioned table to a partitioned table enables any application to adopt partitioning without application downtime. Customers can adopt partitioning for any system and evolve tables as needed to benefit from the partitioning for large tables.
将非分区表的在线转换到分区表可以使任何应用程序在不使用应用程序停机的情况下采用分区。客户可以通过对任何系统进行分区，并根据需要对表进行演化，以从大型表的分区中获益。

限制：
有一些与此功能相关的限制。
它不能被用来分割一个索引有序的表(IOT)。
如果表有域索引，就不能使用它。
您只能在脱机模式下将表转换为reference-partitioned子表。

http://www.cndba.cn/leo1990/article/2176

实验 http://www.cndba.cn/leo1990/article/2176

1 创建表并插入数据，创建索引

[oracle@host1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 26 17:51:26 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> DROP TABLE t1 PURGE;
DROP TABLE t1 PURGE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE t1 (
  id           NUMBER,
  description  VARCHAR2(50),
  created_date DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id)
); 

Table created.

SQL> CREATE INDEX t1_created_date_idx ON t1(created_date);SQL> 

Index created.

SQL> 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 <= 1000;

1000 rows created.

SQL> COMMIT;

Commit complete.

2 我们可以看到数据被分配在3个时间段内。

SQL> SELECT created_date, COUNT(*)
FROM   t1
GROUP BY created_date
ORDER BY 1; 

CREATED_DATE   COUNT(*)
------------ ----------
01-JAN-15	    322
01-JAN-16	    355
01-JAN-17	    323

3 分区表 我们可以使用ALTER TABLE ... MODIFY将表转换为分区表。下面是这个操作的一些基本示例。添加在线关键字允许在网上完成操作。
3.1 基本的离线操作。
SQL> 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'))
  );  

Table altered.

SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> SELECT table_name, partition_name
FROM   user_tab_partitions where TABLE_NAME ='T1'
ORDER BY 1,2;

TABLE_NAME	         PARTITION_NAME
-------------------- --------------------
T1		             T1_PART_2015
T1		             T1_PART_2016
T1		             T1_PART_2017


3.2在线分区表

SQL> 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;  6  

Table altered.

SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> SELECT table_name, partition_name
FROM   user_tab_partitions where TABLE_NAME ='T1'
ORDER BY 1,2;  2    3  

TABLE_NAME	         PARTITION_NAME
-------------------- --------------------
T1		             T1_PART_2015
T1		             T1_PART_2016
T1		             T1_PART_2017


3.3在线操作，修改索引分区。
SQL> 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 LOCAL
  );

Table altered.


--在运行最后一个示例之后，我们可以看到表和分区索引的新分区

SQL> COLUMN table_name FORMAT A20
SQL> COLUMN table_name FORMAT A20
SQL> SELECT table_name, partition_name
FROM   user_tab_partitions where TABLE_NAME ='T1'
ORDER BY 1,2; 

TABLE_NAME	         PARTITION_NAME
-------------------- --------------------
T1		             T1_PART_2015
T1		             T1_PART_2016
T1		             T1_PART_2017

SQL> COLUMN index_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> SELECT index_name, partition_name, status
FROM   user_ind_partitions where INDEX_NAME='T1_CREATED_DATE_IDX'
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


4 组合分区表
原始表也可以使用ALTER table进行复合分区…修改命令。在本例中，我们将原始表转换为range-hash分区表。

SQL> 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
  );

Table altered.


可以使用以下查询显示表和分区索引的子分区。

SQL> COLUMN table_name FORMAT A20 
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN subpartition_name FORMAT A20
SQL> SELECT table_name, partition_name, subpartition_name
FROM   user_tab_subpartitions where TABLE_NAME ='T1'
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

TABLE_NAME	         PARTITION_NAME	      SUBPARTITION_NAME
-------------------- -------------------- --------------------
T1		             T1_PART_2017	      T1_SUB_PART_2017_4

12 rows selected.


SQL> COLUMN index_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN subpartition_name FORMAT A20
SQL> SELECT index_name, partition_name, subpartition_name, status
FROM   user_ind_subpartitions where INDEX_NAME='T1_CREATED_DATE_IDX'
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

INDEX_NAME	          PARTITION_NAME	  SUBPARTITION_NAME    STATUS
-------------------- -------------------- -------------------- --------
T1_CREATED_DATE_IDX     T1_PART_2017	  T1_SUB_PART_2017_4   USABLE

12 rows selected.

http://www.cndba.cn/leo1990/article/2176http://www.cndba.cn/leo1990/article/2176

参考文档:http://www.cndba.cn/leo1990/article/2176http://www.cndba.cn/leo1990/article/2176

http://docs.oracle.com/database/122/VLDBG/evolve-nopartition-table.htm#VLDBG-GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5http://www.cndba.cn/leo1990/article/2176

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
leo

leo

关注

坚持你的坚持

  • 202
    原创
  • 0
    翻译
  • 39
    转载
  • 16
    评论
  • 访问：766487次
  • 积分：1270
  • 等级：核心会员
  • 排名：第8名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ