Oracle-分区表解读
oracle,分区表2016-10-31
分区表的概述
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。 表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
对于数据库中的超大型表，可通过把它的数据分成若干个小表，从而简化数据库的管理活动。对于每一个简化后的小表，我们称为一个单个的分区。
对于分区的访问，我们不需要使用特殊的SQL查询语句或特定的DML语句，而且可以单独的操作单个分区，而不是整个表。同时可以将不同分区的数据放置到不同的表空间。
Oracle提供分区技术以支持VLDB（VeryLargeDataBase）.
分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。
对于外部应用程序来说，虽然存在不同的分区，且数据位于不同的表空间，但逻辑上仍然是一张表。
Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。
查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。
在oracle 10g中最多支持：1024k-1个分区。
什么时候需要分区表？
官网给出的建议：
- 1、表的大小超过2GB。
- 2、表中包含历史数据，新的数据被增加都新的分区中。需要将历史数据和当前的数据分开单独处理，比如历史数据仅仅需要只读，而当前数据则实现DML
表分区的优缺点
优点
- 1、改善查询性能：只需要搜索特定分区，而非整张表，提高查询速度。
- 2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用;
- 3、维护方便：可以单独备份和恢复每个分区。如果表的某个分区出现故障，需要修复数据，只修复该分区即可; 节约维护时间，单个分区的数据装载，索引重建，备份，维护等将远小于整张表的维护时间。
- 4、均衡I/O：将不同的分区映射到不同的磁盘以平衡I/O，提高并发，改善整个系统性能。
缺点：
- 1.已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。
特点
- 共性：不同的分区之间必须有相同的逻辑属性，比如表名，列名，数据类型，约束等，
- 个性：各个分区可以有不同的物理属性，比如pctfree, pctused, and tablespaces.
- 分区独立性：即使某些分区不可用，其他分区仍然可用。
- 特殊性：含有LONG、LONGRAW数据类型的表不能进行分区
ORACLE分区类型
Oracle 10g提供了以下几种分区类型：
- （1）范围分区（range）；
- （2）哈希分区（hash）；
- （3）列表分区（list）；
- （4）范围－哈希复合分区（range-hash）；
- （5）范围－列表复合分区（range-list）。
范围分区（range）
范围分区特性
Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中，并且分区键经常采用日期。
当使用范围分区时，请考虑以下几个规则：
1、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
3、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。
创建范围分区时，必须指定以下内容
分区方法：range
分区列
标识分区边界的分区描述
如：
create table pdba (
id number,
time date)
partition by range (time) --创建基于日期的范围分区并存储到不同的表空间
(
partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),
partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),
partition p4 values less than (maxvalue)
) create table r --创建基于值范围的分区，分区子句未指定表空间时则位于缺省的表空间
(a int)
partition by range (a)
(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (maxvalue)
);
select * from r partition (p1) --查看分区中的数据说明：
partition by 用于指定分区方式
range 表示分区的方式是范围划分
partition pn 用于指定分区的名字
values less than 指定分区的上界（上限）
添加分区：
ALTER TABLE r
add partition p5 values less than (xxx ) tablespace xx;
查看分区表相关信息：
SELECT table_name,partition_name,subpartition_count,
tablespace_name,user_stats from user_tab_partitions;
栗子1
假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。
CREATE TABLE CUSTOMER
(
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),
STATUS CHAR(1)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
)栗子2-按时间划分
按时间划分
CREATE TABLE ORDER_ACTIVITIES
(
ORDER_ID NUMBER(7) NOT NULL,
ORDER_DATE DATE,
TOTAL_AMOUNT NUMBER,
CUSTOTMER_ID NUMBER(7),
PAID CHAR(1)
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01-MAY-2016','DD-MON-YYYY')) TABLESPACEORD_TS01,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2016','DD-MON-YYYY')) TABLESPACE ORD_TS02,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2016','DD-MON-YYYY')) TABLESPACE ORD_TS03
)栗子3-MAXVALUE
CREATE TABLE RangeTable
(
idd INT PRIMARY KEY ,
iNAME VARCHAR(10),
grade INT
)
PARTITION BY RANGE (grade)
(
PARTITION part1 VALUES LESS THEN (1000) TABLESPACE Part1_tb,
PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb
);哈希分区（hash）
也被成为散列分区。
这类分区是在列值上使用哈希算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散哈希分区。
哈希分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。
对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。
创建散列分区时，必须指定以下信息
分区方法：hash
分区列
分区数量或单独的分区描述
分裂、删除和合并分区不能应用于Hash分区，但是，Hash分区能够合并和添加。
创建hash分区有两种方法：一种方法是指定分区数量，另一种方法是指定分区的名字，
但两者不能同时指定。
CREATE TABLE HASH_TABLE
(
COL NUMBER(8),
INF VARCHAR2(100)
)
PARTITION BY HASH (COL)
(
PARTITION PART01 TABLESPACE HASH_TS01,
PARTITION PART02 TABLESPACE HASH_TS02,
PARTITION PART03 TABLESPACE HASH_TS03
)在这里，我们指定了每个分区的表空间。
简写的方式：
CREATE TABLE emp
(
empno NUMBER (4),
ename VARCHAR2 (30),
sal NUMBER
)
PARTITION BY HASH (empno)
PARTITIONS 8 --表空间的数量
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);--表空间的名称hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。
列表分区（list）
该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。
在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。
List分区时必须指定的以下内容
分区方法：list
分区列
分区描述，每个描述指定一串文字值(值的列表),它们是分区列(它们限定将被包括在分区中的行)的离散值
比如：
在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。
create table custaddr
(
id varchar2(15 byte) not null,
areacode varchar2(4 byte)
)
partition by list (areacode)
( partition t_list025 values ('025'),
partition t_list372 values ('372') ,
partition t_list510 values ('510'),
partition p_other values (default)
)栗子1
CREATE TABLE PROBLEM_TICKETS
(
PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR2(2000),
CUSTOMER_ID NUMBER(7) NOT NULL,
DATE_ENTERED DATE NOT NULL,
STATUS VARCHAR2(20)
)
PARTITION BY LIST (STATUS)
(
PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01,
PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02栗子2
CREATE TABLE ListTable
(
id INT PRIMARY KEY ,
name VARCHAR (20),
area VARCHAR (10)
)
PARTITION BY LIST (area)
(
PARTITION part1 VALUES ('guangdong','beijing') TABLESPACE Part1_tb,
PARTITION part2 VALUES ('shanghai','nanjing') TABLESPACE Part2_tb
);
)组合分区
如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。
组合分区呢在10g中有两种：range-hash，range-list。
注意顺序，根分区只能是range分区，子分区可以是hash分区或list分区。
组合分区使用range方法分区，在每个子分区中使用hash方法进行再分区。
组合分区比range分区更容易管理，充分使用了hash分区的并行优势。组合分区支持历史数据和条块数据两者。
如添加新的RANGE分区，同时为DML操作提供更高层的并行性。
创建组合分区时，需要指定如下内容：
分区方法：range
分区列
标识分区边界的分区描述
子分区方法：hash
子分区列
每个分区的子分区数量，或子分区的描述
范围－哈希复合分区（range-hash）
这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。
create table xiaogongjiang
(
transaction_id number primary key,
item_id number(8) not null,
item_description varchar2(300),
transaction_date date
)
partition by range(transaction_date)
subpartition by hash(transaction_id)
subpartitions 3
store in (xgj_space01,xgj_space02,xgj_space03)
(
partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
partition part_02 values less than(to_date(‘2016-01-01’,’yyyy-mm-dd’)),
partition part_03 values less than(maxvalue)
);范围－列表复合分区（range-list）
这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区
CREATE TABLE SALES
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009
(
SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
),
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009
(
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
)
)create table test
(
transaction_id number primary key,
transaction_date date
)
partition by range(transaction_date) subpartition by hash(transaction_id)
subpartitions 3 store in (tablespace01,tablespace02,tablespace03)
(
partition part_01 values less than(to_date(’2009-01-01’,’yyyy-mm-dd’)),
partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)),
partition part_03 values less than(maxvalue)
);
create table emp_sub_template (deptno number, empname varchar(32), grade number)
partition by range(deptno) subpartition by hash(empname)
subpartition template
(subpartition a tablespace ts1,
subpartition b tablespace ts2,
subpartition c tablespace ts3,
subpartition d tablespace ts4
)
(partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (maxvalue)
);
create table quarterly_regional_sales
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
tablespace ts4
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 ('ok', '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 ('ok', 'tx')
),
partition q3_1999 values less than (to_date('1-oct-1999','dd-mon-yyyy'))
(subpartition q3_1999_northwest values ('or', 'wa'),
subpartition q3_1999_southwest values ('az', 'ut', 'nm'),
subpartition q3_1999_northeast values ('ny', 'vm', 'nj'),
subpartition q3_1999_southeast values ('fl', 'ga'),
subpartition q3_1999_northcentral values ('sd', 'wi'),
subpartition q3_1999_southcentral values ('ok', 'tx')
),
partition q4_1999 values less than ( to_date('1-jan-2000','dd-mon-yyyy'))
(subpartition q4_1999_northwest values ('or', 'wa'),
subpartition q4_1999_southwest values ('az', 'ut', 'nm'),
subpartition q4_1999_northeast values ('ny', 'vm', 'nj'),
subpartition q4_1999_southeast values ('fl', 'ga'),
subpartition q4_1999_northcentral values ('sd', 'wi'),
subpartition q4_1999_southcentral values ('ok', 'tx')
)
);Interval Partitioning
数据库管理员日常要做的一件重复而无聊的工作 比如每隔一天要生成新的24个分区，用以存储第二天的数据。而在11g中这项工作可以交由Oracle自动完成了，基于Range和List的Interval Partitioning分区类型登场。
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自动创建的一个新分区中。
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操作。
普通表转分区表方法
参考Dave大神的博文
How to Partition a Non-partitioned Table [ID 1070693.6]
Dave的PDF文档说明
表分区的相关操作
1.添加分区
alter table T_TRACK add partition P_2005_04
values less than(to_date('2005-05-01','yyyy-MM-dd'))
(
subpartition P_2005_04_P1013 values('P1013'),
subpartition P_2005_04_P1013 values('P1014'),
subpartition P_2005_04_P1013 values('P1015'),
subpartition P_2005_04_P1013 values('P1016')
)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
这种方式全局分区索引不会无效
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
10.合并分区：
alter table sales3 merge partitons sp1,sp3 into partition sp3合并后的分区名，不能是边界值较低的那个
11.删除分区：
alter table scott.sales_composite drop partition SALES_JAN2000;
12.与分区表相关的数据字典视图：
DBA_TAB_PARTITIONS
DBA_IND_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_SUBPARTITIONS