探讨Oracle分区表

　　一年又一年，又到年底了，对于数据库的分区表需要检查一下，有无最大分区，次分区是否需要追加分区，如果程序不是自动追加分区的话，那么年中结算的时候，就会报错。

1、oracle分区主要有五种类型

　　（1）RANGE分区 范围分区

　　（2）LIST分区    列表分区

　　（3）HASH分区  哈希分区

　　（4）Range-Hash 复合分区

　　（5）Range-List 复合分区

2、使用分区表的好处

　　1）由于将数据分散到各个分区中，减少了数据损坏的可能性；
　　2）可以对单独的分区进行备份和恢复；
　　3）可以将分区映射到不同的物理磁盘上，来分散IO；
　　4）提高可管理性、可用性和性能；
　　5）适用于大型OLAP系统。

3、检查当期分区以及分区中的High_Value值

SQL> col table_name for A13
SQL> col partition_name for A12
SQL> col partitioning_type for a6
SQL> col subpartitioning_type for a6
SQL> col high_value for a80
SQL> SELECT table_name, partition_name,high_value,partitioning_type,subpartitioning_type
  2    FROM (SELECT a.table_name, a.partition_name,high_value,b.partitioning_type,b.subpartitioning_type,
  3                  rank() over(PARTITION BY a.table_name ORDER BY a.partition_name DESC) rankid
  4             FROM dba_tab_partitions a,dba_part_tables b
  5             where a.table_name = 'PART_TB_NAME'
  6             and a.table_name = b.table_name)
  7   WHERE rankid <= 3;
 
TABLE_NAME    PARTITION_NA HIGH_VALUE                                                                       PARTIT SUBPAR
------------- ------------ -------------------------------------------------------------------------------- ------ ------
PART_TB_NAME  PMAX         MAXVALUE                                                                         RANGE  NONE
PART_TB_NAME  P20131130    TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE  NONE
PART_TB_NAME  P20131031    TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE  NONE

　　以上SQL取分区表的前三个分区，同统计相关表的相关信息。

4、APPEND分区

　　首先查询最大分区是否已经含有数据：没有数据，就可以直接删除，然后再重新创建最大分区。

SELECT COUNT(1) FROM PART_TB_NAME PARTITION(pmax);--0
ALTER TABLE PART_TB_NAME DROP PARTITION pmax;
ALTER tABLE PART_TB_NAME ADD PARTITION P20140131 values less than (TO_DATE('2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESAPCE TESTDATA;
ALTER tABLE PART_TB_NAME ADD PARTITION P20140228 values less than (TO_DATE('2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESAPCE TESTDATA;
ALTER tABLE PART_TB_NAME ADD PARTITION P20140331 values less than (TO_DATE('2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESAPCE TESTDATA;

ALTER TABLE PART_TB_NAME ADD PARTITION pmax values less than (maxvalue) TABLESAPCE TESTDATA; --#新建最大分区

 5、SPLIT分区

　　如果最大分区里有数据，需要将最大分区里的数据剥离出去，然后再重新追加分区。

select COUNT(1) from PART_TB_NAME partition(PMAX); --3
select COUNT(1) from PART_TB_NAME partition(P20131231); --2
alter table PART_TB_NAME split partition pmax at(to_date('2013-12-31','yyyy-mm-dd')) into (partition P20131231,partition pmax); 
--将分区键值小于20131231的数据split到P20131231分区中。
alter table PART_TB_NAME split partition pmax at(to_date('2014-01-31','yyyy-mm-dd')) into (partition P20140131,partition pmax);
select COUNT(1) from PART_TB_NAME partition(P20131231);--2
select COUNT(1) from PART_TB_NAME partition(P20140131);--1
select COUNT(1) from PART_TB_NAME partition(PMAX); --0  接着再去追击分区

6、检查是否分区中还有子分区

SELECT * FROM dba_tab_subpartitions; --查看是否有复合分区

7、检查分区索引

SQL> column INDEX_NAME format a30
SQL> column PARTITION_NAME format a30
SQL> SELECT index_name, NULL partition_name, status
  2    FROM dba_indexes
  3   WHERE table_name = upper('PART_TB_NAME')
  4     AND partitioned = 'NO'
  5  UNION
  6  SELECT index_name, partition_name, status
  7    FROM user_ind_partitions
  8   WHERE index_name IN
  9         (SELECT index_name
 10            FROM dba_indexes
 11           WHERE table_name = upper('PART_TB_NAME'))
 12   ORDER BY 1, 2, 3;
 
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
PK_PART_TB_NAME                                               VALID

8、重建全局索引

drop index idx_part_tb_name ;--这个是存在风险的，尤其是也的数据量很大的时候，操作需谨慎。
create index idx_part_tb_name
on part_tb_name (opt_dt) global 
partition by range (opt_dt)
(
partition P20131231 values less than (to_date('2014-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS')) tablespace testdata1,
partition p20140131 values less than (to_date('2014-02-01 00:00:00','SYYYY-MM-DD HH24:MI:SS')) tablespace testdata1,
partition PMAX values less than (maxvalue) tablespace testdata1
);

9、创建本地索引

create index idx_part_tb_name on part_tb_name(opt_dt) local;　　

 10、摘录总结

          比较表级别分区操作的索引分区维护

表级分区操作	非分区索引	本地分区索引	全局分区索引
增加分区	索引不受影响	索引不受影响	索引不受影响
拆分分区	整个索引被标记为UNUSABLE	受拆分操作影响的分区上的索引被标记为UNUSABLE	索引的所有分区都被标记为UNUSABLE
移动分区	整个索引被标记为UNUSABLE	被移动的分区上的索引被标记为UNUSABLE	索引的所有分区都被标记为UNUSABLE
交换分区	整个索引被标记为UNUSABLE	被交换的分区上的索引被标记为UNUSABLE	索引的所有分区都被标记为UNUSABLE
合并分区	整个索引被标记为UNUSABLE	受合并操作影响的分区上的索引被标记为UNUSABLE	索引的所有分区都被标记为UNUSABLE
截断分区	整个索引被标记为UNUSABLE	索引不受影响	索引的所有分区都被标记为UNUSABLE
删除分区	整个索引被标记为UNUSABLE	本地索引分区被删除，其余索引分区不受影响	索引的所有分区都被标记为UNUSABLE
令数据只读	不可能实现，除非整个表示静态的 (表上没有DML操作)	通过表空间隔离可以令分区级别索引数据只读	理论上可以令分区级别索引数据只读。 实际上无法实现，除非整个表是静态的

 11、程序自动添加分区脚本

--判断当日的表分区是否已经创建
SELECT COUNT(1)
  INTO v_count
  FROM user_tab_partitions
 WHERE table_name = 'PART_TB_NAME'
   AND partition_name = 'P' || to_char(SYSDATE, 'yyyymmdd');

--如果没有创建当日数据的分区，主动为分区表添加分区结构
IF v_count = 0 THEN
  var_sql := 'alter table PART_TB_NAME add PARTITION p' ||
             to_char(SYSDATE, 'yyyymmdd') ||
             ' VALUES LESS THAN (TO_DATE(''' ||
             to_char(SYSDATE + 1, 'yyyymmdd') ||
             ''',''yyyymmdd)) tablespace testdata' ||
             to_char(SYSDATE, 'MM');
  dbms_utility.exec_ddl_statement(var_sql);
END IF;

12、Oracle11g新特性

　　11g自动分区技术：

CREATE TABLE partition_table
(
OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(128),
CREATED DATE
)
PARTITION BY RANGE (CREATED)
INTERVAL(NUMTOYMINTERVAL(1, 'month'))
(PARTITION P20131130 VALUES LESS THAN (TO_DATE('2013-12-01', 'yyyy-mm-dd')));
--这样，就会每个月系统自动生成一个分区
--INTERVAL (NUMTODSINTERVAL(1,'day'))   表示每天
--INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))   表示每年

13、分区表的相关维护

　　本部分摘录于网络博客。

1、增加表分区
--range partitioned table
    ALTER TABLE PART_TB_NAME ADD P20131231 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss'));
--list partitioned table
    ALTER TABLE list_partition ADD PARTITION01 VALUES ('100');
--Adding Values for a List Partition
    ALTER TABLE list_partition MODIFY PARTITION PARTITION02 ADD VALUES('200');
--Dropping Values from a List Partition
    ALTER TABLE list_partition MODIFY PARTITION PARTITION01 DROP VALUES('100');
--hash partitioned table
    ALTER TABLE hash_partition ADD PARTITION03;
--增加subpartition
    ALTER TABLE range_hash_partition MODIFY PARTITIONTEST ADD SUBPARTITION PARTITIONTEST1;
   注:hash partitioned table 新增partition时，现有表的中所有data都有重新计算hash值，然后重新分配到分区中。所以被重新分配的分区的indexes需要rebuild 。
2、删除分区
    ALTER TABLE PART_TB_NAME DROP PARTITION PMAX;
3、分区合并
    1)合并父分区 
    ALTER TABLE PART_TB_NAME MERGE Partition1, Partition2 INTO PARTITION Partition3 UPDATE INDEXES; 
    --如果省略update indexes子句的话，必须重建受影响的分区的index;
    ALTER TABLE PART_TB_NAME MODIFY Partition2 REBUILD UNUSABLE LOCAL INDEXES;
    2)合并子分区
    ALTER TABLE merge_partition
    MERGE SUBPARTITIONS Partition1, Partition2 INTO SUBPARTITION Partition2 UPDATE INDEXES;
4、转换分区
    可以将分区表转换成非分区表，或者几种不同分区表之间的转换。
    CREATE TABLE hash_part02 AS SELECT * FROM hash_partition WHERE 1=2;
    ALTER TABLE hash_partition EXCHANGE Partition2 WITH TABLE hash_part02; 
   这时，分区表hash_partition中的Partition2分区的资料将被转移到hash_part02这个非分区表中。