表分区

1. 分区的概念：

分区是指将数据量巨大的表或索引分隔为多个较小的、可以独自管理的部分，分隔出来的部分称为原表的分区

对于大型的表或索引而言，采用分区技术在执行 DDL 操作时可以简化管理操作，同时还可以大大提高查询等操作的性能

表或者索引是否采用分区管理，对于DML操作（ INSERT 、 UPDATE 、 DELETE 和 SELECT 等）是完全透明的，管理数据记录的 SQL 语句也不需要进行调整

什么时候使用分区： 
　　1、 大数据量的表，比如大于2GB。一方面2GB文件对于32位os是一个上限，另外备份时间长。 
　　2、 包括历史数据的表，比如最新的数据放入到最新的分区中。典型的例子：历史表，只有当前月份的数据可以被修改，而其他月份只能read-only 
　　ORACLE只支持以下分区：tables, indexes on tables, materialized views, and indexes on materialized views 
　　分区对SQL和DML是透明的(应用程序不必知道已经作了分区)，但是DDL可以对不同的分区进行管理。 
　　不同的分区之间必须有相同的逻辑属性，比如共同的表名，列名，数据类型，约束; 
　　但是可以有不同的物理属性，比如pctfree, pctused, and tablespaces. 
　　分区独立性：即使某些分区不可用，其他分区仍然可用。 
　　最多可以分成64000个分区，但是具有LONG or LONG RAW列的表不可以，但是有CLOB or BLOB列的表可以。

注：DML----Data Manipulation Language 数据维护操纵语言
	如insert,delete,update,select(插入、删除、修改、检索)

	DDL----Data Definition Language 数据库定义语言 
	如 create procedure之类

	DCL----Data Control Language 数据库控制语言
	如grant,deny,revoke等，只有管理员才有这样的权限。

2. 分区的优点

1. 高可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍然可以使用；如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，故能比整个大表修复花的时间更少；

2. 维护方便：对于大型的历史数据表，将其分区，分别管理和方便地添加和删除 ；

3. 均衡 I /O: 可以把表的不同分区分配到不同的磁盘来平衡 I/ O 改善性能；对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快 ；

4. 分区对用户透明，最终用户感觉不到分区的存在 。 

5、备份：将大表的数据分成多个文件，方便备份和恢复。  

3.分区的方式

分区的方式包括：范围分区、列表分区、散列分区和复合分区 ；

4.分区表的语法格式
建立分区表的语法格式与建立非分区表类似，只是在语句末尾增加PARTITION BY子句，如下例：
4.1 范围分区：
范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。
举个例子：你可能会将销售数据按照月份进行分区。
当使用范围分区时，请考虑以下几个规则：
1、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。
分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
3、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，
也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
例一：
假设有一个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
)
例二：按时间划分
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-1-2003','dd-mm-yyyy')) tablespace ord_ts01,
partition ord_act_part02 values less than (to_date('01-2-2003','dd-mm-yyyy')) tablespace ord_ts02,
partition ord_act_part02 values less than (to_date('01-3-2003','dd-mm-yyyy')) tablespace ord_ts03
)
例三：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
);

4.2 列表分区：
该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
例一
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
)
例二:
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
);

4.3 散列分区：
这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。
例一：
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次方，这样可以使得各个分区间数据分布更加均匀。

4.4 组合范围列表分区:(范围+列表分区)
这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。
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
)
)

4.5 复合范围散列分区：(范围+散列分区)
这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。
create table dinya_test
(
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 (dinya_space01,dinya_space02,dinya_space03)
(
partition part_01 values less than(to_date(‘2006-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)
); 

5.如何判定什么情况下使用哪种分区方式

对于表而言(常规意义上的堆组织表)，上述分区形式都可以应用(甚至可以对某个分区指定compress属性)，只不过分区依赖列不能是lob,long之类数据类型，每个表的分区或子分区数的总数不能超过1023个。

　　对于索引组织表，只能够支持普通分区方式，不支持组合分区，常规表的限制对于索引组织表同样有效，除此之外呢，还有一些其实的限制，比如要求索引组织表的分区依赖列必须是主键才可以等。

　　注：本篇所有示例仅针对常规表，即堆组织表!

　　对于索引，需要区分创建的是全局索引，或本地索引：

　　l 全局索引(global index)：即可以分区，也可以不分区。即可以建range分区，也可以建hash分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。

　　l 本地索引(local index)：其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就是说你add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区。

　　Oracle建议如果单个表超过2G就最好对其进行分区，对于大表创建分区的好处是显而易见的，这里不多论述why，而将重点放在when以及how。

　　一、When使用Range分区

　　Range分区呢是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中，比如按照时间划分，2008年1季度的数据放到a分区，08年2季度的数据放到b分区，因此在创建的时候呢，需要你指定基于的列，以及分区的范围值，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中，并且支持指定多列做为依赖列，后面在讲how的时候会详细谈到。

　　二、When使用Hash分区

　　通常呢，对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。

　　三、When使用List分区

　　List分区与range分区和hash分区都有类似之处，该分区与range分区类似的是也需要你指定列的值，但这又不同与range分区的范围式列值---其分区值必须明确指定，也不同与hash分区---通过明确指定分区值，你能控制记录存储在哪个分区。它的分区列只能有一个，而不能像range或者hash分区那样同时指定多个列做为分区依赖列，不过呢，它的单个分区对应值可以是多个。

　　你在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

　　四、When使用组合分区

　　如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。

　　组合分区呢在10g中有两种：range-hash，range-list。注意顺序哟，根分区只能是range分区，子分区可以是hash分区或list分区。

　　提示：11g在组合分区功能这块有所增强，又推出了range-range,list-range,list-list,list-hash，这就相当于除hash外三种分区方式的笛卡尔形式都有了。为什么会没有hash做为根分区的组合分区形式呢，再仔细回味一下第二点，你一定能够想明白~~。

6. 表分区的常用操作：

一、添加分区 
以下代码给SALES表添加了一个P3分区 
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD')); 
注意：以上添加的分区界限应该高于最后一个分区界限。

以下代码给SALES表的P3分区添加了一个P3SUB1子分区 
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

ALTER TABLE WID_SETT_TICKET_DAY_1100 ADD PARTITION P20100401 VALUES (20100401) TABLESPACE ODSDATA_04 NOLOGGING;

二、删除分区 
以下代码删除了P3表分区： 
ALTER TABLE SALES DROP PARTITION P3;

ALTER TABLE WID_SETT_TICKET_DAY_1100 DROP PARTITION P20100401;

在以下代码删除了P4SUB1子分区： 
ALTER TABLE SALES DROP SUBPARTITION P4SUB1; 
注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

三、截断分区 
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区： 
ALTER TABLE SALES TRUNCATE PARTITION P2; 
通过以下代码截断子分区： 
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

四、合并分区 
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并： 
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

五、拆分分区 
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。 
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD'))  INTO (PARTITION P21,PARTITION P22);

六、接合分区(coalesca) 
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区： 
ALTER TABLE SALES COALESCA PARTITION;

七、重命名表分区 
以下代码将P21更改为P2 
ALTER TABLE SALES RENAME PARTITION P21 TO P2;

九、跨分区查询 
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));

十、查询表上有多少分区 
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

select *  from USER_TAB_PARTITIONS  where table_name = 'WID_SETT_TICKET_DAY_1100'  and partition_name = 'P20100401';

十一、查看用哪个字段做分区
select * from user_part_key_columns

十二、查询索引信息 
select object_name,object_type,tablespace_name,sum(value)  from v$segment_statistics 
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX' 
group by object_name,object_type,tablespace_name  order by 4 desc

--显示数据库所有分区表的信息： 
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'

--删除一个表的数据是 
truncate table table_name;

--删除分区表一个分区的数据是 
alter table table_name truncate partition p5;

注：分区根据具体情况选择。