荣耀彩票代理

IT技术互动交流平台

ORACLE分区表梳理系列(二) 分区表日常维护及注意事项(红字需要留意)

作者:Yumiko_Sunny  来源:IT165收集  发布日期:2016-12-16 20:35:41

版权声明:本文发布于http://www.cnblogs.com/yumiko/,版权由Yumiko_sunny所有,欢迎转载。转载时,请在文章明显位置注明原文链接。若在未经作者同意的情况下,将本文内容用于商业用途,将保留追究其法律责任的权利。如果有问题,请以邮箱方式联系作者(793113046@qq.com)。


 前言

本文着重总结分区表的日常维护操作以及相应的注意事项。 本文涉及的日常维护内容包括:
  • 增加分区(add)
    • 移动分区(move) 截断分区(truncate) 删除分区(drop) 拆分分区(split) 合并分区(merge)      --hash分区不适用 交换分区(exchange)
      • 收缩分区(coalesce)   --仅适用于hash分区 本文涉及一些非分区表至分区表的迁移方法的思路,以及一些日常维护操作在特殊情况下的处理方法。 本文演示涉及的测试分区表,若无特殊创建或者说明,默认使用“测试表准备”部分提及的测试表。 本文演示使用的数据库版本为oracle 11.2.0.4。

        需要注意:关于分区表日常维护操作,对于分区表索引的影响未提及,会在后面总结分区表索引时进行阐述说明。

        1、测试表准备

        WEILEBIANYUJUTIDECAOZUOYANSHI,SHOUXIANZHUNBEIYIZHANGRANGEXINGDECESHIFENQUBIAOTEST_RANGE_PARTITION。

        ZHEILIDECESHISHUJULAIYUANYUoracleCESHIYONGHUscottXIADEempBIAO。

        --创建分区表TEST_RANGE_PARTITION
        --这里通过dbms_metadata.get_ddl获得emp表的建表结构进而修改
        Yumiko_sunny@OA01> CREATE TABLE 'SCOTT'.'TEST_RANGE_PARTITION'
          2     (    'EMPNO' NUMBER(4,0),
          3          'ENAME' VARCHAR2(10),
          4          'JOB' VARCHAR2(9),
          5          'MGR' NUMBER(4,0),
          6          'HIREDATE' DATE,
          7          'SAL' NUMBER(7,2),
          8          'COMM' NUMBER(7,2),
          9          'DEPTNO' NUMBER(2,0)
         10     ) 
         11    PARTITION BY RANGE ('SAL')
         12     (PARTITION 'TEST_RANGE_SAL_01' VALUES LESS THAN (1000)
         13      PARTITION 'TEST_RANGE_SAL_02' VALUES LESS THAN (2000) 
         14      PARTITION 'TEST_RANGE_SAL_03' VALUES LESS THAN (3000) 
         15      PARTITION 'TEST_RANGE_SAL_MAX' VALUES LESS THAN (MAXVALUE)  
         16     );
        Table created.
        
        
        Yumiko_sunny@OA01> insert into TEST_RANGE_PARTITION select * from emp;
        14 rows created.
        
        
        Yumiko_sunny@OA01> commit;
        Commit complete.

        TONGGUOXIAMIANDEFANGFA,LEJIEGUANYUSHANGMIANCHUANGJIANFENQUBIAODESHUJUFENBUJIBENQINGKUANG。

        --查询分表各分区的条件以及数据库分布情况
        --可以看到此时NUM_ROWS列为空,主要是因为表的的统计信息未收集导致。
        Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
          2  from user_part_tables a,user_tab_partitions b 
          3  where a.TABLE_NAME=b.TABLE_NAME; 
        
        TABLE_NAME                     PARTITION PARTITION_NAME       HIGH_VALUE    NUM_ROWS
        ------------------------------ --------- -------------------- ----------- ----------
        TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_01    1000
        TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    2000
        TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    3000
        TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_MAX   MAXVALUE
        
        
        --收集分区表TEST_RANGE_PARTITION的统计信息
        Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
        Table analyzed.
        
        
        --可以看到,此时各分区的数据情况已经显示出来
        Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
          2  from user_part_tables a,user_tab_partitions b 
          3  where a.TABLE_NAME=b.TABLE_NAME; 
        
        TABLE_NAME                     PARTITION PARTITION_NAME       HIGH_VALUE    NUM_ROWS
        ------------------------------ --------- -------------------- ----------- ----------
        TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_01    1000                 2
        TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    2000                 6
        TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    3000                 3
        TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_MAX   MAXVALUE             3

        荣耀彩票代理TONGGUOSHANGMIANDECAOZUO,YIJINGCHENGGONGCHUANGJIANLEYIZHANGRANGEXINGDEFENQUBIAO。

        XIAMIANJIANGYITUOZHEIZHANGBIAO,JIESHAOFENQUBIAODERICHANGWEIHUCAOZUO。

        2、增加分区维护操作(add)

        ZENGJIAFENQUWEIHUCAOZUO,GUMINGSIYI,ZHUYAOZHENDUIDANGQIANFENQUBIAOJINXINGTIANJIAXINFENQUDECAOZUO。

        荣耀彩票代理DANGFENQUBIAOCUNZAIMORENTIAOJIANFENQU,RU:RANGEFENQUBIAODEMAXVALUEFENQU、LISTFENQUBIAODEDEFAULTFENQU,CISHIZENGJIAFENQUCAOZUOHUIBAOCUO。

        荣耀彩票代理XIAMIANCHANGSHITONGGUOZENGJIAFENQUCAOZUO,ZHIJIEWEICESHIBIAOZENGJIAFENQUTEST_RANGE_SAL_04

        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000);
        alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000)
                                                       *
        ERROR at line 1:
        ORA-14074: partition bound must collate higher than that of the last partition

        KEYIKANDAO,ZHENDUICUNZAIMORENTIAOJIANDEFENQUBIAO,WUFAZHIXINGZENGJIAFENQUCAOZUO。

        JIEJUEBANFA:

        1、删除原默认条件分区,待增加分区后,再重新添加默认条件分区。
        2、使用拆分分区(split)的方式,后面介绍

        荣耀彩票代理ZHEILI,WOMENCHANGSHIXIAJIEJUEBANFA1DEFANGFAJINXINGCAOZUO。

        --删除存在默认条件MAXVALUE的分区
        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_MAX;
        Table altered.
        
        
        --重新收集分区表的统计信息
        Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
        Table analyzed.
        
        
        --观察分区表的信息,可以看到此时默认条件MAXVALUE的分区已经不存在
        Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
          2  from user_part_tables a,user_tab_partitions b 
          3  where a.TABLE_NAME=b.TABLE_NAME; 
        
        TABLE_NAME                     PARTITION PARTITION_NAME       HIGH_VALUE    NUM_ROWS
        ------------------------------ --------- -------------------- ----------- ----------
        TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_01    1000                 2
        TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    2000                 6
        TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    3000                 3
        
        
        --增加新分区TEST_RANGE_SAL_04
        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000);
        Table altered.
        
        
        --重新增加默认条件MAXVALUE分区
        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_MAX values less than(maxvalue);
        Table altered.

        TONGGUOSHANGMIANDEFANGFA,YIJINGWANCHENGLEZENGJIAFENQUDECAOZUO。XIAMIANJINYIBUYANZHENGZENGJIAFENQUDECAOZUO。

        --重新收集测试分区表的统计信息
        Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
        Table analyzed.
        
        
        --查看分区表信息,可以看到上面增加的新分区
        Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
          2  from user_part_tables a,user_tab_partitions b 
          3  where a.TABLE_NAME=b.TABLE_NAME; 
        
        TABLE_NAME            PARTITION PARTITION_NAME     HIGH_VALUE   NUM_ROWS
        --------------------- --------- ------------------ ----------- ---------
        TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_01  1000                2
        TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_02  2000                6
        TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_03  3000                3
        TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_MAX MAXVALUE            0
        TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_04  4000                0

        需要注意的是:对于默认条件的分区进行删除,其数据不会重分布到其他分区,而是删除数据。因此在生产环境使用需慎重

        ZHICI,ZENGJIAFENQUWEIHUCAOZUODEJIESHAOJIESHU。

        3、移动分区维护操作(move)

        荣耀彩票代理YIDONGFENQUWEIHUCAOZUO,ZHUYAOSHIJIANGFENQUCONGYIGEBIAOKONGJIANQIANYIZHILINGYIGEBIAOKONGJIANZHONG。

        --查看当前分区对应的表空间情况
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME       TABLESPACE_NAME
        ------------------------------ -------------------- ------------------------------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_02    USERS
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03    USERS
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_01    USERS
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX   USERS
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_04    USERS
        
        
        --执行移动分区操作
        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION move partition TEST_RANGE_SAL_01 tablespace PARTITION_TS;
        Table altered.
        
        
        --验证移动后,分区所在的表空间
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME       TABLESPACE_NAME
        ------------------------------ -------------------- ------------------------------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_02    USERS
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03    USERS
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_01    PARTITION_TS
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX   USERS
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_04    USERS

        XUYAOZHUYIDESHI:

        对于组合分区,无法直接移动分区荣耀彩票代理,否则会抛出ORA-14257错误,示例如下:

        --准备一张list-list的组合分区表
        Yumiko_sunny@OA01> CREATE TABLE 'EMPLOYEE_LIST_LIST_PART'
          2      ( 'EMPNO' NUMBER(4,0),
          3        'ENAME' VARCHAR2(10),
          4        'JOB' VARCHAR2(9),
          5        'MGR' NUMBER(4,0),
          6        'HIREDATE' DATE,
          7        'SAL' NUMBER(7,2),
          8        'COMM' NUMBER(7,2),
          9        'DEPTNO' NUMBER(2,0)
         10     )
         11     PARTITION BY LIST (DEPTNO)
         12     SUBPARTITION BY LIST (JOB)
         13     (
         14     PARTITION EMPLOYEE_DEPTNO_10 VALUES (10) 
         15       ( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES ('MANAGER'),
         16         SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)
         17       ),
         18     PARTITION EMPLOYEE_DEPTNO_20 VALUES (20) 
         19       ( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES ('MANAGER'),
         20         SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)
         21       ),
         22     PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT) 
         23       ( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES ('MANAGER'),
         24         SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT)
         25       )
         26     );
        
        Table created.
        
        
        
        --查看当前该组合分区所在表空间的信息
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions;
        
        TABLE_NAME              PARTITION_NAME         SUBPARTITION_NAME        TABLESPACE_NAME
        ----------------------- ---------------------- ------------------------ ---------------
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_MAGAGER  USERS
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_DEFAULT  USERS
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_MAGAGER  USERS
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_DEFAULT  USERS
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER  USERS
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT  USERS
        
        
        --移动组合分区表的区分
        Yumiko_sunny@OA01> alter table EMPLOYEE_LIST_LIST_PART  move partition EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS;
        alter table EMPLOYEE_LIST_LIST_PART  move partition EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS
                                                            *
        ERROR at line 1:
        ORA-14257: cannot move partition other than a Range, List, System, or Hash partition

        TONGGUOSHANGMIANDEYANSHI,KEYIQINGCHUDEKANDAO,DUIYUZUHEFENQU,WUFAZHIJIEYIDONGFENQUZHIXINDEBIAOKONGJIAN。

        JIEJUEBANFA:

        YIDONGFENQUBIAODEZIFENQU,RANHOUXIUGAIDANGQIANSUOZAIFENQUDESHUXINGJIKE。JUTIYANSHIRUXIA:

        --移动子分区
        Yumiko_sunny@OA01> alter table EMPLOYEE_LIST_LIST_PART  move subpartition EMPLOYEE_20_JOB_MAGAGER tablespace PARTITION_TS;
        Table altered.
        
        
        Yumiko_sunny@OA01> alter table EMPLOYEE_LIST_LIST_PART  move subpartition EMPLOYEE_20_JOB_DEFAULT tablespace PARTITION_TS;
        Table altered.
        
        
        --修改分区的默认属性
        Yumiko_sunny@OA01> ALTER TABLE EMPLOYEE_LIST_LIST_PART MODIFY DEFAULT ATTRIBUTES FOR PARTITION EMPLOYEE_DEPTNO_20
          2  tablespace PARTITION_TS;
        Table altered.
        
        
        
        --验证移动分区后的结果
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions;
        
        TABLE_NAME              PARTITION_NAME         SUBPARTITION_NAME        TABLESPACE_NAME
        ----------------------- ---------------------  -----------------------  ---------------
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_MAGAGER  USERS
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_DEFAULT  USERS
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_MAGAGER  PARTITION_TS
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_DEFAULT  PARTITION_TS
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER  USERS
        EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT  USERS

        KEYIKANDAO,TONGGUOYIDONGZIFENQUDEFANGFA,WANCHENGLEDUIYUZUHEFENQUDEYIDONGCAOZUO。

        4、截断分区维护操作(truncate)

        JIEDUANFENQUWEIHUCAOZUO,XIANGDUIYUCHUANTONGDEdeleteCAOZUO,SHANCHUSHUJUDEXIAOLVHUIGENGGAO。ERQIEHUIJIANGDIGAOSHUIWEIXIAN。

        YANSHIRUXIA:

        --查看当前测试表分区情况及分区中的记录数
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions
          2  where PARTITION_NAME='TEST_RANGE_SAL_02' or PARTITION_NAME='TEST_RANGE_SAL_03';
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    6
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
        
        
        
        --执行截断分区操作
        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION truncate partition TEST_RANGE_SAL_02;
        Table truncated.
        
        
        
        --重新收集最新的测试表的统计信息
        Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
        Table analyzed.
        
        
        
        --验证截断操作后,分区的记录数变化
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions
          2  where PARTITION_NAME='TEST_RANGE_SAL_02' or PARTITION_NAME='TEST_RANGE_SAL_03';
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3

        荣耀彩票代理CONGSHANGMIANDEYANSHIZHONGKEYIKANDAO,TONGGUOtruncateCAOZUO,CESHIBIAODETEST_RANGE_SAL_02FENQUSHUJUBEIQINGKONG。

        ZHICI,YANSHIWANBI。

        5、删除分区维护操作(drop)

        荣耀彩票代理DUIYUFENQUDESHANCHUCAOZUO,XUYAOZHUYI,ZAISHANCHUFENQUHOU,FENQUSUOJILUDESHUJU,BUHUIZHONGFENBUZHIQITAFENQUZHONG,ERSHIBEIYIBINGSHANCHU。

        --检查当前分区表的分区情况,以及数据的分布情况
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
        
        
        --执行分区的删除操作
        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_04;
        Table altered.
        
        
        
        --再次检查分区表的分区情况,以及数据的分布情况
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0

        KEYIKANDAO,FENQUDESHANCHUCAOZUOBUHUIYINGXIANGSHUJUDEFENBUQINGKUANG。

        6、拆分分区维护操作(split)

        ZAI“ZENGJIAFENQUWEIHUCAOZUO”BUFEN,TIDAOLEDUIYUCUNZAIMORENTIAOJIANDEFENQUBIAOZENGJIAFENQUDEDELIANGZHONGBANFA,ZHEILIJIANGJIESHAOTONGGUOCHAIFENFENQUDEBANFALAIZENGJIAFENQU。

        需要注意:在目标分区拆分后,被拆分的分区会按照拆分规则,将数据进行重分布

        YANSHISHILI:

        SHOUXIAN,JIANGCESHIBIAODESHUJUFENBUHAIYUANZHICHUJIANSHIDESHUJUFENBUTAI。

        --清空测试分区表中的所有数据
        Yumiko_sunny@OA01> truncate table TEST_RANGE_PARTITION;
        Table truncated.
        
        
        --重新加载测试分区表的数据
        Yumiko_sunny@OA01> insert into TEST_RANGE_PARTITION select * from emp;
        14 rows created.
        
        
        Yumiko_sunny@OA01> commit;
        Commit complete.
        
        
        --重新收集测试表的统计信息
        Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
        Table analyzed.
        
        
        --查看此时,数据在分区间的分布情况
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    6
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    3

        荣耀彩票代理CHAKANCISHI,CUNZAIMORENTIAOJIANMAXVALUEDEFENQUTEST_RANGE_SAL_MAXDEJUTISHUJUXINXI。

        Yumiko_sunny@OA01> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_MAX);
        
             EMPNO ENAME      JOB              MGR HIREDATE          SAL     COMM    DEPTNO
        ---------- ---------- --------- ---------- ------------ -------- -------- ---------
              7788 SCOTT      ANALYST         7566 19-APR-87        3000                 20
              7839 KING       PRESIDENT            17-NOV-81        5000                 10
              7902 FORD       ANALYST         7566 03-DEC-81        3000                 20

          

        XIAMIANZHENDUISHANGMIANDEFENQUTEST_RANGE_SAL_MAXJINXINGCHAIFENCHULI,QIZHONG:

        将SAL>=3000且SAL<4000的数据放入新的分区TEST_RANGE_SAL_04。
        将SAL>=4000的数据保留在分区TEST_RANGE_SAL_MAX中。

        --针对目标分区,执行拆分分区维护操作
        --依据上面的需求,将数据拆分至分区TEST_RANGE_SAL_04以及TEST_RANGE_SAL_MAX中
        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION split partition TEST_RANGE_SAL_MAX at (4000) into
          2  (partition TEST_RANGE_SAL_04,partition TEST_RANGE_SAL_MAX);
        Table altered.
        
        
        --查看此时测试分区表的分区情况,以及数据分布情况
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    6
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    2
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    1

        荣耀彩票代理YANZHENGFENQUZHONGSHIJIDESHUJUNEIRONG

        Yumiko_sunny@OA01> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_04);
        
             EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
        ---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
              7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
              7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
        
        
        
        Yumiko_sunny@OA01> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_MAX);
        
             EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
        ---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
              7839 KING       PRESIDENT            17-NOV-81          5000                    10

        KEYIKANDAO,JINGGUOCHAIFEN,SHUJUYIANZHIQIANDEXUQIU,FENBIECUNCHUZAILIANGGEFENQUZHONG。


        7、合并分区维护操作(merge)

        HEBINGFENQUCAOZUO,ZHUYAOSHIJIANGBUTONGDEFENQU,TONGGUOFENQUDEHEBING,JINXINGZHENGHE。

        XUYAOZHUYI:

        对于list分区,合并的分区无限制要求。 对于range分区,合并的分区必须相邻,否则无法进行合并操作。 对于hash分区,无法进行合并分区操作

        荣耀彩票代理CIWAI,DUIYUrangeFENQU,XIAXIANZHIYOUBIANJIEZHIJIAODIDEFENQUJUEDING,SHANGXIANZHIYOUBIANJIEZHIJIAOGAODEFENQUJUEDING。

        YANSHISHILI:

        荣耀彩票代理TONGGUOHEBINGFENQUJISHU,JIANGCESHIBIAODEFENQUTEST_RANGE_SAL_01YIJIFENQUTEST_RANGE_SAL_02JINXINGHEBING,JUTIRUXIA: 

        --查看当前分区表的分区情况
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    6
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             2
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    2
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    1
        
        
        
        --进行合并分区操作
        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION merge partitions 
          2  TEST_RANGE_SAL_01,TEST_RANGE_SAL_02
          3  into partition TEST_RANGE_SAL_00;
        Table altered.
        
        
        
        --验证合并分区后的结果
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    3
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    2
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    1
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    8

        8、交换分区维护操作(exchange) 

        交换分区技术,主要是将一个非分区表的数据同“一个分区表的一个分区”进行数据交换。支持双向交换,既可以从分区表的分区中迁移到非分区表,也可以从非分区表迁移至分区表的分区中
        原则上,非分区表的结构、数据分布等,要符合分区表的目标分区的定义规则

        YANSHIRUXIA:

        SHOUXIAN,QINGKONGCESHIFENQUBIAODESHUJU

        Yumiko_sunny@OA01> truncate table TEST_RANGE_PARTITION;
        Table truncated.
        
        
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    0

        CHUANGJIANYIZHANGJIYUempBIAO,sal<2000DECESHIFEIFENQUBIAOemp_test。

        Yumiko_sunny@OA01> create table emp_test as select * from emp where sal < 2000;
        Table created.
        
        
        Yumiko_sunny@OA01> select count(*) from emp_test;
          COUNT(*)
        ----------
                 8

        ZHUYI,CISHIFEIFENQUBIAODESHUJULIANGWEI8TIAOJILU。

        荣耀彩票代理ZHIXINGJIAOHUANFENQUCAOZUO,GUANCHAFENQUBIAODEJILUBIANHUA,YIJIFEIFENQUBIAODEJILUBIANHUA

        --执行分区交换操作
        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;
        Table altered.
        
        
        
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    8
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0
        
        
        
        Yumiko_sunny@OA01> select count(*) from emp_test;
        
          COUNT(*)
        ----------
                 0

        荣耀彩票代理KEYIKANDAO,TONGGUOFENQUJIAOHUAN,FEIFENQUBIAODESHUJUZHUANYIZHIFENQUBIAOZHONG,TONGSHIFEIFENQUBIAODEJILUBEIQINGCHU。

        ZAICIZHIXINGJIAOHUANFENQUCAOZUO,GUANCHAFENQUBIAODEJILUBIANHUA,YIJIFEIFENQUBIAODEJILUBIANHUA

        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;
        Table altered.
        
        
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    0
        
        
        
        Yumiko_sunny@OA01> select count(*) from emp_test;
        
          COUNT(*)
        ----------
                 8

        KEYIKANDAO,CISHIFENQUBIAODESHUJUYOUZAICIZHUANYIHUIZHIFEIFENQUBIAO,ZHENGMINGLEQIANMIANSUOSHU,FENQUJIAOHUANJISHU,JIKEYICONGFENQUBIAODEFENQUZHONGQIANYIDAOFEIFENQUBIAO,YEKEYICONGFEIFENQUBIAOQIANYIZHIFENQUBIAODEFENQUZHONG。

        荣耀彩票代理RUOFEIFENQUBIAODESHUJU,BUFUHEFENQUBIAODEFENQUGUIZE,CISHIJIAOHUANHUIPAOCHUORA-14099CUOWU。

        --清空上面测试非分区表的数据
        Yumiko_sunny@OA01> truncate table emp_test;
        Table truncated.
        
        
        --加载emp的所有数据至该测试非分区表
        --之所以使用测试非分区表,是考虑emp表以后做其他实验时可能还需要其中的数据
        --通过这样操作,测试非分区表的数据,既存在sal<2000的数据,也存在sal>2000的数据
        Yumiko_sunny@OA01> insert into emp_test select * from emp;
        14 rows created.
        
        
        Yumiko_sunny@OA01> commit;
        Commit complete.
        
        
        
        --尝试交换分区,观察结果
        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;
        alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test
                                                                                         *
        ERROR at line 1:
        ORA-14099: all rows in table do not qualify for specified partition

        KEYIKANDAO,YOUYUTEST_RANGE_SAL_00FENQUDEXIANZHITIAOJIANWEIsal<2000,ERCESHIFEIFENQUBIAODESHUJUBAOHANLEsal>2000DESHUJU,YINCIJIAOHUANSHIBAI。

        JIEJUEBANFA:

        通过without validation子句,可以避免数据校验,而交换成功。但会存在与分区规则相悖的数据,因此该方法要慎重。

        Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test without validation;
        Table altered.
        
        
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                   14
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
        TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0

          

        JISHUFANGANKUOZHANSILU:

        若打算采用交换分区的方法,以实现非分区表到分区表的转换,可以采用先创建一个只有默认条件的单一分区的分区表,在分区交换数据后,根据实际需要,通过前面提到的“拆分分区”的方法进行分区操作。

        9、收缩分区维护操作(coalesce)

        收缩分区维护操作,仅仅可以在hash分区以及组合分区的hash子分区上进行使用

        TONGGUOSHIYONGSHOUSUOFENQUJISHU,KEYISHOUSUODANGQIANhashFENQUDEFENQUSHULIANG。

        DUIYUhashFENQUDESHUJU,ZAISHOUSUOGUOCHENGZHONG,oracleHUIZIDONGWANCHENGSHUJUZAIFENQUJIANDEZHONGFENBU。

        YANSHIRUXIA:

        荣耀彩票代理SHOUXIANJIYUempBIAODESHUJU,CHUANGJIANYIZHANGhashFENQUBIAO

        Yumiko_sunny@OA01> CREATE TABLE 'EMPLOYEE_HASH_PART'
          2      ( 'EMPNO' NUMBER(4,0),
          3        'ENAME' VARCHAR2(10),
          4        'JOB' VARCHAR2(9),
          5        'MGR' NUMBER(4,0),
          6        'HIREDATE' DATE,
          7        'SAL' NUMBER(7,2),
          8        'COMM' NUMBER(7,2),
          9        'DEPTNO' NUMBER(2,0)
         10      )
         11      PARTITION BY HASH (ENAME)
         12      (
         13      PARTITION EMPLOYEE_PART01,
         14      PARTITION EMPLOYEE_PART02
         15     );  
        
        Table created.
        
        
        Yumiko_sunny@OA01> insert into EMPLOYEE_HASH_PART select * from emp;
        14 rows created.
        
        
        Yumiko_sunny@OA01> commit;
        Commit complete.
        
        
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        EMPLOYEE_HASH_PART             EMPLOYEE_PART02           USERS                    6
        EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                    8

        ZHIXINGSHOUSUOFENQUCAOZUO

        Yumiko_sunny@OA01> alter table EMPLOYEE_HASH_PART coalesce partition;
        Table altered.
        
        
        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                   14

        KEYIKANDAO,TONGGUOSHOUSUOFENQU,YUANBENLIANGGEFENQUZHENGHEDAOYIGE,ERQIESHUJUYETONGSHIBEIZHENGHE。

        XUYAOZHUYI:

        DANGhashFENQUZHONGZHIYOUYIGEFENQUSHI,CISHIWUFAJINXINGSHOUSUOCAOZUO。

        Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
        
        TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
        ------------------------------ ------------------------- --------------- ----------
        EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                   14
        
        
        
        Yumiko_sunny@OA01> alter table EMPLOYEE_HASH_PART coalesce partition;
        alter table EMPLOYEE_HASH_PART coalesce partition
                    *
        ERROR at line 1:
        ORA-14285: cannot COALESCE the only partition of this hash partitioned table or index

        ZHICI,GUANYUFENQUBIAODERICHANGWEIHUCAOZUOJIZHUYISHIXIANGZONGJIEJIESHU,HOUXUHUICHOUSHIJIANZONGJIEFENQUBIAOSUOYINDEWEIHU。

Tag标签:         
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规