Oracle 11g-Interval Partitioning
数据库管理员日常要做的一件重复而无聊的工作 比如每隔一天要生成新的24个分区，用以存储第二天的数据。而在11g中这项工作可以交由Oracle自动完成了，基于Range和List的Interval Partitioning分区类型登场。
在 11g 里的 Interval 创建，这种方法对没有写全的分区会自动创建。 比如我
这里只写了 1 月日期，如果插入的数据有其他月份的，会自动生成对应的分区。
CREATE TABLE TB_INTERVAL PARTITION BY RANGE (time_col) INTERVAL(NUMTOYMINTERVAL(1, 'month')) (PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2016', 'dd-mm-yyyy')));
指定需要Oracle自动创建分区的间隔时间，上面这个例子是1个月，然后至少创建一个基本分区，上面这个例子是在2016-1-1之前的所有数据都在P0分区中，以后每个月的数据都会存放在Oracle自动创建的一个新分区中。
select table_name,partition_name from user_tab_partitions where table_name='TB_INTERVAL';
select count(*) from TB_INTERVAL partition (p1);
创建按月分区的分区表
- 创建分区表
/* Formatted on 2010/6/10 20:21:12 (QP5 v5.115.810.9015) */ create table intervalpart (c1 number, c3 date) partition by range (c3) interval ( numtoyminterval (1, 'month') ) (partition part1 values less than (to_date ('01/12/2010', 'mm/dd/yyyy')), partition part2 values less than (to_date ('02/12/2010', 'mm/dd/yyyy')) )
注意： 如果在建 Interval 分区表是没有把所有的分区写完成，在插入相关数据后会自动生成分区
2. 查看现在表的分区：
SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALPART'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ INTERVALPART PART1 INTERVALPART PART2
- 插入测试数据：
SQL> begin 2 for i in 0 .. 11 loop 3 insert into intervalpart values(i,add_months(to_date('2010-1-1','yyyy-mm-dd'),i)); 4 end loop ; 5 commit; 6 end; 7 /
PL/SQL 过程已成功完成。
补充： add_months() 函数获取前一个月或者下一个月的月份， 参数中 负数 代
表 往前， 正数 代表 往后。
–上一个月
select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;
–下一个月
select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;
- 观察自动创建的分区：
SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALPART'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ INTERVALPART PART1 INTERVALPART PART2 INTERVALPART SYS_P22 INTERVALPART SYS_P23 INTERVALPART SYS_P24 INTERVALPART SYS_P25 INTERVALPART SYS_P26 INTERVALPART SYS_P27 INTERVALPART SYS_P28 INTERVALPART SYS_P29 INTERVALPART SYS_P30 INTERVALPART SYS_P31
已选择 12 行。
5. 查看分区内容：
SQL> select * from INTERVALPART; C1 C3 --------- ---------- 1 2010-01-01 0 2010-01-01 1 2010-02-01 2 2010-03-01 3 2010-04-01 4 2010-05-01 5 2010-06-01 6 2010-07-01 7 2010-08-01 8 2010-09-01 9 2010-10-01 10 2010-11-01 11 2010-12-01 已选择 13 行。 SQL> select * from INTERVALPART partition(part1); C1 C3 --------- ---------- 1 2010-01-01 0 2010-01-01 SQL> select * from INTERVALPART partition(part2); C1 C3 --------- ---------- 1 2010-02-01
创建一个以天为间隔的分区表
- 创建分区表：
SQL> create table dave 2 ( 3 id number, 4 dt date 5 ) 6 partition by range (dt) 7 INTERVAL (NUMTODSINTERVAL(1,'day')) 8 ( 9 partition p100101 values less than (to_date('2010-01-01','yyyy-mm-dd')) 10 );
- 查看表分区：
SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ DAVE P100101
- 插入测试数据：
SQL> begin 2 for i in 1 .. 12 loop 3 insert into dave values(i,trunc(to_date('2010-1-1','yyyy-mm-dd')+i)); 4 end loop; 5 commit; 6 end; 7 /
PL/SQL 过程已成功完成。
4. 观察自动创建的分区：
SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ DAVE P100101 DAVE SYS_P32 DAVE SYS_P33 DAVE SYS_P34 DAVE SYS_P35 DAVE SYS_P36 DAVE SYS_P37 DAVE SYS_P38 DAVE SYS_P39 DAVE SYS_P40 DAVE SYS_P41 DAVE SYS_P42 DAVE SYS_P43
已选择 13 行。
5. 查看分区内容：
SQL> select * from dave partition(SYS_P32); ID DT ---------- ---------- 1 2010-01-02 SQL> select * from dave partition(SYS_P33); ID DT -------- ---------- 2 2010-01-03 SQL> select * from dave partition(SYS_P34); ID DT -------- ---------- 3 2010-01-04 SQL> select * from dave; ID DT -------- ---------- 1 2010-01-02 2 2010-01-03 3 2010-01-04 4 2010-01-05 5 2010-01-06 6 2010-01-07 7 2010-01-08 8 2010-01-09 9 2010-01-10 10 2010-01-11 11 2010-01-12 12 2010-01-13 已选择 12 行。
System Partitioning
系统分区，在这个新的类型中，我们不需要指定任何分区键，数据会进入哪个分区完全由应用程序决定，实际上也就是由SQL来决定，终于，我们在Insert语句中可以指定插入哪个分区了。
假设我们创建了下面这张分区表，注意，没有指定任何分区键：
CREATE TABLE systab (c1 integer, c2 integer) PARTITION BY SYSTEM ( PARTITION p1 TABLESPACE tbs_1, PARTITION p2 TABLESPACE tbs_2, PARTITION p3 TABLESPACE tbs_3, PARTITION p4 TABLESPACE tbs_4 );
现在由SQL语句来指定插入哪个分区：
– 数据插入p1分区
INSERT INTO systab PARTITION (p1) VALUES (4,5);
– 数据插入第2个分区，也就是p2分区
INSERT INTO systab PARTITION (2) VALUES (7,8);
– 为了实现绑定变量，用pno变量来代替实际分区号，以避免过度解析
INSERT INTO systab PARTITION (:pno) VALUES (9,10);
由于System Partitioning的特殊性，所以很明显，这种类型的分区将不支持Partition Split操作，也不支持create table as select操作。
12C 对表分区维护的增强
Oracle Database 12c对表分区变化比较多，共分为下面几点
1.在线移动分区:通过MOVE ONLINE关键字实现在线分区移动。移动过程中，对表和被移动的分区可以执行查询操作，
DML语句以及分区的创建和维护操作。整个移动过程对用户来说是透明的。
2.多个分区同时操作：可以对多个分区同时进行维护操作，如将一年的12个分区合并到一个新的分区中，或者将一个分区
分成多个分区。可以通过FOR语句指定操作的每个语句，对于RANGE分区而言，也可以通过TO来指定处理分区的范围。
多个分区操作自动并行完成。
3.INTERVAL-REFERENCE分区：把11g的interval分区和reference分区结合，这样主表自动增加一个分区后，所有字表，
孙子表·····重重孙子表上都会自动随着外界列数据增加，自动创建新的分区。
4.TRUNCATE和EXCHANGE分区及子分区。五分是TRUNCATE还是EXCHANGE分区，在主表上执行，都可以级联的作用在字表，
孙子吧·····重重孙子表上同时执行。对于TRUNCATE而言，所有表的TRUNACATE操作在同一个事务中，如果中途失败，
会回滚到之前的状态。通过关键字CASCADE实现。
5.异步全局索引维护：对于非常大的分区表而言，UPDATE GLOBAL INDEX不再是痛苦。Oracle可以实现了异步维护的
功能，即使是几亿条的记录的全局索引，在分区维护操作，比如DROP或TRUNCATE后，仍然是VALID状态,索引不会失效，
不过索引的状态是包含OBSOLETE数据，当维护操作完成，索引状态恢复。
6.部分本地和全局索引：Oracle的所有可以在分区级别定义。无论全局索引还是本地所有都可以在分区表的部分分区
上建立，其他分区上则没有所有。当通过所有列访问全表数据时，Oracle通过UNION ALL实现，一部分通过索引扫描，
另一部分通过全分区扫描。这可以减少对历史数据的索引量，增强了灵活性。
具体例子：
1)添加多个新分区：
在12c之前，一次只能添加一个新分区到一个已存在的分区表。在12c中只需要一条单独的ALTER TABLE ADD PARTITION
命令就可以添加N个新分区。
ALTER TABLE EMP_PART ADD PARTITION PARTITION P4 VALUES LESS THAN(35000) PARTITION P5 VALUES LESS THAN(40000)
同样，只要MAXVALUE分区不存在，可以添加多个新分区到一个列表和系统分区表
2)删除、截断多个分区/子分区
通过在此之前，一次只能删除/截断一个分区。12c中通过ALTER TABLE table_name {TRUNCAT|DROP} PARTITIONS
ALTER TABLE EMP_PART DROP PARTITIONS P4,P5; ALTER TABLE EMP_PART TRUNCATE PARTITIONS P4,P5;
要保持索引更新，使用UPDATE INDEXES或UPDATE GLOBAL INDEXES语句。
ALTER TABLE EMP_PART DROP PARTITIONS P4,P5 UPDATE GLOBAL INDEXES; ALTER TABLE EMP_PART TRUNCATE PARTITIONS P4,P5 UPDATE GLOBAL INDEXES;
如果没有使用UPDATE GLOBAL INDEXES更新索引，也可以通过查询ORPHANED_ENTRIES字段找出是否有索引包含过期的条目
3)将单个分区分割为多个新分区
在此之前是无法单个命令完成这个操作的。
ALTER TABLE EMP_PART SPLIT PARTITIONS p_max INTO (PARTITION P4 VALUES LESS THAN (30000), PARTITION P5 VALUES LESS THAN(40000), PARTITION P_MAX);
4)将多个分区合并为一个分区
ALTER TABLE EMP_PART MERGE PARTITIONS P2,P3,P4 INTO PARTITION P_MERGE;
如果是连续分区，可以通过TO来
ALTER TABLE EMP_PART MERGE PARTITIONS P2 TO P4 INTO PARTITION P_MERGE;
普通表转分区表方法
将普通表转换成分区表有 4 种方法：
1. Export/import method
2. Insert with a subquery method
3. Partition exchange method
4. DBMS_REDEFINITION
详情参考
How to Partition a Non-partitioned Table [ID 1070693.6]
或者
表分区的相关操作
1.添加分区
添加新的分区有 2 中情况：
（ 1）原分区里边界是 maxvalue 或者 default。 这种情况下，我们需要把边界分区 drop 掉，加上新分区后，在添加上新的分区。 或者采用 split，对边界分区进行拆分。
（ 2）没有边界分区的。 这种情况下，直接添加分区就可以了。
以边界分区添加新分区示例：
（ 1）分区表和索引的信息如下：
SQL> create table custaddr 2 ( 3 id varchar2(15 byte) not null, 4 areacode varchar2(4 byte) 5 ) 6 partition by list (areacode) 7 ( 8 partition t_list556 values ('556') tablespace icd_service, 9 partition p_other values (default)tablespace icd_service 10 );
表已创建。
SQL> create index ix_custaddr_id on custaddr(id) 2 local ( 3 partition t_list556 tablespace icd_service, 4 partition p_other tablespace icd_service 5 );
索引已创建。
（ 2）插入几条测试数据：
SQL> insert into custaddr values('1','556'); 已创建 1 行。 SQL> insert into custaddr values('2','551'); 已创建 1 行。 SQL> insert into custaddr values('3','555'); 已创建 1 行。 SQL> commit; 提交完成。 SQL> select * from custaddr; ID AREA --------------- ---- 1 556 2 551 3 555 SQL> select * from custaddr partition(t_list556); ID AREA --------------- ---- 1 556 SQL>
（ 3）删除 default 分区
sql> alter table custaddr drop partition p_other; 表已更改。 sql> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; table_name partition_name ------------------------------ ------------------------------ custaddr t_list556
（ 4）添加新分区
SQL> alter table custaddr add partition t_list551 values('551') tablespace icd_service; 表已更改。 SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ CUSTADDR T_LIST556 CUSTADDR T_LIST551
（ 5）添加 default 分区
SQL> alter table custaddr add partition p_other values (default) tablespace icd_service; 表已更改。 SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ CUSTADDR T_LIST556 CUSTADDR T_LIST551 CUSTADDR P_OTHER
（ 6）对于局部索引， oracle 会自动增加一个局部分区索引。验证一下：
sql> select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name='ix_custaddr_id'; owner index_name table_name ---------------------- ------------------------------ ------------------ icd ix_custaddr_id custaddr sql> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='ix_custaddr_id'; index_owner index_name partition_name ------------------------------ ------------------------------ ------------------ icd ix_custaddr_id p_other icd ix_custaddr_id t_list551 icd ix_custaddr_id t_list556
分区索引自动创建了。
用 split方法示例
sql> alter table custaddr split partition p_other values('552') into (partition t_list552 tablespace icd_service, partition p_other tablespace icd_service); 表已更改。
–注意values(‘552’)，如果是 Range 类型的，使用 at， List 使用 Values。
SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ CUSTADDR T_LIST556 CUSTADDR T_LIST551 CUSTADDR T_LIST552 CUSTADDR P_OTHER SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IX_CUSTADDR_ID'; index_owner index_name partition_name ------------------------------ ------------------------------ ------------------ icd ix_custaddr_id p_other icd ix_custaddr_id t_list551 icd ix_custaddr_id t_list552 icd ix_custaddr_id t_list556
注意：分区表会自动维护局部分区索引。全局索引会失效，需要进行 rebuild。
2.删除分区
alter table T_TRACK drop partition p_2005_04;
3.添加子分区
alter table T_TRACK modify partition P_2005_01 add subpartition P_2005_01_P1017 values('P1017');
4.删除子分区
alter table T_TRACK drop subpartition p_2005_01_p1017;
5.截断一个分区表中的一个分区的数据：
alter table sales3 truncate partition sp1 --这种方式会使全局分区索引无效 alter table sales3 truncate partition sp1 update indexes --这种方式全局分区索引不会无效
说明：
Truncate 相对 delete 操作很快，数据仓库中的大量数据的批量数据加载可能
会有用到； 截断分区同样会自动维护局部分区索引，同时会使全局索引 unusable,需要重建
6.截断分区表的子分区
alter table comp truncate subpartition sub1
7.截断带有约束的分区表
a、禁用约束 alter table sales disable constraint dname_sales1 b、截断分区 alter table sales truncate partitoin dec c、启用约束 alter table sales enable constraint dname_sales1
8.查看一个表是不是分区表
select table_name,partitioned from user_tables; TABLE_NAME PAR ------------------------------ --- DEPT NO DEPT3 YES
9.将一个表的分区从一个表空间移动到另一个表空间
a、查看分区在哪个表空间 SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME, SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT'; b、移动分区 alter table sales move partiton sp1 tablespace tp; c、检查是否移动成功 SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME, SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT'; 移动表空间后，要重建索引，否则索引会变得无效 alter index xxx rebuild
注意： 分区移动会自动维护局部分区索引， oracle 不会自动维护全局索引，所以需要我们重新 rebuild 分区索引，具体需要 rebuild 哪些索引，可以通过
dba_part_indexes,dba_ind_partitions 去判断
SQL> Select index_name,status From user_indexes Where table_name='CUSTADDR'; INDEX_NAME STATUS ------------------------------ -------- IX_CUSTADDR_ID N/A
10.合并分区：
相邻的分区可以 merge 为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区， 原先的局部索引相应也会合并，全局索引会失效，需要 rebuild。
alter table sales3 merge partitons sp1,sp3 into partition sp3
合并后的分区名，不能是边界值较低的那个
11.与分区表相关的数据字典视图：
DBA_TAB_PARTITIONS DBA_IND_PARTITIONS DBA_TAB_SUBPARTITIONS DBA_IND_SUBPARTITIONS
