Oracle分区表

分区表

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间（物理文件上），这样查询数据时，不至于每次都扫描整张表。

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库 管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但 是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

什么时候使用分区表，官方给的建议是：
a. 表的大小超过 2GB。
b. 表中包含历史数据，新的数据被增加到新的分区中。

表分区的分析

优点：

a.改善查询性能： 对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
b.增强可用性： 如果表的某个分区出现故障，表在其他分区的数据仍然可用。
c.维护方便： 如果表的某个分区出现故障，需要修复数据，只修复该分区即可。
d.均衡 I/O： 可以把不同的分区映射到磁盘以平衡 I/O，改善整个系统性能。

缺点：

分区表相关，已经存在的表没有方法可以直接转化为分区表。不过 oracle 提供了在线重定义表的功能。

表分区的类型

范围分区（RANGE）

范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。当使用范围分区时，请考虑以下几个规则：

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-MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01, 
	PARTITION ORD_ACT_PART02 
		VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE 
	ORD_TS02, PARTITION ORD_ACT_PART02 VALUES LESS THAN 
(TO_DATE('01-JUL-2003','DD-MON-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 THAN (1000) TABLESPACE Part1_tb, 
PARTITION part2 VALUES LESS THAN (MAXVALUE) TABLESPACE Part2_tb ); 
--——在表上执行查询
select * from RANGETABLE; 
--——在表分区上执行查询
select * from RANGETABLE partition(part1);

列表分区（LIST）

该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。创建一个按字段数据列表固定可枚举值分区的表。插入记录分区字段的值必须在列表中，否则不能被插入。

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 NUMBER PRIMARY KEY ,name VARCHAR2(20),
area VARCHAR 10) )
PARTITION BY LIST (area) (
PARTITION part1 VALUES ('guangdong','beijing') ABLESPACE Part1_tb,
PARTITION part2 VALUES ('shanghai','nanjing') TABLESPACE Part2_tb );

哈希分区（散列分区）（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 次方，这样可以使得各个分区间数据分布更加均匀。

组合分区（RANGE-LIST 和 RANGE-HASH）

1.基于范围分区和列表分区，
表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。

2.基于范围分区和散列分区
表首先按某列进行范围分区，然后再按某列进行散列分区

表分区的操作

添加分区

以下代码给 SALES 表添加了一个 P3 分区

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(
TO_DATE('2019-09-01','YYYY-MM-DD'));

注:hash partitioned table 新增 partition 时，现有表的中所有 data 都有重
新计算 hash 值，然后重新分配到分区中，所以被重新分配的分区的 indexes 需要
rebuild 。

删除分区

ALTER TABLE SALES DROP PARTITION P3; ALTER TABLE SALES 
DROP SUBPARTITION P4SUB1;

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表.

截断分区

截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：

ALTER TABLE SALES TRUNCATE PARTITION P2;

通过以下代码截断子分区：

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

合并分区

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2 UPDATE INDEXES; 
--如果省略 update indexes 子句的话，必须重建受影响的分区的 index; 
ALTER TABLE range_example MODIFY PARTITION part02 REBUILD 
UNUSABLE LOCAL INDEXES;

拆分分区

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对 HASH 类型的分区进行拆分。

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) 
INTO (PARTITION P21,PARTITION P22);

**注意：**如果是 RANGE 类型的，使用 at，LIST 类型的使用 values。

接合分区（coalesce）

分区接合是针对散列分区或者*-散列子分区的，目的是减少分区数。当某个散列分区接合后，Oracle 将其分区的数据分散到其它分区中。被接合的分区是由数据库选择的，接合完成后该分区会被删除，且如果没有使用 UPDATE INDEX 子句，本地索引和全局索引均将变成不可用，一般需要重建索引。

--散列分区表的散列分区接合
ALTER TABLE table_name COALESCE PARTITION;
--散列分区表的散列子分区接合
ALTER TABLE table_name MODIFY PARTITION partition_name 
COALESCE SUBPARTITION; 
--重命名表分区
ALTER TABLE table_name RENAME PARTITION old_name TO new_name;
ALTER TABLE table_name RENAME SUBPARTITION old_name TO new_name;

更多Oracle相关知识敬请关注我哟，或猛戳主页，谢谢！