论oracle分区表的创建与维护

创建分区表：
oracle分区方法：range、hash、list和composite partition；
range分区表示例：
CREATE TABLE sales_range (salesman_id NUMBER(5), salesman_name VARCHAR2(30),
 sales_amount NUMBER(10), sales_date DATE) COMPRESS PARTITION BY RANGE(sales_date)
  (
      PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
      PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
      PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
       PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')));
分区字段values less than必须是确定值,最后一个可以是maxvalue,每个分区可以单独指定物理属性
range分区特点：
最早、最经典的分区方法
Range分区通过对分区字段值的范围进行分区
Range分区特别适合于按时间周期进行数据的存储：日、周、月、年等
数据管理能力强
数据迁移
数据备份
数据交换
范围分区的数据可能不均匀
范围分区与记录值有关，实施难度和可维护性相对较差
hash分区表示例：
create table emp_t(empno integer,ename varchar2(20))
partition by hash(empno)
partitions 4;--指定分区所在表空间(partition part_01 tablespace test,partition part_02 tablespace sys);
list分区表示例：
CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30),
 sales_state VARCHAR2(20), sales_amount NUMBER(10), sales_date DATE)
PARTITION BY LIST(sales_state)
 (
    PARTITION sales_west VALUES('California', 'Hawaii'),
    PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
    PARTITION sales_central VALUES('Texas', 'Illinois'),
     PARTITION sales_other VALUES(DEFAULT));
list分区特点：
List分区通过对分区字段的离散值进行分区
List分区是不排序的，而且分区之间也没有关联
List分区适合于对数据离散值进行控制
List分区只支持单个字段
List分区具有与range分区相似的优缺点：
数据管理能力强
各分区的数据可能不均匀
composite分区表示例：
CREATE TABLE quarterly_regional_sales (deptno NUMBER, item_no VARCHAR2(20),
 txn_date DATE, txn_amount NUMBER, state VARCHAR2(2))
PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state)
( PARTITION q1_1999 VALUES LESS THAN(TO_DATE('1-APR-1999','DD-MON-YYYY'))
      (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
      SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
     SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
     SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
     SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
    SUBPARTITION q1_1999_southcentral VALUES ('NM', 'TX')),
PARTITION q2_1999 VALUES LESS THAN(TO_DATE('1-JUL-1999','DD-MON-YYYY'))
  (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
  SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
   SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
  SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
   SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
  SUBPARTITION q2_1999_southcentral VALUES ('NM', 'TX')),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
   (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
        … ….
      SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
      SUBPARTITION q4_1999_southcentral VALUES ('NM', 'TX')));
composite分区特点：
Oracle支持的Composite分区：
  range-hash
  Range-list
既适合于历史数据，又适合于数据均匀分布
与范围分区一样提高可用性和可管理性
更好的PDML和partition-wise joins特性
实现粒度更细的操作
支持符合local indexes
不支持符合 global indexes

分区表设计原则：
表的大小：当表的大小超过2GB，或对于OLTP系统，表记录超过1000万时，都应该考虑对表进行分区
数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样的表进行分区，可充分利用分区排除无关数据查询的特性
数据维护：按时间段删除成批的数据，对于这样的表需要考虑进行分区，以满足维护需要
数据备份和恢复：按时间周期进行表空间备份时，将分区与表空间建立对应关系
只读数据：如果一个表中大部分数据都是只读，通过对表进行分区，可将只读数据存储在只读表空间，对于数据的备份有利
OLAP并行数据操作
分区表及索引分区数据字典信息：
--1、查询当前用户下有哪些是分区表：
SELECT * FROM USER_PART_TABLES;
--2、查询当前用户下有哪些分区索引：
SELECT * FROM USER_PART_INDEXES;
--3、查询当前用户下分区索引的分区信息：
SELECT *
  FROM USER_IND_PARTITIONS T
 WHERE T.INDEX_NAME = ?
--4、查询当前用户下分区表的分区信息：
  SELECT * FROM USER_TAB_PARTITIONS T WHERE T.TABLE_NAME = ?;

--5、查询某分区下的数据量：
SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);
--6、查询索引、表上在那些列上创建了分区：
SELECT * FROM USER_PART_KEY_COLUMNS;
--7、查询某用户下二级分区的信息（只有创建了二级分区才有数据）：
SELECT * FROM USER_TAB_SUBPARTITIONS;
--查看某一分区上的数据
select * from user_table partition(partitionname);
分区表维护：
--删除分区
    ALTER TABLE table_name DROP PARTITION partition_name;
--说明：此语句不可用于hash分区表，如果是全局索引，因为全局索引的分区结构和表可以不一致，若不一致的情况下，会导致整个全局索引失效，在删除分区的时候，语句修改为：
    ALTER TABLE table_name DROP PARTITION partition_name UPDATE GLOBAL INDEXES;
--合并分区（coalesce partition）：合并分区是用来操作HASH分区表和hash全局索引的，它会重新分配删除的分区的数据到现有的分区中
    ALTER TABLE table_name COALESCE PARTITION;
--合并分区和删除中间的RANGE有点像，但是合并分区是不会删除数据的，对于LIST、HASH分区也是和RANGE分区不一样的，其语法为：
    ALTER TABLE table_name MERGE PARTITIONS    partition_name1,partition_name2 INTO PARTITION MERGED_PARTITION;
--创建新的分区（分区数据若不能提供范围，则插入时会报错，需要增加分区来扩大范围）
    ALTER TABLE table_name ADD PARTITION partition_name VALUES LESS THAN(2500000);--list分区or RANGE分区
    ALTER TABLE table_name ADD PARTITION partition_name;--HASH分区
--创建子分区：在分区下创建新的子分区大致如下（RANGE分区，若为LIST或HASH分区，将创建方式修改为对应的方式即可）
    ALTER TABLE <table_name> MODIFY PARTITION <partition_name> ADD SUBPARTITION <user_define_subpartition_name> VALUES LESS THAN(....);
--修改分区名称（修改相关的属性信息）:
    ALTER TABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TO MERGED_PARTITION02;
--交换分区（快速交换数据，其实是交换段名称指针），首先创建一个交换表，和原表结构相同，如果有数据，必须符合所交换对应分区的条件
    CREATE TABLE TABLE_PARTITION_2
    AS SELECT * FROM TABLE_PARTITION WHERE 1=2;
--然后将第一个分区的数据交换出去
    ALTER TABLE TABLE_PARTITION EXCHANGE PARTITION TAB_PARTOTION_01
    WITH TABLE TABLE_PARTITION_2 INCLUDING INDEXES;
--此时会发现第一个分区的数据和表TABLE_PARTITION_2做了瞬间交换，比TRUNCATE还要快，因为这个过程没有进行数据转存，只是段名称的修改过程，和实际的数据量没有关系。
--如果是子分区也可以与外部的表进行交换，只需要将关键字修改为：SUBPARTITION 即可。
--清空分区数据
   ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>;
   ALTER TABLE <table_name> TRUNCATE subpartition <subpartition_name>;