oracle表分区创建

oracle表分区创建
⼀、什么是分区表
表分区有以下优点：
1、数据查询：数据被存储到多个⽂件上，减少了I/O负载，查询速度提⾼。

2、数据修剪：保存历史数据⾮常的理想。

3、备份：将⼤表的数据分成多个⽂件，⽅便备份和恢复。

4、并⾏性：可以同时向表中进⾏DML操作，并⾏性性能提⾼，均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

5、增强可⽤性：如果表的某个分区出现故障，表在其他分区的数据仍然可⽤；
6、维护⽅便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
7、改善查询性能：对分区对象的查询可以仅搜索⾃⼰关⼼的分区，提⾼检索速度。

8、需要注意的是包含LONG、LONGRAW数据类型的表不能分区,如果表格⼤于2G需要考虑分区。

⼆、分区表的种类
1、RANGE 范围分区
说明：针对记录字段的值在某个范围。

规则：
（1）、每⼀个分区都必须有⼀个VALUES LESS THEN⼦句，它指定了⼀个不包括在该分区中的上限值。

分区键的任何值等于或者⼤于这个上限值的记录都会被加⼊到下⼀个⾼⼀些的分区中。

（2）、所有分区，除了第⼀个，都会有⼀个隐式的下限值，这个值就是此分区的前⼀个分区的上限值。

（3）、在最⾼的分区中，MAXVALUE被定义。

MAXVALUE代表了⼀个不确定的值。

这个值⾼于其它分区中的任何分区键的值，
也可以理解为⾼于任何分区中指定的VALUE LESS THEN的值，同时包括空值。

若不添加maxvalue的分区插⼊数值⼀旦超过设置的最⼤上限会报错。

例⼀，按date范围创建分区表
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 (INSERT_DATE) --按时间分区
(
PARTITION DATE_RANGE1 VALUES LESS THAN (TO_DATE(' 2001-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data1,
PARTITION DATE_RANGE2 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data2,
PARTITION DATE_RANGE3 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3
)
例⼆、按照number范围分区
PARTITION BY RANGE (CUSTOMER_ID) --按id分区
(
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的分区插⼊数值不属于所设置的分区会报错。

例⼀、姓⽒
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)
(
PARTITION MALE VALUES ('男') TABLESPACE part_Data1,
PARTITION FEMALE VALUES ('⼥') TABLESPACE part_Data2
)
例⼆、varchar2的⽇期
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
)
3、HASH 散列分区
说明：这类分区是在列值上使⽤散列算法，以确定将⾏放⼊哪个分区中。

规则：当列的值没有合适的条件，没有范围的规律，也没有固定的值，建议使⽤散列分区。

散列分区为通过指定分区编号来均匀分布数据的⼀种分区类型，因为通过在I/O设备上进⾏散列分区，
使得这些分区⼤⼩⼀致。

建议分区的数量采⽤2的n次⽅，这样可以使得各个分区间数据分布更加均匀。

创建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
3、复合分区
说明：顾名思义，复合分区就由range+list+hash两两组合⽽来，⼀般分为range+list,list+range,range+hash,list+bash，这⾥指列出⼏种常⽤组合。

规则：如果组合中存在hash，皆把hash分区作为⼦分区，原因⼤家可以通过hash分区的性质知道。

例⼀、range+list，这种分区是基于范围分区和列表分区，表⾸先按某列进⾏范围分区，然后再按某列进⾏列表分区。

CREATE TABLE PART_TAB_SALE_RANGE_LIST
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(10),
)
PARTITION BY RANGE(SALES_DATE)
SUBPARTITION BY LIST (STATUS)
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE part_Data1
(
SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1,
SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
SUBPARTITION P1SUB3 VALUES (DEFAULT) TABLESPACE part_Data3
),
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE part_Data2
(
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1,
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
SUBPARTITION P2SUB3 VALUES (DEFAULT) TABLESPACE part_Data3
),
PARTITION P3 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3
(
SUBPARTITION P3SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1,
SUBPARTITION P3SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
SUBPARTITION P3SUB3 VALUES (DEFAULT) TABLESPACE part_Data3
)
)
例⼆、range+bash，这种分区是基于范围分区和散列分区，表⾸先按某列进⾏范围分区，然后再按某列进⾏散列分区。

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)
);
三、分区表的操作
1.DML操作
说明：DML操作和平常的表⼀样，有异于的地⽅是增加了可以指定表的特定分区才执⾏DML操作。

例如，查询分区表
SELECT*FROM PART_TAB_SALE_RANGE_LIST;--不指定分区直接查询
SELECT*FROM PART_TAB_SALE_RANGE_LIST PARTITION(P2);--指定分区查询
SELECT*FROM PART_TAB_SALE_RANGE_LIST SUBPARTITION(P1SUB2);--指定⼩分区查询
对于分区表，指定分区执⾏DML效率更⾼，但，如果指定了分区，⽽条件中的数据⼜不在该分区中时，将不会产⽣任何DML操作。

2.DDL操作
1）添加分区
（1）对range分区表添加分区
ALTER TABLE PART_TAB_SALE_RANGE_LIST ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2009-06-01','YYYY-MM-
DD'));
注意：增加⼀个分区的时候，增加的分区的条件必须⼤于现有分区的最⼤值，否则系统将提⽰ORA-14074 partition bound must collate higher than that of the last partition 错误。

（2）对range分区表list⼦分区添加分区
ALTER TABLE PART_TAB_SALE_RANGE_LIST MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE'); 2）删除分区
（1）对range分区表删除分区
ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP PARTITION P3;
（2）对range分区表list⼦分区删除⼦分区
ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP SUBPARTITION P4SUB1;
注意：如果删除的分区是表中唯⼀的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

3）截断分区
说明：截断某个分区是指清空某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。

当表中即使只有⼀个分区时，也可以截断该分区。

注意：如果截断的分区表有约束，需要先关闭约束。

alter table sales disable/enable constraint restraint_name，截断分区会使全局索引⽆效，需要重建。

(1)清空分区：
ALTER TABLE SALES TRUNCATE PARTITION P2;--这种⽅式使全局分区索引⽆效
ALTER TABLE SALES TRUNCATE PARTITION P2 update indexes;--这种⽅式使全局分区索引有效UPDATE GLOBAL INDEXES (2)清空⼦分区：
ALTER TABLE PART_TAB_SALE_RANGE_LIST TRUNCATE SUBPARTITION P2SUB2;
ALTER TABLE PART_TAB_SALE_RANGE_LIST TRUNCATE SUBPARTITION P2SUB2 update indexes;
4）合并分区
说明：合并分区是将相邻的分区合并成⼀个分区，结果分区将采⽤较⾼分区的界限，值得注意的是，不能将分区合并到界限较低的分区。

ALTER TABLE PART_TAB_SALE_RANGE_LIST MERGE PARTITIONS P1,P2 INTO PARTITION P2;
注意：在本例中将原有的表的part_01分区和part_02分区进⾏了合并，合并后的分区为part_02,
如果在合并的时候把合并后的分区定为part_01的时候，系统将提⽰ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。

5）拆分分区
说明：拆分分区将⼀个分区拆分两个新分区，拆分后原来分区不再存在。

注意不能对HASH类型的分区进⾏拆分。

ALTER TABLE PART_TAB_SALE_RANGE_LIST SBLIT PARTITION P2 AT(TO_DATE('2003-03-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
6）接合分区
说明：接合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据⽐较⼤时，可以增加散列分区，然后进⾏接合，
值得注意的是，接合分区只能⽤于散列分区中。

ALTER TABLE PART_TAB_SALE_RANGE_LIST COALESCA PARTITION;
7）重命名表分区
ALTER TABLE SALES RENAME PARTITION P21 TO P2;
8）移动分区
说明：把分区移动到令⼀个表空间，移动后要重建索引
alter table sales move partiton sp1 tablespace tablespace_name;
alter index index_name rebuild;
--查询是否移动成功 SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT';
四、表分区相关的数据字典表
--显⽰表分区信息显⽰数据库所有分区表的详细分区信息： select * from DBA_TAB_PARTITIONS
--显⽰⼦分区信息显⽰数据库所有组合分区表的⼦分区信息： select * from DBA_TAB_SUBPARTITIONS
--显⽰数据库所有分区表的信息： select * from DBA_PART_TABLES
--显⽰数据库可访问的所有分区表的分区列信息：select * from DBA_PART_KEY_COLUMNS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
--查询索引信息
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
五、局部索引与全局索引
表可以按range，hash，list分区，表分区后，其上的索引和普通表上的索引有所不同，oracle对于分区表上的索引分为2类，即局部索引和全局索引。

关于由Oracle分区表引出的局部索引与全局索引，我们将在“Oracle 索引”中做详细讲述。