分区表的概念与创建

1 概念

oracle 11g 支持自动分区，不过得在创建表时就设置好分区。
　　如果已经存在的表需要改分区表，就需要将当前表 rename后，再创建新表，然后复制数据到新表，然后删除旧表就可以了。
　　分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。
　　Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

1.1 什么时候需要分区表

When to Partition a Table，官网的2个建议如下：

• Tables greater than 2GB should always be considered for partitioning.
• Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month’s data is updatable and the other 11 months are read only.

在oracle 10g中最多支持：1024k-1个分区：
Tables can be partitioned into up to 1024K-1 separate partitions

1.2 为什么要分区

• 一般一张表超过2G的大小，ORACLE是推荐使用分区表的。
• 这张表主要是查询，而且可以按分区查询，只会修改当前最新分区的数据，对以前的不怎么做删除和修改。
• 数据量大时查询慢。
• 便于维护，可扩展：11g 中的分区表新特性：Partition（分区）一直是 Oracle 数据库引以为傲的一项技术，正是分区的存在让 Oracle 高效的处理海量数据成为可能，在 Oracle 11g 中，分区技术在易用性和可扩展性上再次得到了增强。
• 与普通表的 sql 一致，不需要因为普通表变分区表而修改我们的代码。

1.3 分区的优点

• 由于将数据分散到各个分区中，减少了数据损坏的可能性
• 可以对单独的分区进行备份和恢复
• 可以将分区映射到不同的物理磁盘上，来分散IO
• 提高可管理性、可用性和性能

1.4 分区的类型

• 范围分区（range）
• 哈希分区（hash）
• 列表分区（list）
• 范围－哈希复合分区（range-hash）
• 范围－列表复合分区（range-list）

分区表的几种类型

---

2 相关查询

2.1 分区表查询

• 查询数据库中不同用户的分区表的数目

select owner,count(1) from dba_tables where partitioned='YES' group by owner; 

• 查询数据库中用户的分区表

select * from dba_tables where partitioned='YES' and owner='数据库用户名' ;

• 查询数据库中 该用户下的对应表的分区字段

select * from dba_part_key_columns where name='表名' and owner ='数据库用户名';  

• 查看该数据库中 所有用户的 所有分区表的和对应分区字段

SELECT * FROM all_PART_KEY_COLUMNS;

• 查询数据库中，该用户下对应的分区表的表名 和分区表所对应的分区字段

SELECT * FROM all_PART_KEY_COLUMNS t where  t.owner='数据库用户名'  and  t.name  in（select table_name from dba_tables where partitioned='YES'  and owner='数据库用户名' ）;

• 查看分区表有多少个分区

select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='DBA_AUDIT_TRAIL';

• 查看分区类型

select table_name,partitioning_type from user_part_tables where table_name='DBA_AUDIT_TRAIL';

注：
user_tab_partitions：可查看分区表的名字、归属表空间以及表的详细分区情况。
user_part_tables：可查看用户所有的分区表，以及分区方式

• 查看分区字段

select * from dba_part_key_columns where name='DBA_AUDIT_TRAIL';

• 查看分区内容

# select * from 表名 partition(分区名) ;
select * from dba_audit_trail SYS_P1003;

• 查看分区表信息

select table_name,partition_name,high_value from dba_tab_partitions where table_name='DBA_AUDIT_TRAIL';

• 显示数据库所有分区表的信息

select * from DBA_PART_TABLES;

• 显示当前用户可访问的所有分区表信息

select * from ALL_PART_TABLES;

• 显示当前用户所有分区表的信息

select * from USER_PART_TABLES;

• 显示表分区信息 显示数据库所有分区表的详细分区信息

select * from DBA_TAB_PARTITIONS;

• 显示当前用户可访问的所有分区表的详细分区信息

select * from ALL_TAB_PARTITIONS;

• 显示当前用户所有分区表的详细分区信息

select * from USER_TAB_PARTITIONS;

• 显示子分区信息 显示数据库所有组合分区表的子分区信息

select * from DBA_TAB_SUBPARTITIONS;

• 显示当前用户可访问的所有组合分区表的子分区信息

select * from ALL_TAB_SUBPARTITIONS;

• 显示当前用户所有组合分区表的子分区信息

select * from USER_TAB_SUBPARTITIONS;

• 显示分区列 显示数据库所有分区表的分区列信息

select * from DBA_PART_KEY_COLUMNS;

• 显示当前用户可访问的所有分区表的分区列信息

select * from ALL_PART_KEY_COLUMNS;

• 显示当前用户所有分区表的分区列信息

select * from USER_PART_KEY_COLUMNS;

• 显示子分区列 显示数据库所有分区表的子分区列信息

select * from DBA_SUBPART_KEY_COLUMNS;

• 显示当前用户可访问的所有分区表的子分区列信息

select * from ALL_SUBPART_KEY_COLUMNS;

• 显示当前用户所有分区表的子分区列信息

select * from USER_SUBPART_KEY_COLUMNS;

• 怎样查询出oracle数据库中所有的的分区表

select * from user_tables a where a.partitioned='YES';

• 跨分区查询

select sum( *) from
(select count(*) cn from t_table_SS PARTITION (P200709_1)
union all
select count(*) cn from t_table_SS PARTITION (P200709_2)
);

3 分区表的维护操作

3.1 创建分区表自动分区

3.1.1 创建interval分区表

create table test_range (idnumber,test_date date)
partition by range(test_date) interval(numtodsinterval(1,‘day’))
(partition p_20160612 values less than(to_date(‘20160613’,‘yyyymmdd’)));

SQL> select table_name,partitioning_type,partition_count,interval from user_part_tableswhere table_name='TEST_RANGE';
TABLE_NAME  PARTITION PARTITION_COUNT INTERVAL
-------------------- ------------------------ ---------------------------------------------
TEST_RANGE    RANGE    1048575 NUMTODSINTERVAL(1,'DAY')

插入测试数据（存在分区）

SQL> insert into TEST_RANGE values(1,to_date('20160612','yyyymmdd'));
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';
TABLE_NAME           PARTITION_NAME
--------------------------------------------------
TEST_RANGE           P_20160612

插入测试数据（不存在分区）

SQL> insert into TEST_RANGE values(1,to_date('20160613','yyyymmdd'));
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';
 
TABLE_NAME           PARTITION_NAME
--------------------------------------------------
TEST_RANGE           P_20160612
TEST_RANGE           SYS_P122

# 先插入较大数值
SQL> insert into TEST_RANGE values (1,to_date('20160615','yyyymmdd')); 
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';
TABLE_NAME           PARTITION_NAME
--------------------------------------------------
TEST_RANGE           P_20160612
TEST_RANGE           SYS_P122
TEST_RANGE           SYS_P123                 # 新增分区
# 先插入中间数值
SQL> insert into TEST_RANGE values (1,to_date('20160614','yyyymmdd')); 
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';
TABLE_NAME           PARTITION_NAME
--------------------------------------------------
TEST_RANGE           P_20160612
TEST_RANGE           SYS_P122
TEST_RANGE           SYS_P123
TEST_RANGE           SYS_P124   			# 新增分区

说明：
对于interval分区表插入“不存在分区”对应的数值时，会自动生成按照interval生成相应分区；若先插入较大数值，再插入较小数值，分区会按照interval依次生成，如test_range只存在20160612分区，插入20160615数值时会生成20160615分区，再插入20160614数值时会再生成20160614分区。

3.1.2 创建template分区表

drop table test_range purge;
create table test_range (idnumber,test_date date)
partition by range(test_date) interval(numtodsinterval(1,‘day’))
subpartition by hash(id)
subpartition template
(subpartition a,
subpartition b,
subpartition c)
(partitionp_20160612 values less than (to_date(‘20160613’,‘yyyymmdd’)));

插入测试数据

SQL> insert into test_rangevalues(1,sysdate+2);
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name,subpartition_namefrom user_tab_subpartitions where table_name ='TEST_RANGE';
TABLE_NAME                     SUBPARTITION_NAME
------------------------------------------------------------
TEST_RANGE                     P_20160612_A
TEST_RANGE                     P_20160612_B
TEST_RANGE                     P_20160612_C
TEST_RANGE                     SYS_SUBP125
TEST_RANGE                     SYS_SUBP126
TEST_RANGE                     SYS_SUBP127

发现新生成的分区并未按照template形式

SQL> alter table test_range addpartition P_20160615 values less than(to_date('20160616','yyyymmdd'));
alter table test_range add partition P_20160615values less than(to_date('20160616','yyyymmdd'))
           *
ERROR at line 1:
ORA-14760: ADDPARTITION is not permitted on Interval partitioned objects

采取interval keyword创建的分区表不支持自己add partition

3.1.3 不采取interval创建template分区表

drop table test_range purge;
create table test_range (idnumber,test_date date)
partition by range(test_date)
subpartition by hash(id)
subpartition template
(subpartition a,
subpartition b,
subpartition c)
(partitionp_20160612 values less than (to_date(‘20160613’,‘yyyymmdd’)));

添加分区

SQL> alter table test_range addpartition P_20160615 values less than(to_date('20160616','yyyymmdd'));
Table altered.
SQL>
SQL> select table_name,subpartition_namefrom user_tab_subpartitions where table_name ='TEST_RANGE';
TABLE_NAME                     SUBPARTITION_NAME
------------------------------------------------------------
TEST_RANGE                     P_20160612_A
TEST_RANGE                     P_20160612_B
TEST_RANGE                     P_20160612_C
TEST_RANGE                     P_20160615_A
TEST_RANGE                     P_20160615_B
TEST_RANGE                     P_20160615_C

说明：
同时使用partition interval & subpartition template关键字创建的分区表，子分区按照系统自定义命名子分区名字，不按照subpartition template命名子分区，并且不支持自己添加分区；
仅使用subpartition template关键字创建的分区表，子分区按照subpartition template命名子分区。

3.2 按天、周、月、年自动分区

3.2.1 按年创建

numtoyminterval(1, ‘year’)

• 按年创建分区表

create table test_part
(
ID NUMBER(20) not null,
REMARK VARCHAR2(1000),
create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'year'))
(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

• 创建主键

alter table test_part add constraint test_part_pk primary key (ID) using INDEX;

create/recreate indexes create index test_part_create_time on TEST_PART (create_time);

3.2.2 按月创建

numtoyminterval(1, ‘month’)

• 按年创建分区表

create table test_part
(
ID NUMBER(20) not null,
REMARK VARCHAR2(1000),
create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

• 创建主键

alter table test_part add constraint test_part_pk primary key (ID) using INDEX;
create/recreate indexes create index test_part_create_time on TEST_PART (create_time);

3.2.3 按天创建

numtodsinterval(1, ‘day’)

• 按年创建分区表

create table test_part
(
ID NUMBER(20) not null,
REMARK VARCHAR2(1000),
create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtodsinterval(1, 'day'))
(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

• 创建主键

alter table test_part add constraint test_part_pk primary key (ID) using INDEX;
create/recreate indexes create index test_part_create_time on TEST_PART (create_time);

3.2.4 按周创建

numtodsinterval(7, ‘day’)

• 按周创建分区表

create table test_part
(
ID NUMBER(20) not null,
REMARK VARCHAR2(1000),
create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtodsinterval(7, 'day'))
(partition part_t01 values less than(to_date('2018-11-12', 'yyyy-mm-dd')));

• 创建主键

alter table test_part add constraint test_part_pk primary key (ID) using INDEX;

3.2.5 测试

测试可以添加几条数据来看看效果，oracle 会自动添加分区。

• 查询当前表有多少分区

select table_name,partition_name from user_tab_partitions where table_name='TEST_PART';

• 查询这个表的某个（SYS_P21）里的数据

select * from TEST_PART partition(SYS_P21);

3.2.6 numtoyminterval 和 numtodsinterval 的区别

1.numtodsinterval(<x>,<c>)
x 是一个数字，c 是一个字符串。把 x 转为 interval day to second 数据类型。
常用的单位有 (‘day’,‘hour’,‘minute’,‘second’)。
测试一下：

select sysdate, sysdate + numtodsinterval(4,'day') as res from dual;

2.numtoyminterval (<x>,<c>)
将 x 转为 interval year to month 数据类型。
常用的单位有 (‘year’,‘month’)。
测试一下：

select sysdate, sysdate + numtoyminterval(3, 'year') as res from dual;

3.2.7 默认分区

partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd'))
表示小于 2018-11-01 的都放在 part_t01 分区表中。

3.3 在现有表的基础上建立分区表

• 重命名

alter table test_part rename to test_part_temp;

• 创建 partition table

create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t1 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

• 创建主键

alter table test_part add constraint test_part_pk_1 primary key (ID) using INDEX;

• 将 test_part_temp 表里的数据迁移到 test_part 表中

insert into test_part_temp select * from test_part;

• 为分区表设置索引

create/recreate indexes
create index test_part_create_time_1 on TEST_PART (create_time);

• 删除老的 test_part_temp 表

drop table test_part_temp purge;

• 允许分区表的分区键是可更新。
  1.当某一行更新时，如果更新的是分区列，并且更新后的列植不属于原来的这个分区，
  2.如果开启了这个选项，就会把这行从这个分区中 delete 掉，并加到更新后所属的分区，此时就会发生 rowid 的改变。
  3.相当于一个隐式的 delete + insert ，但是不会触发 insert/delete 触发器。

alter table test_part enable row movement;

3.4 添加分区

例1：给SALES表添加了一个P3分区

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));

注意：以上添加的分区界限应该高于最后一个分区界限。

例2：给SALES表的P3分区添加了一个P3SUB1子分区

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

• range partitioned table

ALTER TABLE range_example ADD PARTITION part04 VALUES LESS THAN (TO_DATE('2008-10-1 00:00:00','yyyy-mm-ddhh24:mi:ss'));

• list partitioned table

ALTER TABLE list_example ADD PARTITION part04 VALUES('TE');

• Adding Values for a List Partition

ALTER TABLE list_example MODIFY PARTITION part04 ADD VALUES('MIS');

• Dropping Values from a List Partition

ALTER TABLE list_example MODIFY PARTITION part04 DROP VALUES('MIS');

• hash partitioned table

ALTER TABLE hash_example ADD PARTITION part03;

• 增加subpartition

ALTER TABLE range_hash_example MODIFY PARTITION part_1 ADD SUBPARTITION part_1_sub_4;

注:hash partitioned table新增partition时，现有表的中所有data都有重新计算hash值，然后重新分配到分区中，所以被重新分配的分区的indexes需要rebuild 。

3.5 删除分区

ALTER TABLE SALES DROP PARTITION P3;
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

3.6 截断分区

截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。

ALTER TABLE SALES TRUNCATE PARTITION P2;

截断子分区：

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

3.7 合并分区

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2 UPDATE INDEXES;

注意，如果省略update indexes子句的话，必须重建受影响的分区的index;

ALTER TABLE range_example MODIFY PARTITION part02 REBUILD UNUSABLE LOCAL INDEXES;

3.8 拆分分区

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

注意：如果是RANGE类型的，使用at，LIST类型的使用values。

3.9 接合分区（coalesce）

分区接合是针对散列分区或者*-散列子分区的，目的是减少分区数。当某个散列分区接合后，Oracle将其分区的数据分散到其它分区中。被接合的分区是由数据库选择的，接合完成后该分区会被删除，且如果没有使用UPDATE INDEX子句，本地索引和全局索引均将变成不可用，一般需要重建索引。

• 散列分区表的散列分区接合

ALTER TABLE table_name COALESCE PARTITION;

• 散列分区表的散列子分区接合

ALTER TABLE table_name MODIFY PARTITION partition_name COALESCE SUBPARTITION;

3.10 重命名表分区

ALTER TABLE table_name RENAME PARTITION old_name TO new_name;
ALTER TABLE table_name RENAME SUBPARTITION old_name TO new_name;

3.11 交换分区

可以将一个分区(子分区)和非分区表进行数据交换，oracle交换的方法是其实是对逻辑存储段进行交换。同样，散列|范围|列表分区可以与复合*-散列|*-范围|*-列表分区间也可以进行数据交换。当应用中需要将非分区表的数据转换进入分区表的分区时非常高效实用。使用INCLUDEING INDEXES子句可以同步将本地索引也进行交换，使用WITH VALIDATATION子句还可以实现行数据的验证。
交换分区时如果不带UPDATE INDEXES子句，则全局索引或全局索引基于的分区将变为不可用。
1）三种单级分区与非分区表的交换

ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE nonpartition_name;

2）单级散列分区表与复合*-散列分区的交换
此时要求单级散列分区表的分区键与复合*-散列分区表的子分区键相同，且两个交换的散列分区数也得相同，此外也不能指定单级散列分区表的某一个分区进行交换。
3）复合*-散列分区中的散列子分区交换
使用ALTER TABLE … EXCHANGE SUBPARTITION与非分区表进行交换，且只能跟非分区表进行交换。
4）单级列表分区表与复合*-列表分区的交换
此时要求List分区表的分区键和*-List表的子分区键相匹配，前者的List分区数与后者的List子分区相同。
复合*-列表分区中的列表子分区交换
同样也是使用ALTER TABLE … EXCHANGE SUBPARTITION与非分区表进行交换，且只能跟非分区表进行交换。
6）单级范围分区表与复合*-范围分区表的交换
此时要求Range分区表的分区键和*-Range表的子分区键相匹配，前者的Range分区数与后者的Range子分区相同。
7）复合*-范围分区中的范围子分区交换
同样也是使用ALTER TABLE … EXCHANGE SUBPARTITION与非分区表进行交换，且只能跟非分区表进行交换。

3.12 移动分区

alter table custaddr move partition P_OTHER tablespace system;
alter table custaddr move partition P_OTHER tablespace icd_service;

分区移动会自动维护局部分区索引，oracle不会自动维护全局索引，所以需要我们重新rebuild分区索引，具体需要rebuild哪些索引，可以通过dba_part_indexes,dba_ind_partitions去判断。

Select index_name,status From user_indexes Where table_name='CUSTADDR';