oracle分区表详解
关于分区表的概念及操作：
1.表空间及分区表的概念
2.表分区的具体作用
3.表分区的优缺点
4.表分区的几种类型及操作方法
5.对表分区的维护性操作
ORACLE提供了分区技术以支持VLDB（vary large database）。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。
oracle的分区可以包含多个分区，，每个分区都是一个独立的段（SEGMENT）,可以存放在不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可通过在查询时直接指定分区的方法来查询。
(1)表空间及表分区的概念
表空间：是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中。但是主要存放的是表，所以称为表空间
分区表：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑给表进行分区。表分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放在多个表空间（物理文件上），这样查询数据时，不至于每次都扫描整张表。
(2)表分区的具体作用
分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段就做分区。每个分区有自己的名称，还可以选择自己的存储特性，从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可以进行集体管理也可以进行单独管理。这就使数据库管理员在管理分区的对象时，有相当大的灵活性，但是从应用程序的角度来看，分区后的表和非分区的表完全相同，使用DML命令访问分区后的表时，无需任何修改
(3)什么时候需要分区表：
表的大小超过2GB
表中包含历史数据，新的数据被增加到新的分区中
(4)表分区的优缺点：
优点：
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度
增强可用性：如果表的某个分区出现故障，表的其他的分区的数据仍然可用
维护方便：如果表的某个分区出现故障，需要修复数据，只需要修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘一平衡I/O，改善整个系统性能
缺点：
已经存在的表没有办法可以直接转化为分区表。不过ORACLE提供了在线重定义表的功能
二。ORACLE中主要的几种分区类型
范围分区（range）
列表分区（list）
散列分区(哈希分区)（hash）
范围-哈希复合分区（range-hash）
范围-列表复合分区（range-list）
(1) range分区
range分区是以列的值的范围来作为分区的划分的条件，将记录存放在列值所在range分区中。
当使用范围分区时，请考虑以下几个规则：
1>每一个分区都必须有一个VALUESLESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2>所有的分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值
3>在最高的分区中，MAXVALUE被定义，MAXVALUE代表了一个不确定的值，这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUELESS
THEN的值，同时包括空值。
4>如果创建了分区maxvalue,那么后期将不能添加分区。
例如：
每个分区中存的数据如下：
create table test_partition_range
( emp_id number,
emp_name varchar2(30),
emp_sex varchar2(4),
emp_age number
)
partition by range(emp_id)
(
partition t_1 values less than (10),
partition t_2 values less than (20),
partition t_3 values less than (30),
partition t_max values less than(maxvalue)
)
select 't_1',a.* from test_partition_range partition(t_1) a
union all
select 't_2',b.* from test_partition_range partition(t_2) b
union all
select 't_3', c.* from test_partition_range partition(t_3) c
union all
select 't_max',d.* from test_partition_range partition(t_max) d
;
创建范围分区的时候要按照顺序来，因为下一个分区隐藏的下限值是上一个分区的分界线
这是范围分区的使用，范围分区一般会用作日期分区
create table ODSGVS_ZSD_FPDDCX_H_NEW
(
GMDM VARCHAR2(100),
GMMS VARCHAR2(200),
VKORG VARCHAR2(40),
ERDAT DATE
)
partition by range (ERDAT)
(
partition B2008 values less than (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace ODSDAT
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition B200901 values less than (TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace ODSDAT
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
)
);
格式化日期的时候'SYYYY-MM-DD HH24:MI:SS'前缀S如遇公元前的显示，会在显示前加（-），年度值必须介于-4713
和 +9999 之间，且不为 0。
'NLS_CALENDAR=GREGORIAN'
NLS_CALENDAR=GREGORIAN：初始化参数NLS_CALENDAR用于指定Oracle所使用的日历体系，其取值为Arabic Hijrah、English Hijrah、Gregorian、Japanese Imperial、Persian、ROC Official、Thai Buddha。
TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'建时间的范围分区的标准格式
range分区同时指定多个列作为分区依赖列
create table test_partition_range_2
( emp_id number,
emp_name varchar2(30),
emp_sex varchar2(4),
emp_age number
)
partition by range(emp_id,emp_name)
(
partition t_1 values less than (10,'liu'),
partition t_3 values less than (20,'wang'),
partition t_2 values less than (30,'zhang')
)
多列分区中当第一列能够区分出大小时，就按照第一列来确定分区，当第一列不能确定分区时用第二列来确定分区。
list分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列作为分区依赖列，但它的单个分区对应值可以是多个。在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区
create table test_partition_list
(index_id varchar2(20),
index_name varchar2(40)
)
partition by list(index_id)(
partition test_01 values ('01'),
partition test_02 values ('02'),
partition test_03 values ('03'),
partition test_default values (default)
);
select 'test_01', q.*
from test_partition_list partition(test_01) q
union all
select 'test_02', q1.*
from test_partition_list partition(test_02) q1
union all
select 'test_03', q2.*
from test_partition_list partition(test_03) q2
union all
select 'test_default', q3.*
from test_partition_list partition(test_default) q3
这是创建列表分区的基本步骤，而且也存在创建了default分区后无法再添加分区的情况。
(3) 散列分区（hash）
对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放在哪个分区中，hash分区也可以支持多个依赖列
hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。
CREATE TABLE emp_11
(
empno NUMBER(4),
ename VARCHAR2(30),
sal NUMBER
)
PARTITION BY HASH (empno) PARTITIONS 8
CREATE TABLE emp_11_test
(
empno NUMBER(4),
ename VARCHAR2(30),
sal NUMBER
)
PARTITION BY HASH (empno)
(
partition part_01,
partition part_02,
partition part_03,
partition part_04
)
(4) 组合分区（range-list）范围列表分区
这种分区是基于范围分区和列表分区，表首先按照某列进行范围分区，然后再按某列进行列表分区，分区之中的分区也被称为子分区。
如果某表按照某列分区之后，仍然比较大或者有一些其他的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区
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('20000101','YYYYMMDD'))
(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('20010101','YYYYMMDD'))
(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('20020101','YYYYMMDD'))
(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('20030101','YYYYMMDD'))
(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')
)
);
(5) 组合分区（range-hash）范围散列分区
这种分区是基于范围分区和散列分区，表首先按照某列进行范围分区，然后再按照某列进行散列分区。