Oracle partition table 分区表详解

LuoXinLoves

2020-02-02

分区表就是通过使用分区技术，将一张大表，拆分成多个表分区（独立的segment），从而提升数据访问的性能，以及日常的可维护性。
分区表中，每个分区的逻辑结构必须相同。如：列名、数据类型。
分区表中，每个分区的物理存储参数可以不同。如：各个分区所在的表空间。
对于应用而言完全透明，分区前后没有变化，不需要进行修改。

分区表相关试图
显示表分区信息显示数据库所有分区表的详细分区信息﹕
DBA_TAB_PARTITIONS

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

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

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

分区表分为四类：1、范围分区表 2、列表分区表 3、哈希分区表 4、组合分区表
1、RANGE 范围分区
说明：针对记录字段的值在某个范围。
规则：
（1）、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。
分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
（2）、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
（3）、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，
也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。若不添加maxvalue的分区插入数值一旦超过设置的最大上限会报错。

Example:
create table RPTUSER.RPT_TMP_10086BDL_bak
(
HANDLEDATE DATE,
TEL_CALLING VARCHAR2(15)
) TABLESPACE "IS_IEF_CBT"
PARTITION BY RANGE ("HANDLEDATE") --按date形式分区
( partition P201805 values less than (TO_DATE(‘2018-06-01 00:00:00‘,‘YYYY-MM-DD hh24:mi:ss‘)) tablespace IS_IEF_CBT,
partition P201806 values less than (TO_DATE(‘2018-07-01 00:00:00‘,‘YYYY-MM-DD hh24:mi:ss‘)) tablespace IS_IEF_CBT,
partition P201807 values less than (TO_DATE(‘2018-08-01 00:00:00‘,‘YYYY-MM-DD hh24:mi:ss‘)) tablespace IS_IEF_CBT,
partition pmax values less than (MAXVALUE) tablespace IS_IEF_CBT);

CREATE TABLE PART_TAB_CUSTOMER_BY_RANGE
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR2(30) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
SEX VARCHAR2(10),
STATUS VARCHAR2(10),
INSERT_DATE DATE
)
PARTITION BY RANGE (CUSTOMER_ID) --按number形式分区
(
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE part_Data1,
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE part_Data2,
PARTITION CUS_PART2 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3
)

2、LIST 列表分区
说明：该分区的特点是某列的值只有有限个值，基于这样的特点我们可以采用列表分区。
规则：默认分区为DEFAULT，若不添加DEFAULT的分区插入数值不属于所设置的分区会报错。
在定义范围分区时，每个分区定义必须使用 values（‘value01‘,‘value02‘....）子句。表示该分区存储包含相关value值的数据行。
在定义范围分区时，最后一个分区可以是values（DEFAULT）。表示该分区存储未在其他分区定义的数据行。

Example:
CREATE TABLE PART_TAB_CUSTOMER_BY_LIST
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
SEX VARCHAR2(10),
CORP_ID VARCHAR2(3),
INSERT_DATE DATE，
SUM_DATE varchar2(4)
)
PARTITION BY LIST (SEX) --SEX列只有两个值,采用列表分区
(
PARTITION MALE VALUES (‘男‘) TABLESPACE part_Data1,
PARTITION FEMALE VALUES (‘女‘) TABLESPACE part_Data2
)

CREATE TABLE PART_TAB_CUSTOMER_BY_LIST
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
SEX VARCHAR2(10),
CORP_ID VARCHAR2(3),
INSERT_DATE DATE，
SUM_DATE varchar2(4)
)
PARTITION BY LIST (SUM_DATE)
(
PARTITION SUM_DATE1 VALUES (‘2012‘) TABLESPACE part_Data1,
PARTITION SUM_DATE2 VALUES (‘2013‘) TABLESPACE part_Data2，
PARTITION SUM_DATE2 VALUES (DEFAULT) TABLESPACE part_Data2
)

也可以VALUES里面有多个值如PARTITION EMPLOYEE_PART01 VALUES (‘PRESIDENT‘,‘MANAGER‘,‘ANALYST‘)

3、HASH 散列分区
说明：这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。
规则：当列的值没有合适的条件，没有范围的规律，也没有固定的值，建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，
使得这些分区大小一致。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。

Example:
创建hash分区有两种方法：一种方法是指定分区的名字，另一种方法是指定分区数量。

例一、常规方法指定分区名字
CREATE TABLE PART_TAB_CUSTOMER_BY_HASH
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR2(30) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
SEX VARCHAR2(10),
STATUS VARCHAR2(10),
INSERT_DATE DATE
)
PARTITION BY HASH (CUSTOMER_ID) --按id散列
(
PARTITION hash1 TABLESPACE part_Data1,
PARTITION hash2 TABLESPACE part_Data2
)

例二、指定分区数量
PARTITION BY HASH (empno) PARTITIONS 2 STORE IN (part_Data1,part_Data2);
--往往我们不需要知道bash分区的名字，因为数据放在哪个分区是oracle根据bash算法存放的，并不是用户指定，
所以当用户插入一条记录，并不能确定放在哪个分区，这个不同于range和list

4.组合分区
组合分区中，主要通过在不同列上，使用“范围分区”、“列表分区”以及“HASH分区”不同组合方式，进而实现组合分区。
组合分区中，分区本身没有相应的segment，可以认为是一个逻辑容器，只有子分区拥有实际的segment，用于存放数据。
在11g以后，组合分区新增了四种组合方式：“RANGE-RANGE”、“LIST-RANGE”、“LIST-HASH”以及“LIST-LIST”。

Example:
以LIST-LIST的组合方式为例，创建组合分区

CREATE TABLE "EMPLOYEE_LIST_LIST_PART"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
PARTITION BY LIST (DEPTNO) --LIST-LIST的组合方式,先分区DEPTNO再分区JOB
SUBPARTITION BY LIST (JOB)
(
PARTITION EMPLOYEE_DEPTNO_10 VALUES (10) TABLESPACE test_tbs_01
( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES (‘MANAGER‘),
SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)
),
PARTITION EMPLOYEE_DEPTNO_20 VALUES (20) TABLESPACE test_tbs_02
( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES (‘MANAGER‘),
SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)
),
PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT) TABLESPACE test_tbs_03
( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES (‘MANAGER‘),
SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT)
)
);

增加分区

-- range partitioned table
ALTER TABLE range_example ADD PARTITION part04 VALUES LESS THAN (TO_DATE(‘2008-10-1 00:00:00‘,‘yyyy-mm-dd hh24: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;

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

--增加subpartition
ALTER TABLE range_hash_example MODIFY PARTITION part_1 ADD SUBPARTITION part_1_sub_4; --注意复合分区这里是MODIFY

删除分区
对range分区表删除分区
ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP PARTITION P3;
对range分区表list子分区删除子分区
ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP SUBPARTITION P4SUB1;

对于哈希分区表,哈希复合分区表,range-hash分区表
-- 减少hash 分区的个数，一次减少一个。不能指定减少partition的名称。
ALTER TABLE hash_example COALESCE PARTITION ;
--subpartition 的语法对于如下
ALTER TABLE diving MODIFY PARTITION us_locations
COALESCE SUBPARTITION;

合并父分区
ALTER TABLE range_example
MERGE PARTITIONS part01_1,part01_2 INTO PARTITION part01
UPDATE INDEXES;

如果省略update indexes子句的话，必须重建受影响的分区的index 。
ALTER TABLE range_example MODIFY PARTITION part02 REBUILD UNUSABLE LOCAL INDEXES;

分割分区
1、range类型分区的分割
ALTER TABLE range_example
SPLIT PARTITION part01
AT (TO_DATE(‘2008-06-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘))
INTO ( PARTITION part01_1,PARTITION part01_2
);

一个分区一次性只能分割成两个分区，at关键字后面指定的值为第一个分区的range范围，默认为less than 。
2、list类型分区的分割
ALTER TABLE list_example
SPLIT PARTITION part01 VALUES(‘ME‘,‘PE‘)
INTO ( PARTITION part01_1, PARTITION part01_2
);
3、Range_Hash类型分区的分割新分区会对原有分区的subpartition做rehash的动作。如果在分割是指定subpartition的个数，则按新规则rehash subpartition，如果没有指定则保留原有subpartition的个数不变。

ALTER TABLE range_hash_example SPLIT PARTITION part_1
AT (TO_DATE(‘2008-07-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) INTO (
PARTITION part_1_1 SUBPARTITIONS 2 STORE IN (tbs01,tbs02),
PARTITION part_1_2
);
subpartitions 2 -- 指定新分区的subpartition的个数，store in 子句指定subpartition存储的tablespace

分区索引
1.建立普通的索引
create index com_index_range_example_id on range_example(id);

2.建立本地分区索引
create index local_index_range_example_id on range_example(id)local;

3.建立全局分区索引
create index gidx_range_exampel_id on range_example(id)
GLOBAL partition by range(id)
(
part_01 values less than(1000),
part_02 values less than(MAXVALUE)
);
对于分区索引的删除，local index 不能指定分区名称，单独的删除分区索引。local index 对应的分区会伴随着data分区的删除而一起被删除。
global partition index 可以指定分区名称，删除某一分区。但是有一点要注意，如果该分区不为空，则会导致更高一级的索引分区被置为UNUSABLE 。
ALTER INDEX gidx_range_exampel_id drop partition part_01 ; 此句将导致part_02 状态为UNUSABLE 。