表分区
前提:表数据量比较大的时候,查询比较慢
优点:1.提高查询效率 2.增强可用性 3.维护方便(只对某些有问题分区数据维护,不用对整张表维护)
缺点:2.分区表需要维护(维护创建的分区)
(1)范围分区:
按入职日期进行范围分区
CREATE TABLE MYEMP
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
HIREDATE DATE,
DEPTNO NUMBER(7)
)
PARTITION BY RANGE (HIREDATE)
(
PARTITION part1 VALUES LESS THAN (TO_DATE('1981-1-1','YYYY/MM/DD')), --①
PARTITION part2 VALUES LESS THAN (TO_DATE('1982-1-1','YYYY/MM/DD')),
PARTITION part3 VALUES LESS THAN (TO_DATE('1983-1-1','YYYY/MM/DD')),
PARTITION part4 VALUES LESS THAN (MAXVALUE) --默认最大
);
-- SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='MYEMP'; 查看分区表情况
INSERT INTO MYEMP(EMPNO,ENAME,HIREDATE,DEPTNO)
SELECT EMPNO,ENAME,HIREDATE,DEPTNO FROM emp;
-- 查看分区数据
SELECT * FROM MYEMP PARTITION(part4);
(2)列表分区:
该分区的特点是某列的值比较少并且不会经常变动,基于这样的特点我们可以采用列表分区。
按DEPTNO进行LIST分区
CREATE TABLE MYEMP2
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
HIREDATE DATE,
DEPTNO NUMBER(7)
)
PARTITION BY LIST (DEPTNO)
(
PARTITION prt_10 VALUES (10) ,
PARTITION prt_20 VALUES (20) ,
PARTITION prt_30 VALUES (30) ,
PARTITION prt_40 VALUES (40)
);
-- SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='MYEMP2'; 查看分区表情况
INSERT INTO MYEMP2(EMPNO,ENAME,HIREDATE,DEPTNO)
SELECT EMPNO,ENAME,HIREDATE,DEPTNO FROM emp WHERE deptno IN (10,20,30,40);
SELECT * FROM MYEMP2 PARTITION (prt_10);
-- 增加50号部门分区
ALTER TABLE MYEMP2 ADD PARTITION prt_50 VALUES(50);
INSERT INTO MYEMP2(EMPNO,ENAME,HIREDATE,DEPTNO)
SELECT EMPNO,ENAME,HIREDATE,DEPTNO FROM emp WHERE deptno =50;
SELECT * FROM MYEMP2 PARTITION (prt_50);
(3)散列分区/HASH 分区:
通过计算hash值,将相同的hash值放到相同的分区
CREATE TABLE MYEMP3
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
HIREDATE DATE,
DEPTNO NUMBER(7)
)
PARTITION BY HASH (ENAME)
(PARTITION part01,
PARTITION part02);
-- SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='MYEMP3'; 查看分区表情况
INSERT INTO MYEMP3(EMPNO,
ENAME,
HIREDATE,
DEPTNO)
VALUES(1,'张三',DATE'1980-1-1',10);
INSERT INTO MYEMP3(EMPNO,
ENAME,
HIREDATE,
DEPTNO)
VALUES(2,'李四',DATE'1981-10-02',20);
INSERT INTO MYEMP3(EMPNO,
ENAME,
HIREDATE,
DEPTNO)
VALUES(3,'张五',DATE'1982-11-03',30);
SELECT * FROM MYEMP3 PARTITION(part01);
SELECT * FROM MYEMP3 PARTITION(part02);
(4)组合分区:
这种分区是基于两种分区的组合,分区之中的分区被称为子分区。
按入职日期进行范围分区,再按DEPTNO进行LIST子分区
CREATE TABLE MYEMP4
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
HIREDATE DATE,
DEPTNO NUMBER(7)
)
PARTITION BY RANGE(HIREDATE) SUBPARTITION BY LIST(DEPTNO)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('1981-01-01','YYYY-MM-DD'))
(
SUBPARTITION P1A VALUES (10),
SUBPARTITION P1B VALUES (20),
SUBPARTITION P1C VALUES (30),
SUBPARTITION P1D VALUES (50)
),
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 (50)
),
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 (50)
),
PARTITION P4 VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION P4A VALUES (10),
SUBPARTITION P4B VALUES (20),
SUBPARTITION P4C VALUES (30),
SUBPARTITION P4D VALUES (50)
)
);
-- SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='MYEMP4'; 查看分区表情况
INSERT INTO MYEMP4(EMPNO,ENAME,HIREDATE,DEPTNO)
SELECT EMPNO,ENAME,HIREDATE,DEPTNO FROM emp;
-- 所有数据
SELECT * FROM MYEMP4;
-- 主分区
SELECT * FROM MYEMP4 PARTITION (P2);
-- 子分区
SELECT * FROM MYEMP4 SUBPARTITION (P2A);
(5)分区表相关操作
1)查看分区数据(列表分区为例)
SELECT * FROM MYEMP2 PARTITION(MYEMP_DEPTNO_40);
1)添加分区(原有的分区表没有给定默认分区的前提)
ALTER TABLE MYEMP2 ADD PARTITION MYEMP_DEPTNO_50 VALUES (50);
2)删除分区
ALTER TABLE MYEMP2 DROP PARTITION MYEMP_DEPTNO_50;
注意:如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除表。
3)重命名表分区
以下代码将P21更改为P2
ALTER TABLE MYEMP2 RENAME PARTITION MYEMP_DEPTNO_50 TO MYEMP_DEPTNO_60;
总结:
1.非分区表, 不能直接改为分区表(通过重新建立分区表,将旧表数据导入到分区表)
2.创建了分区表, 给了默认分区, 不能添加其它分区
3.建完的分区表, 数据插入只能是当前分区所能包含的数据