ORACLE分区表学习笔记
学习视频地址： https://www.bilibili.com/video/BV1Du411278k?spm_id_from=333.337.search-card.all.click&vd_source=3f8214d2a71887957af53b432bdeddb8
文章目录
前言
1、允许用户将一个表分成多个分区
2、用户可以执行查询，只访问表中的特定分区
3、将不同的分区存储在不同的磁盘，提高访问性能和安全性
4、可以独立地备份和恢复每个分区
分区：范围分区、散列分区、列表分区、复合分区
一、范围分区
表中的一个列或一组列的值的范围分区
PARTITION BY RENGE(COLUNM_NAME)
(
PARTITION PART1 VALUE LESS THAN(RANGE1),
PARTITION PART2 VALUE LESS THAN(RANGE2),
...
PARTITION PARTN VALUE LESS THAN(MAXVALUE)
)
创建范围分区，按照销售量进行分区，销售量小于1000的分一个区，销售量小于两千的分一个区，销售量小于三千的分一个区
CREATE TABLE SALES(
PRODUCT_ID VARCHAR2(5)
SALES_COUNT NUMBER(10,2)
)
PARTITION BY RANGE(SALES_COUNT)--分区字段
(
PARTITION P1 VALUE LESS THAN(1000),
PARTITION P2 VALUE LESS THAN(2000),
PARTITION P3 VALUE LESS THAN(3000)
)
查询分区
SELECT * FROM USER_TAB_PARTITION U
WHERE U.TABLE_NAME='SALES';
这样子创建的分区SALES_COUNT字段值最大只能为3000,SALES_COUNT字段值超过3000数据库会报错
第1行出现错误：
ORA-14400：插入的分区关键字未映射到任何分区
查询特定分区的数据
SELECT * FROM SALES PARTITION(P1);
增加第四分区P4，无穷大
ALTER TABLE SALES ADD PARTITION P4
VALUES LESS THAN(MAXVALUES);
二、散列分区
允许用户对不具有逻辑范围的数据进行分区
通过在分区键上执行HASH函数决定存储分区
将数据平均低分不到不同的 分区
PARTITION BY HASH (COLUMN_NAME)
PARTITION BY NUMBER_OF PARTITIONS;
或
PARTITION BY HASH (COLUMN_NAME)
(
PARTITION PART1 [TABLESPACETB1],
PARTITION PART2 [TABLESPACETB2],
...
PARTITION PARTN [TABLESPACETBSN]
);
如下按照empno列进行散列分区，分成p1、p2两个分区，数据散列方式是按照hash算法进行散列，基本能够比较平均地散列到各个区
CREATE TABLE MY_EMP(
EMPNO NUMBER,ENAME VARCHAR2(10)
)
PARTITION BY HASH(EMPNO)
(
PARTITION P1,PARTITION P2
);
查询分区
SELECT * FROM USER_TAB_PARTITIONS U WHERE U.TABLE_NAME ='MY_EMP';
三、列表分区
允许用户将不相关的数据组织在一起
PARTITION BY LIST (COLUNM_NAME)
(
PARTITION PART1 VALUES(VALUES_LIST1),
PARTITION PART2 VALUES(VALUES_LIST2),
...
PARTITION PARTN VALUES(DAFAULT)
);
建表样例
CREATE TABLE PERSIONCITY(
ID NUMBER,NAME VARCHAR2(10),CITY VARCHAR2(10)
)
PARTITION BY LIST(CITY)
(
PARTITION 东边 VALUES('开封','商丘')，
PARTITION 西边 VALUES('洛阳')，
PARTITION 南边 VALUES('许昌')，
PARTITION 北边 VALUES('新乡')
);
列表分区根据字段值进行分区，样例代码中按照CITY字段值进行列表分区，若CITY值为开封、商丘则将数据插入‘东边’分区中，若CITY字段值为洛阳则将数据插入‘西边’分区中，若CITY字段值为许昌则将该条数据插入‘南边’分区中，若CITY字段之为新乡则将数据插入‘北边’分区中，若还存在其他CITY值还可以再写一行PARTITION 其他 VALUES（DEFAULT）
四、复合分区
范围分区和散列分区或列表分区的组合
复合分区只有两种情况：1、范围分区和散列分区相结合；2、列表分区和散列分区相结合
范围分区和列表分区都必须在外层，散列分区在内层
PARTITION BY RANGE(COLUMN_NAME1)
SUBPRITITION BY HASH(COLUMN_NAME2)
SUBPRITITIONS NUMBER_OF_PARTITIONS
(
PARTITION PART1 VALUES LESS THAN(RANGE1),
PARTITION PART2 VALUES LESS THAN(RANGE2),
...
PARTITION PARTN VALUES LESS THAN(MAXVALUES)
);
实例
CREATE TABLE STUDENT(
SNO NUMBER,SNAME VARCHAR2(10)
)
PARTITION BY RANGE(SNO)
SUBPARTITION BY HASH(SNAME)
SUBPARTITIONS 4
(
PARTITION P1 VALUES LESS THAN(1000),
PARTITION P2 VALUES LESS THAN(2000),
PARTITION P3 VALUES LESS THAN(MAXVALUE)
);
对于每个范围分区都有四个散列子分区
查看具体分区情况
启动OracleDBConsoleorcl服务
登录https://localhost:1158/em
使用Oracle的sys账号登录
找到建立的表
具体的建立的分区情况如下图
P1、P2、P3三个范围分区中都各自有四个散列分区，建立分区时自动分配命名，注意不能手动更改分区名，更改会导致报错
Oracle11g新增的分区类型
五、引用分区
基于由外键引用的父表的分区分方法，它依赖已有的父表子表的关系，子表通过外键关联到父表，进而继承了父表的分区方式而不需自己创建，字表还继承了父表的维护操作。
1、主表是范围分区，子表是引用分区
2、主表是列表分区，子表是引用分区
3、主表是散列分区，子表是引用分区
实例
主表建立
CREATE TABLE STUDENT(
STU_ID NUMBER PRIMARY KEY,STU_NAME VARCHAR2(10),GRADE VARCHAR2(10)
)
PARTITION BY RANGE(STU_ID)
(
PARTITION PAR_STU1 VALUES LESS THAN(1000),
PARTITION PAR_STU2 VALUES LESS THAN(2000),
PARTITION PAR_STU3 VALUES LESS THAN(MAXVALUE)
);
子表建立，设置外键FK_SCORE，将SCORE表中的STU_ID与STUDENT表中的STU_ID做外键关联
CREATE TABLE SCORE(
ID NUMBER PRIMARY KEY,STU_ID NUMBER NUT NULL,COUSE_NAME VARCHAR2(20),
SCORE NUMBER,
CONSTRAINT FK_SCORE FOREGIN KEY(STU_ID) REFERENCES STUDENT(STU_ID)
)
PARTITION BY REFERENCE (FK_SCORE);
子表建立完成后，子表自动创建分区，分区情况与父表一致，如示例中父表建的是范围分区，有三个分区，子表也会自动建立范围分区，存在三个分区，分区条件也与父表一致，可以按照复合分区中介绍的方法进行验证
Student表有多少个分区，score表就有多少个分区
六、间隔分区
间隔分区：可以完全自动地根据间隔阈值创建范围分区，他是范围分区的扩展。
在数据仓库中有广泛应用。
CREATE TABLE SALE_DETAIL(
SALE_DETAIL_ID NUMBER,PRODUCT_ID NUMBER,QUANTITY NUMBER,SALE_DATE DATE
)
PARTITION BY RANGE(SALE_DATE)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION P_201006 VALUES LESS THAN (TO_DATE('20100601','yyyymmdd'))
);
该分区会按照sale_date字段，小于20100601的数据都在P_201006分区中，每增加一个月自动创建一个该月份的分区，例如插入的时间为20100621这日期比20100601大，且在20100601加一个月的范围内所以自动创建时间范围在20100601至20100701的分区。插入的时间是20100101，就会插入P_201006分区中。
插入时间为20101121他不会自动创建中间间隔的几个月的分区，会直接创建时间 小于20101201的分区
七、虚拟列分区
把分区建立在某个虚拟列上，即建立在函数或者表达式的计算结果上，来完成某种任务。
CREATE TABLE SALE(
SALE_ID NUMBER PRIMARY KEY,PRODUCT_ID NUMBER,PRICE NUMBER,
QUANTITY NUMBER,SALE_DATE DATE,
TOTAL_PRICE AS (PRICE * QUANTITY) VIRTUAL
)
PARTITION BY RANGE (TOTAL_PRICE)
(
PARTITION P_1000 VALUES LESS THAN (1000),
PARTITION P_2000 VALUES LESS THAN (2000),
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
);
TOTAL_PRICE字段并不是真实表中存在的字段，而是通过虚拟分区创建的字段，示例中是将虚拟分区字段进行范围分区
八、系统分区
不指定分区列，由ORACLE来完成分区的控制和管理吗，谈没有了范围分区或列表分区的界限。
CREATE TABLE PERSON(
ID NUMBER,NAME VARCHAR2(20),ADDRESS VARCHAR2(20)
)
PARTITION BY SYSTEM(
PARTITION P1,PARTITION P2,PARTITION P3
);
九、操作已分区的表
（一）在已分区的表中插入数据与操作普通表完全相同，ORACLE会将数据自动保存到对应的分区
（二）查询、修改和删除分区表时可以显示执行要操作的分区
十、分区维护操作
（一）分区维护操作修改已分区表的分区。
（二）分区维护的类型
1、计划事件——定期删除最旧的分区
2、非计划事件——解决应用程序或系统问题
（三）分区维护操作有：
1、添加分区
在最后一个分区之后添加新分区
ALTER TABLE SALES
ADD PARTITION P4 VALUES LESS THAN (4000);
2、删除分区
删除一个指定的分区，分区的数据也随之删除
ALTER TABLE SALES DROP PARTITION P4;
3、截断分区
删除指定分区中的所有记录
ALTER TABLE SALES TRUNCATE PARTITION P3;
4、合并分区
将范围分区或复合分区的两个相邻区连接起来
ALTER TABLE SALES
MERGE PARTITION S1,S2 INTO PARTITION S2;
5、拆分分区
将一个大分区中的记录拆分到两个分区中
ALTER TABLE SALES SPLIT PARTITION P2 AT (1500)
INTO (PARTITION P21,PARTITION P22);
将分区从1500部分截断，前半部分的数据转移至P21分区，后半部分的数据转移至P22分区
总结
笔记可能做的不详细，需要的同学可以上B站看原视频 https://www.bilibili.com/video/BV1Du411278k?spm_id_from=333.337.search-card.all.click&vd_source=3f8214d2a71887957af53b432bdeddb8视频比较老了看起来是2010年的，但是讲的不错，记录一下。
204
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
