Oracle表分区学习笔记，一篇就够了

–表分区的具体作用

Oracle的表分区功能通过改善可管理性、性能和可用性，
从而为各式应用程序带来了极大的好处。
通常，分区可以使某些查询以及维护操作的性能大大提高。
此外,分区还可以极大简化常见的管理任务，
分区是构建千兆字节数据系统或超高可用性系统的关键工具。

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

–什么时候使用分区表：

1.表的数据量特别大
2.表中包含历史数据，新的数据被增加到新的分区中。

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

缺点： 分区表相关，已经存在的表没有方法可以直接转化为分区表。 需要维护。

;

–分区表类型

• 1.范围分区：RANGE
  2.列表分区：LIST
  3.组合分区
  4.哈希分区

select * from emp

—范围分区的语法结构：
CREATE TABLE 表名(
字段名1 数据类型
,字段名2 数据类型
,字段名3 数据类型
…
) -----不要加分号，分号表示语句结束，语句结束说明建表成功，建好了的表不能再建分区
PARTITION BY RANGE (作为分区字段的字段名称)
(
PARTITION 分区名1 VALUES LESS THAN (分区字段具体的一个上限值),–分区的上限值不能比下一个分区的上限值高
PARTITION 分区名2 VALUES LESS THAN (分区字段具体的一个上限值),—分区名不能重复
PARTITION 分区名3 VALUES LESS THAN (分区字段具体的一个上限值),----
PARTITION 分区名4 VALUES LESS THAN (MAXVALUE) -----最大值，空值会插入到这个分区中
);
—创建分区表
CREATE TABLE EMP_RANGE
(
EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2), – 以SAL字段为例子，自己建一个分区表
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
PARTITION BY RANGE(HIREDATE)
(–入职日期为1982年1月1日之前的数据都会放到这个分区里 RANGE_1981
PARTITION RANGE_1981 VALUES LESS THAN (TO_DATE(19820101,‘YYYYMMDD’))
–入职日期为1982年1月1日到1983年1月1日的数据都会放到这个分区里 RANGE_1982
,PARTITION RANGE_1982 VALUES LESS THAN (TO_DATE(19830101,‘YYYYMMDD’))
,PARTITION RANGE_MAX VALUES LESS THAN (MAXVALUE)
);

----插入数据
INSERT INTO EMP_RANGE
SELECT * FROM EMP;
COMMIT;

—查分区表数据
SELECT * FROM EMP_RANGE; --查整张表的数据
SELECT * FROM EMP_RANGE PARTITION(RANGE_1981);–查指定的分区
SELECT * FROM EMP_RANGE PARTITION(RANGE_1982);
SELECT * FROM EMP_RANGE PARTITION(RANGE_MAX);

—当分区字段作为查询条件时，只要查询的范围条件没有跨越分区
–就会进入到对应的分区中来，当跨越了两个或者多个分区，就会整张表查
SELECT * FROM EMP_RANGE T
WHERE T.HIREDATE BETWEEN TO_DATE(19820502,‘YYYYMMDD’) AND TO_DATE(19830702,‘YYYYMMDD’)

–练习

SELECT * FROM EMP;
创建一个EMP 分区表，按照工资做范围分区
小于 1500 的为第一个分区
1500 到2500 的为第二个分区
大于2500 的为第三个分区

CREATE TABLE EMP_RANGE_SAL
(
EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
PARTITION BY RANGE(SAL)
(
PARTITION RANGE_1500 VALUES LESS THAN (1500)
,PARTITION RANGE_1500_2500 VALUES LESS THAN (2500)
,PARTITION RANGE_MAX VALUES LESS THAN (MAXVALUE)
);

select * from EMP_RANGE_SAL ;

---

—二 列表分区
—LIST
—列表分区
—语法
create table EMP_LIST
(
字段 字段对应的数据类型
)
PARTITION BY LIST(判断数据放到哪个分区的字段)
(–注意 分区名称不可以重复，
PARTITION 分区名称1 VALUES (具体的值)
,PARTITION 分区名称2 VALUES (具体的值)
);

—样例
按照部门编号创建分区表
10 20 30 每个部门都要放到不同的分区里
create table EMP_LIST
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)
PARTITION BY LIST(DEPTNO)
(— PARTITION LIST_10 VALUES(10,20)
—10部门的数据都会放到这个分区 LIST_10 来
PARTITION LIST_10 VALUES(10)
—20部门的数据都会放到这个分区 LIST_20 来
,PARTITION LIST_20 VALUES (20)
—30部门的数据都会放到这个分区 LIST_30 来
,PARTITION LIST_30 VALUES(30)
);

insert INTO EMP_LIST
SELECT * FROM EMP;
COMMIT;

SELECT T.* FROM EMP_LIST PARTITION (LIST_20) T —T 是别名
WHERE T.DEPTNO = 20
;

–练习

创建一个列表分区
按照 工作进行分区
ANALYST CLERK 放在同一个分区
MANAGER PRESIDENT 放在同一个分区
SALESMAN 放在一个分区;

CREATE TABLE EMP_LIST_JOB
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
PARTITION BY LIST(JOB)
(
PARTITION LIST_JOB1 VALUES(‘ANALYST’,‘CLERK’),
PARTITION LIST_JOB2 VALUES (‘MANAGER’,‘PRESIDENT’),
PARTITION LIST_JOB3 VALUES(‘SALESMAN’)
);

INSERT INTO EMP_LIST_JOB
SELECT * FROM EMP;
COMMIT;

SELECT * FROM EMP_LIST_JOB;
SELECT * FROM EMP_LIST_JOB PARTITION(LIST_JOB1);
SELECT * FROM EMP_LIST_JOB PARTITION(LIST_JOB2);
SELECT * FROM EMP_LIST_JOB PARTITION(LIST_JOB3);
–范围分区是一个范围
–列表分区是一些确定的值

—组合分区
—分区之中套分区，就是一个分区里边还套着一个分区
–SUBPARTITION
CREATE TABLE EMP_RANGE_LIST
(
字段名称 字段数据类型
)
PARTITION BY RANGE(主分区字段) --主分区
SUBPARTITION BY LIST (子分区字段)–子分区
(PARTITION 主分区名1 VALUES LESS THAN(主分区的分区上限)
(—对子分区的定义
SUBPARTITION 子分区名1 VALUES (子分区的判断值),
SUBPARTITION 子分区名2 VALUES (子分区的判断值)
),
PARTITION 主分区名2 VALUES LESS THAN (主分区的分区上限)
(
SUBPARTITION 子分区名1 VALUES (子分区的判断值) ,
SUBPARTITION 子分区名2 VALUES (子分区的判断值)
)
);

—样例
CREATE TABLE EMP_RANGE_LIST
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(7,2)
)
PARTITION BY RANGE(HIREDATE) --主分区 LIST RANGE
SUBPARTITION BY LIST (DEPTNO)–子分区 RANGE RANGE
(
PARTITION P2 VALUES LESS THAN (TO_DATE(‘1982-01-01’,‘YYYY-MM-DD’))
(
SUBPARTITION P2A VALUES (10),
SUBPARTITION P2B VALUES (20),
SUBPARTITION P2C VALUES (30),
SUBPARTITION P2D VALUES (40)
),
PARTITION P3 VALUES LESS THAN (TO_DATE(‘1983-01-01’,‘YYYY-MM-DD’))
(
SUBPARTITION P3A VALUES (10) ,
SUBPARTITION P3B VALUES (20),
SUBPARTITION P3C VALUES (30),
SUBPARTITION P3D VALUES (40)
),
PARTITION P4 VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION P4A VALUES (10) ,
SUBPARTITION P4B VALUES (20),
SUBPARTITION P4C VALUES (30),
SUBPARTITION P4D VALUES (40)
)
);

INSERT INTO EMP_RANGE_LIST
SELECT *
FROM EMP;

COMMIT;

查主分区
SELECT * FROM EMP_RANGE_LIST PARTITION(P2);
–查子分区
SELECT * FROM EMP_RANGE_LIST SUBPARTITION(P4B);

---

–分区的维护

有关表分区的一些维护性操作
—1)添加分区
–SC_RANGE

注意：以上添加的分区界限应该高于最后一个分区界限。
ALTER table 表名称 ADD PARTITION 分区名 VALUES LESS THAN(上限值); ?

ALTER table EMP_RANGE ADD PARTITION RANGE_1983 VALUES LESS THAN(TO_DATE(19840101,‘YYYYMMDD’)); --添加的分区界限应该高于最后一个分区界限

ALTER table EMP_RANGE split PARTITION RANGE_MAX at(TO_DATE(19840101,‘YYYYMMDD’)) into (PARTITION RANGE_1983, PARTITION RANGE_MAX); – RANGE_1983

SELECT * FROM EMP_RANGE PARTITION(RANGE_1983); – RANGE_1983

–以下代码给EMP_RANGE_LIST表的 P2 分区添加了一个 P2_E 子分区
ALTER TABLE 表名称 MODIFY PARTITION 分区名 ADD SUBPARTITION 子分区名 VALUES(值);

ALTER TABLE EMP_RANGE_LIST MODIFY PARTITION P2 ADD SUBPARTITION P2_E VALUES(50);

2)删除分区
以下代码删除了RANGE_1985表分区：

ALTER TABLE 表名 DROP PARTITION 分区名称;?
ALTER TABLE EMP_RANGE DROP PARTITION RANGE_MAX;

在以下代码删除了P2_E子分区：
ALTER TABLE EMP_RANGE_LIST DROP SUBPARTITION P2_E;

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

3)截断分区—删除分区表中的数据，表分区还存在,不需要提交事务
截断某个分区是指删除某个分区中的数据，并不会删除分区，
也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。
通过以下代码截断分区：
SELECT * FROM EMP_RANGE PARTITION(RANGE_1981)
ALTER TABLE EMP_RANGE TRUNCATE PARTITION RANGE_1981;

SELECT * FROM EMP_RANGE PARTITION(RANGE_1982);
ALTER TABLE EMP_RANGE TRUNCATE PARTITION (RANGE_1981);

通过以下代码截断子分区：
SELECT * FROM EMP_RANGE_LIST SUBPARTITION(P4B)

SELECT * FROM EMP_RANGE_LIST SUBPARTITION(P4B);
ALTER TABLE EMP_RANGE_LIST TRUNCATE SUBPARTITION P4B;

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

ALTER TABLE EMP_RANGE MERGE PARTITIONS RANGE_1982,RANGE_1983 INTO PARTITION RANGE_1983;

–拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。
注意不能对HASH类型的分区进行拆分。
19840101
19830101 19840101
ALTER TABLE EMP_RANGE SPLIT PARTITION RANGE_1983
AT(TO_DATE(‘19830101’,‘YYYYMMDD’)) INTO (PARTITION RANGE_1982,PARTITION RANGE_1983);

7)重命名表分区
以下代码将P21更改为P2
ALTER TABLE SALES RENAME PARTITION P21 TO P2;?
ALTER TABLE EMP_RANGE RENAME PARTITION RANGE_1983 TO RANGE_19840101;