摆下分区表是囊个用的

前言：分区是管理巨形表和巨形索引的有效手段，可以提高他们的管理性，可用性，并行性 。
表分区种类：范围分区，列表分区，散列分区，复合分区，间隔分区，引用分区，系统分区，虚拟列分区等。
分区索引：本地分区索引和全局分区索引

在那种情况下需要对表分区呢？
1.表的大小超过2GB
2.对表进行并行DML操作时，必须对表分区
3.有时候为了平衡I/0操作，将表分散存储到不同的表空间中去
4.如果需要将表的一部分设置为只读状态，另一部分设置为读写状态必须对表分区
5.如果需要对表的一部分设置为联机状态，另一部分设置为脱机状态必须对表分区
6.如果表中存在long或long raw类型则不能分区，(一个表只能包含long类型一次)
7.索引表仅支持范围分区和散列分区，不能采用复合分区和列表分区
8.分区列必须是主键列的一个子集
9.如果索引表使用了溢出存储，则溢出数据段将采用索引表相同的分区方法

在那种情况下需要对索引分区呢？
1.为了避免移动数据时重建整个索引，可以对索引进行分区，只需重建与移动数据相关的索引分区
2.为了避免对表的分区进行维护时将整个索引设置为失效状态，可以对索引进行分区，只将与表分区对应的索引分区
设置为失效状态即可，不影响其他部分索引的使用
3.为了避免由于索引值的单调急剧增长导致索引条目的剧增进而影响索引的使用效率，可以将索引进行分区，这样新增的索引
条目可以分散在多个索引分区中

范围分区：根据分区列的值的范围分区，通常根据日期类型分区 。
CREATE TABLE sales 
(invoice_no NUMBER, 
... 
sale_date DATE NOT NULL )

PARTITION BY RANGE (sale_date) 
(PARTITION sales1999_q1 
VALUES LESS THAN (TO_DATE(‘1999-04-01’,’YYYY-MM-DD’) 
TABLESPACE ts_sale1999q1, 
PARTITION sales1999_q2 
VALUES LESS THAN (TO_DATE(‘1999-07-01’,’YYYY-MM-DD’) 
TABLESPACE ts_sale1999q2, 
PARTITION sales1999_q3 
VALUES LESS THAN (TO_DATE(‘1999-10-01’,’YYYY-MM-DD’) 
TABLESPACE ts_sale1999q3, 
PARTITION sales1999_q4 
VALUES LESS THAN (TO_DATE(‘2000-01-01’,’YYYY-MM-DD’) 
TABLESPACE ts_sale1999q4 )
--values less than (maxvalue)
enable row movement;
--重建销售分区表

列表分区：不能划分范围(非数值类型和日期类型)，同时分区列的取值是一个包含多少数值的集合，使用列表分区可以将无序的

，无关联的数据自然分组。
create table sales_by_list(
deptno number,
detpname varchar(20)
)

partition by list(deptname)
(

partition  list1 values('北京','上海') tablespace tbs1,

partition  list1 values('重庆','四川') tablespace tbs2,

partition  list1 values('广州') tablespace tbs1

)
--列表分区

散列分区：又称hash分区，采用hash算法，将数据均匀的分布到指定的分区中去。
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分区

范围哈希组合分区：
create table emp ( 
empno number(4), 
ename varchar2(30), 
hiredate date) 
partition by range (hiredate) 
subpartition by hash (empno) 
subpartitions 2 
(partition e1 values less than (to_date('20020501','YYYYMMDD')), 
partition e2 values less than (to_date('20021001','YYYYMMDD')), 
partition e3 values less than (maxvalue));

范围列表组合分区：
CREATE TABLE customers_part (
customer_id NUMBER(6),
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR2(20),
nls_territory VARCHAR2(30),
credit_limit NUMBER(9,2)) 
PARTITION BY RANGE (credit_limit)
SUBPARTITION BY LIST (nls_territory)
SUBPARTITION TEMPLATE 
(SUBPARTITION east VALUES ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
SUBPARTITION west VALUES ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
SUBPARTITION other VALUES (DEFAULT))
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
create table t1 (id1 number,id2 number) 
partition by range (id1) subpartition by list (id2)
(partition p11 values less than (11)
(subpartition subp1 values (1))
);

复合分区：结合2个基本分区方法，先采用一个分区方法对表或索引进行分区，然后再采用另一个分区方法将分区再分成若干个子

分区。每个分区的子分区都是数据的一个逻辑子集，支持所有操作，包括添加分区，合并分区，同时提供了更高层次的分区剪裁

和更细致的数据分布。
oracle 11g支持符合分区的包括以下几种：
1.范围-范围复合分区
2.范围-散列复合分区
3.范围-列表复合分区
4.列表-范围复合分区
5.列表-散列复合分区
6.列表-列表复合分区

本地分区索引：本地分区索引是指为分区表的各个分区单独创建索引分区，各个索引分区之间是相互独立的，索引的分区与表的

分区是一一对应的。当创建了本地分区索引之后，oracle会自动对表的分区和索引的分区进行同步维护，如果分区表添加了新的

分区，oralce会自动为新分区建立新的索引分区。相反，只要表的分区依然存在，用户就不能删除它所对应的索引分区，只有删

除表分区是时候才会自动删除表分区对应的索引分区。特别适合数据仓库以及DSS系统的应用，由于本地分区索引易于管理，在允

许的情况下应尽量为分区表创建本地分区索引
create tabel test
(
 a  number,
 b  varchar(10)
)
organization  index
including  a
overflow tablespace test
partition by range(a)
(
partition values less than(1000) tablespace tbs1,

partition values less than(2000) tablespace tbs2
 
);
--创建本地范围分区索引表
--分区列必须是索引表主键列的子集
--必须使用organization  index和including
--overflow子句是设置溢出数据段的属性

全局分区索引：是指先对整个表建立索引，然后再对索引进行分区，索引的分区之间不是相互独立的，索引分区与表分区也不是

一一对应的。区索引，适合OLPT系统的应用。
CREATE INDEX month_ix ON sales(sales_month)
GLOBAL PARTITION BY RANGE(sales_month)
(PARTITION pm1_ix VALUES LESS THAN (2)
PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));
--创建全局分区索引

分区表的维护：增加分区： 
ALTER TABLE sales ADD PARTITION sales2000_q1 
VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-DD’) 
TABLESPACE ts_sale2000q1;

如果已有maxvalue分区，不能增加分区，可以采取分裂分区的办法增加分区！

删除分区： 
ALTER TABLE sales DROP PARTION sales1999_q1;

截短分区:
alter table sales truncate partiton sales1999_q2;

合并分区：
alter table sales merge partitons sales1999_q2, sales1999_q3 into sales1999_q23;
alter index ind_t2 rebuild partition p123 parallel 2;

分裂分区： 
ALTER TABLE sales 
SPLIT PARTITON sales1999_q4 
AT TO_DATE (‘1999-11-01’,’YYYY-MM-DD’) 
INTO (partition sales1999_q4_p1, partition sales1999_q4_p2) ;
alter table t2 split partition p123 values (1,2) into (partition p12,partition p3);

交换分区:
alter table x exchange partition p0 with table bsvcbusrundatald ;

访问指定分区：
select * from sales partition(sales1999_q2)

EXPORT指定分区： 
exp sales/sales_password tables=sales:sales1999_q1 
file=sales1999_q1.dmp

IMPORT指定分区： 
imp sales/sales_password FILE =sales1999_q1.dmp 
TABLES = (sales:sales1999_q1) IGNORE=y

注意事项：
1.分区索引结构表不支持UPDATE GLOBAL INDEXES 子句。
2.若分区表跨不同表空间，做导出、导入时目标数据库必须预建这些表空间。分表区各区所在表空间在做导入时目标数据库一定

要预建这些表空间！这些表空间不一定是用户的默认表空间，只要存在即可。如果有一个不存在，就会报错！
3.在操作基础表的同时更新全局索引这就不需要后来单独地重建全局索引
4.因为没有被标记成UNUSABLE， 所以全局索引的可用性更高了，甚至正在执行分区的DDL 语句时仍然可用索引来访问表中的其他

分区,避免了查询所有失效的全局索引的名字以便重建它们；
5.因为要更新事先被标记成UNUSABLE 的索引，所以分区的DDL 语句要执行更长时间，当然这要与先不更新索引而执行DDL 然后再

重建索引所花的时间做个比较，一个适用的规则是如果分区的大小小于表的大小的5% ，则更新索引更快一点
6.要登记对索引的更新并产生重做记录和撤消记录，重建整个索引时可选择NOLOGGING
7.普通表变为分区表将已存在数据的普通表转变为分区表，没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方

式进行转变，一般可以有三种方法，视不同场景使用
普通表转为分区表的三种形式：

第一：利用原表重建分区
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;
CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME) 
(PARTITION P1 VALUES LESS THAN (TO_DATE('2000-1-1', 'YYYY-MM-DD')), 
PARTITION P2 VALUES LESS THAN (TO_DATE('2002-1-1', 'YYYY-MM-DD')), 
PARTITION P3 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), 
PARTITION P4 VALUES LESS THAN (MAXVALUE))
AS SELECT ID, TIME FROM T; 
RENAME T TO T_OLD; 
RENAME T_NEW TO T; 
SELECT COUNT(*) FROM T; 
COUNT(*)
----------
5000 
SELECT COUNT(*) FROM T PARTITION (P1); 
COUNT(*)
----------
2946 
SELECT COUNT(*) FROM T PARTITION (P2); 
COUNT(*)
----------
731 
SELECT COUNT(*) FROM T PARTITION (P3); 
COUNT(*)
----------
1096 
优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到

各个分区中了。 
不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语

句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比

较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。 
适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。

第二：使用交换分区的方法
Drop table t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;
CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 
(PARTITION P1 VALUES LESS THAN (TO_DATE('2005-9-1', 'YYYY-MM-DD')), 
PARTITION P2 VALUES LESS THAN (MAXVALUE)); 
ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T; 
RENAME T TO T_OLD; 
RENAME T_NEW TO T; 
优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一

步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T

中，可以保证对T插入的操作不会丢失。 
不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数

据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。 
适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。

第三：利用在线重定义功能 
Drop table t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T'); 
PL/SQL 过程已成功完成。 
CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 
(PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')), 
PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), 
PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')), 
PARTITION P4 VALUES LESS THAN (MAXVALUE)); 
表已创建。 
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW'); 
PL/SQL 过程已成功完成。 
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T', 'T_NEW'); 
PL/SQL 过程已成功完成。 
SELECT COUNT(*) FROM T; 
COUNT(*)
----------
5000 
SELECT COUNT(*) FROM T PARTITION (P3); 
COUNT(*)
----------
1096 
优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法

具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不

再需要任何额外的管理操作。 
不足：实现上比上面两种略显复杂。

把一个已存在数据的大表改成分区表：
第一种（表不是太大）：
1.把原表改名：
rename xsb1 to xsb2;
2.创建分区表：
CREATE TABLE xsb1
PARTITION BY LIST (c_test) 
(PARTITION xsb1_p1 VALUES (1),
PARTITION xsb1_p2 VALUES (2),
PARTITION xsb1_p0 VALUES (default))
nologging AS SELECT * FROM xsb2;
3.将原表上的触发器、主键、索引等应用到分区表上；
4.删除原表：
drop table xsb2;
第二种(表很大)：
1. 创建分区表：
CREATE TABLE x PARTITION BY LIST (c_test) [range ()]
(PARTITION p0 VALUES [less than ](1) tablespace tbs1,
PARTITION p2 VALUES (2) tablespace tbs1,
PARTITION xsb1_p0 VALUES ([maxvalue]default))
AS SELECT * FROM xsb2 [where 1=2];
2. 交换分区 alter table x exchange partition p0 with table bsvcbusrundatald ;
3. 原表改名alter table bsvcbusrundatald rename to x0;
4. 新表改名alter table x rename to bsvcbusrundatald ;
5. 删除原表drop table x0;
6. 创建新表触发器和索引create index ind_busrundata_lp on bsvcbusrundatald(。。。) local tablespace tbs_brd_ind ;
或者：
1. 规划原大表中数据分区的界限，原则上将原表中近期少量数据复制至另一表；
2. 暂停原大表中的相关触发器；
3. 删除原大表中近期数据；
4. 改名原大表名称；
5. 创建分区表；
6. 交换分区；
7. 重建相关索引及触发器（先删除之再重建）.