Oracle分区

风和雪的爱情

已于 2023-08-15 09:34:50 修改

阅读量77

点赞数

文章标签： oracle 数据库

于 2023-08-04 10:52:32 首次发布

原文链接：http://blog.itpub.net/70024420/viewspace-2933268/

版权

2万字详解Oracle分区表技术，太顶了

参考：http://blog.itpub.net/70024420/viewspace-2933268/

大家好，我是哪吒，最近项目有一个新的需求，按月建表，按天分区。

不都是分库分表吗？怎么又来个分区？

让我们一起，一探究竟，深入理解一下Oracle分区表技术，实现快速入门，丰富个人简历，提高面试level，给自己增加一点谈资，秒变面试小达人，BAT不是梦。

三分钟你将学会：

Oracle是如何存储数据的？
Oracle分区是什么？
何时分区？
分区表的分类都有哪些？
Oracle分区技术实战演练

一、Oracle是如何存储数据的？

1、逻辑存储与物理存储

在国企或者一线大厂，一般都会选择使用Oracle数据库，程序通过mybatis等持久层框架访问Oracle数据库，指定表空间，表空间内包含若干张表，表中存有行数据，行数据以行片段的形式存储在数据库块中，① 当插入的行太大，无法装入单个块时；② 或因为更新的缘故，导致现有行超出了当前空间时 -> 就会发生整个行不存储在一个位置的情况。

Oracle在逻辑上将数据存储在表空间中，在物理上将数据存储在数据文件中。

表空间包括若干个数据文件，这些表空间使用与运行Oracle软件的操作系统一致的物理结构。数据库的数据存储在构成数据库表空间的数据文件中。

临时文件是一个临时表空间的文件；它是通过TEMPFILE选项创建的。临时表空间不包含表，通常用于排序。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kz0ZH4WV-1691117526426)(media/bb)]

2、进一步分析它们之间的关系

数据库包含若干个表空间（逻辑存储单元）；
每一个表空间包含很多的Oracle 逻辑数据块，逻辑数据块的大小一般在2 KB 至32 KB，默认8 KB；
Oracle 数据块是逻辑I/O的最小单位；
特定数目的相邻逻辑块构成了“区”；
特定逻辑结构分配的一组区构成了一个段;

3、Oracle逻辑数据块

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-grtCCb9E-1691117526427)(media/数据块)]

数据库块包含块头、行数据、可用空间。

（1）块头

块头包含段类型（如表或索引）、数据块地址、表目录、行目录和事务处理插槽。

每个插槽的大小为24 字节，修改块中的行时会使用这些插槽。

（2）行数据

块中行的实际数据。

（3）可用空间

可用空间位于块的中部，允许头和行数据空间在必要时进行增长。当插入新行或用更大的值更新现有行的列时，行数据会占用可用空间。

（4）致块头增长的原因有：

行目录需要更多的行条目；
需要的事务处理插槽数多于最初配置的数目；

块中的可用空间最初是相邻的。但是，删除和更新操作可能会使块中的可用空间变成碎片，需要时Oracle 服务器会接合块中的空闲空间。

二、Oracle分区表技术

分区是指表和索引可以被分成若干个部分，它们拥有相同的逻辑属性和数据结构。所有分区的字段和索引都是一样的。

分区表是将表数据分为若干个可以被单独管理的片，每个片就是一个分区，分一个分区都可以拥有自己的物理属性，比如表空间、事务槽、存储参数、最小区段数等，通过建分区语句指定，提升可用性和存储效率。

每个分区可以被单独管理，降低管理成本和备份成本，提高容错率，避免“一荣既荣，一损俱损”的问题。

1、分区表的优缺点

（1）优点

可以通过指定分区提高查询性能；
提高容错率，避免“一荣既荣，一损俱损”的问题；
降低管理成本；
降低备份成本；

（2）缺点

普通表和分区表不能直接转换，可以通过数据迁移，再重命名的方式实现，需要重建约束、索引，在创建表时可以添加关键字“parallel compress”并行执行，提高效率，下面会通过SQL实例介绍。

2、何时分区？

单表的数据量如果过大，会影响SQL的读写性能，我们可以通过分库分表的方式解决表性能的问题，Oracle的分区表是将一张大表在物理上分成几个较小的表，从逻辑上看仍然是一张完整的表。这样，每次DML操作只考虑其中一张分区表即可。

那么，临界点是多少呢？

数量量超过500万且空间占用超过2GB的时候必须分区；
数量量高于100万，低于500万时建议分区；

注意：单个分区的数据可以超过500万，但存储空间不建议超过2GB。

三、分区相关的数据字典

根据数据字典表的前缀不同，可查询的内容及权限有所差异：

DBA_开头：需要DBA权限，查询全库内容；
ALL_开头：查询当前用户权限下的内容；
USER_开头：查询当前用户下的内容；

以下是分区表的一些相关字典表，前缀是“DBA_”、“ALL_”、“USER_”；

分区表信息字典表：*_PART_TABLES；
分区信息字典表：*_TAB_PARTITIONS；
子分区信息字典表：*_TAB_SUBPARTITIONS；
分区表的分区字段信息字典表：*_PART_KEY_COLUMNS；

四、分区表的分类

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d464vEAa-1691117526428)(media/分区表)]

**注:**示例写的有点复杂，换了一个博客看的示例：

https://blog.csdn.net/jarniyy/article/details/83994562

Oracle表分区分为四种：范围分区，散列分区，列表分区和复合分区

一：范围分区

就是根据数据库表中某一字段的值的范围来划分分区，例如：

范围分区基础数据

create table graderecord  
  (  
  sno varchar2(10),  
  sname varchar2(20),  
  dormitory varchar2(3),  
  grade int  
)  
partition by range(grade)  
(  
  partition bujige values less than(60), --不及格  
  partition jige values less than(85), --及格  
  partition youxiu values less than(maxvalue) --优秀  
) ; 
insert into graderecord values('511601','魁','229',92);  
insert into graderecord values('511602','凯','229',62);  
insert into graderecord values('511603','东','229',26);  
insert into graderecord values('511604','亮','228',77);  
insert into graderecord values('511605','敬','228',47);  
insert into graderecord(sno,sname,dormitory) values('511606','峰','228');  
insert into graderecord values('511607','明','240',90);  
insert into graderecord values('511608','楠','240',100);  
insert into graderecord values('511609','涛','240',67);  
insert into graderecord values('511610','博','240',75);  
insert into graderecord values('511611','铮','240',60);

范围分区查询

--select *
C##SCOTT@LHRCDB> select * from graderecord;  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511603                         东                                                           229               26
511605                         敬                                                           228               47
511602                         凯                                                           229               62
511604                         亮                                                           228               77
511609                         涛                                                           240               67
511610                         博                                                           240               75
511611                         铮                                                           240               60
511601                         魁                                                           229               92
511606                         峰                                                           228
511607                         明                                                           240               90
511608                         楠                                                           240              100

已选择 11 行。

C##SCOTT@LHRCDB> 
--查询不及格

C##SCOTT@LHRCDB> select * from graderecord partition(bujige);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511603                         东                                                           229               26
511605                         敬                                                           228               47

C##SCOTT@LHRCDB> 
--查询及格
C##SCOTT@LHRCDB> select * from graderecord partition(jige);

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511602                         凯                                                           229               62
511604                         亮                                                           228               77
511609                         涛                                                           240               67
511610                         博                                                           240               75
511611                         铮                                                           240               60

C##SCOTT@LHRCDB> 
--查询优秀
C##SCOTT@LHRCDB> select * from graderecord partition(youxiu);

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511601                         魁                                                           229               92
511606                         峰                                                           228
511607                         明                                                           240               90
511608                         楠                                                           240              100

C##SCOTT@LHRCDB>

说明：数据中有空值，Oracle机制会自动将其规划到maxvalue的分区中。

二：散列分区

散列分区是根据字段的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等。

还是刚才那个表，只不过把范围分区改换为散列分区，语法如下（删除表之后重建）：

hash分区基础数据

create table graderecord  
(  
  sno varchar2(10),  
  sname varchar2(20),  
  dormitory varchar2(3),  
  grade int  
)  
partition by hash(sno)  
(  
  partition p1,  
  partition p2,  
  partition p3  
);  
insert into graderecord values('511601','魁','229',92);  
insert into graderecord values('511602','凯','229',62);  
insert into graderecord values('511603','东','229',26);  
insert into graderecord values('511604','亮','228',77);  
insert into graderecord values('511605','敬','228',47);  
insert into graderecord(sno,sname,dormitory) values('511606','峰','228');  
insert into graderecord values('511607','明','240',90);  
insert into graderecord values('511608','楠','240',100);  
insert into graderecord values('511609','涛','240',67);  
insert into graderecord values('511610','博','240',75);  
insert into graderecord values('511611','铮','240',60);

hash分区查询

C##SCOTT@LHRCDB> select * from graderecord partition(p1);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511603                         东                                                           229               26
511608                         楠                                                           240              100

C##SCOTT@LHRCDB> select * from graderecord partition(p2);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511602                         凯                                                           229               62
511604                         亮                                                           228               77
511605                         敬                                                           228               47
511606                         峰                                                           228
511607                         明                                                           240               90
511609                         涛                                                           240               67

已选择 6 行。

C##SCOTT@LHRCDB> select * from graderecord partition(p3);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511601                         魁                                                           229               92
511610                         博                                                           240               75
511611                         铮                                                           240               60

C##SCOTT@LHRCDB>

三：列表分区

列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。

list分区基础数据

create table graderecord  
(  
  sno varchar2(10),  
  sname varchar2(20),  
  dormitory varchar2(3),  
  grade int  
)  
partition by list(dormitory)  
(  
  partition d229 values('229'),  
  partition d228 values('228'),  
  partition d240 values('240')  
) ;
insert into graderecord values('511601','魁','229',92);  
insert into graderecord values('511602','凯','229',62);  
insert into graderecord values('511603','东','229',26);  
insert into graderecord values('511604','亮','228',77);  
insert into graderecord values('511605','敬','228',47);  
insert into graderecord(sno,sname,dormitory) values('511606','峰','228');  
insert into graderecord values('511607','明','240',90);  
insert into graderecord values('511608','楠','240',100);  
insert into graderecord values('511609','涛','240',67);  
insert into graderecord values('511610','博','240',75);  
insert into graderecord values('511611','铮','240',60);

list分区查询

以上根据宿舍来进行列表分区，插入与范围分区实验相同的数据，做查询如下：

C##SCOTT@LHRCDB> select * from graderecord partition(d229);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511601                         魁                                                           229               92
511602                         凯                                                           229               62
511603                         东                                                           229               26

C##SCOTT@LHRCDB> select * from graderecord partition(d228); 

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511604                         亮                                                           228               77
511605                         敬                                                           228               47
511606                         峰                                                           228

C##SCOTT@LHRCDB> select * from graderecord partition(d240);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511607                         明                                                           240               90
511608                         楠                                                           240              100
511609                         涛                                                           240               67
511610                         博                                                           240               75
511611                         铮                                                           240               60

C##SCOTT@LHRCDB>

四：复合分区（范围-散列分区，范围-列表分区,散列-列表）

首先讲范围-散列分区。先声明一下：列表分区不支持多列，但是范围分区和哈希分区支持多列。

create table graderecord  
(  
  sno varchar2(10),  
  sname varchar2(20),  
  dormitory varchar2(3),  
  grade int  
)  
partition by range(grade)  
subpartition by hash(sno,sname)  
(  
  partition p1 values less than(75)  
            (  
               subpartition sp1,subpartition sp2  
            ),  
  partition p2 values less than(maxvalue)  
            (  
               subpartition sp3,subpartition sp4  
            )  
);

以grade划分范围，然后以sno和sname划分散列分区，当数据量大的时候散列分区则趋于“平均”。

insert into graderecord values('511601','魁','229',92);  
insert into graderecord values('511602','凯','229',62);  
insert into graderecord values('511603','东','229',26);  
insert into graderecord values('511604','亮','228',77);  
insert into graderecord values('511605','敬','228',47);  
insert into graderecord(sno,sname,dormitory) values('511606','峰','228');  
insert into graderecord values('511607','明','240',90);  
insert into graderecord values('511608','楠','240',100);  
insert into graderecord values('511609','涛','240',67);  
insert into graderecord values('511610','博','240',75);  
insert into graderecord values('511611','铮','240',60);  
insert into graderecord values('511612','狸','244',72);  
insert into graderecord values('511613','杰','244',88);  
insert into graderecord values('511614','萎','244',19);  
insert into graderecord values('511615','猥','244',65);  
insert into graderecord values('511616','丹','244',59);  
insert into graderecord values('511617','靳','244',95);

范围-散列分区查询

--分区p1数据如下，本例中75分以下：
C##SCOTT@LHRCDB> select * from graderecord partition(p1);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511602                         凯                                                           229               62
511611                         铮                                                           240               60
511614                         萎                                                           244               19
511615                         猥                                                           244               65
511603                         东                                                           229               26
511605                         敬                                                           228               47
511609                         涛                                                           240               67
511612                         狸                                                           244               72
511616                         丹                                                           244               59

已选择 9 行。

C##SCOTT@LHRCDB> 
--分区p2数据如下，本例中75分之上包括75分：

C##SCOTT@LHRCDB> select * from graderecord partition(p2);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511606                         峰                                                           228
511607                         明                                                           240               90
511601                         魁                                                           229               92
511604                         亮                                                           228               77
511608                         楠                                                           240              100
511610                         博                                                           240               75
511613                         杰                                                           244               88
511617                         靳                                                           244               95

已选择 8 行。

C##SCOTT@LHRCDB> 
--子分区sp1
C##SCOTT@LHRCDB> select * from graderecord subpartition(sp1);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511602                         凯                                                           229               62
511611                         铮                                                           240               60
511614                         萎                                                           244               19
511615                         猥                                                           244               65

C##SCOTT@LHRCDB> 
--子分区sp2：
C##SCOTT@LHRCDB> select * from graderecord subpartition(sp2);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511603                         东                                                           229               26
511605                         敬                                                           228               47
511609                         涛                                                           240               67
511612                         狸                                                           244               72
511616                         丹                                                           244               59

C##SCOTT@LHRCDB> 
--子分区sp3：
C##SCOTT@LHRCDB> select * from graderecord subpartition(sp3);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511606                         峰                                                           228
511607                         明                                                           240               90

C##SCOTT@LHRCDB> 
--子分区sp4：
C##SCOTT@LHRCDB> select * from graderecord subpartition(sp4);  

SNO                            SNAME                                                        DORMITORY      GRADE
------------------------------ ------------------------------------------------------------ --------- ----------
511601                         魁                                                           229               92
511604                         亮                                                           228               77
511608                         楠                                                           240              100
511610                         博                                                           240               75
511613                         杰                                                           244               88
511617                         靳                                                           244               95

已选择 6 行。

C##SCOTT@LHRCDB>

说明：当数据量越来越大时，哈希分区的分区表中数据越来越趋于平衡。

下面讲范围-列表分区

范围-列表分区有两种创立方式，先说说没有模板的创建方式，这个表我要重建：

范围-列表分区基础数据

create table MobileMessage  
(  
 ACCT_MONTH VARCHAR2(6), -- 帐期 格式：年月 YYYYMM  
 AREA_NO VARCHAR2(10), -- 地域号码   
 DAY_ID VARCHAR2(2), -- 本月中的第几天 格式 DD  
 SUBSCRBID VARCHAR2(20), -- 用户标识   
 SVCNUM VARCHAR2(30) -- 手机号码  
)  
partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID)  
(  
  partition p1 values less than('200705','012')  
  (  
    subpartition shangxun1 values('01','02','03','04','05','06','07','08','09','10'),  
    subpartition zhongxun1 values('11','12','13','14','15','16','17','18','19','20'),  
    subpartition xiaxun1 values('21','22','23','24','25','26','27','28','29','30','31')  
  ),  
  partition p2 values less than('200709','014')  
  (  
    subpartition shangxun2 values('01','02','03','04','05','06','07','08','09','10'),  
    subpartition zhongxun2 values('11','12','13','14','15','16','17','18','19','20'),  
    subpartition xiaxun2 values('21','22','23','24','25','26','27','28','29','30','31')  
  ),  
  partition p3 values less than('200801','016')  
  (  
    subpartition shangxun3 values('01','02','03','04','05','06','07','08','09','10'),  
    subpartition zhongxun3 values('11','12','13','14','15','16','17','18','19','20'),  
    subpartition xiaxun3 values('21','22','23','24','25','26','27','28','29','30','31')  
  )  
)  ;
insert into MobileMessage values('200701','010','04','ghk001','13800000000');  
insert into MobileMessage values('200702','015','12','myx001','13633330000');  
insert into MobileMessage values('200703','015','24','hjd001','13300000000');  
insert into MobileMessage values('200704','010','04','ghk001','13800000000');  
insert into MobileMessage values('200705','010','04','ghk001','13800000000');  
insert into MobileMessage values('200705','011','18','sxl001','13222000000');  
insert into MobileMessage values('200706','011','21','sxl001','13222000000');  
insert into MobileMessage values('200706','012','11','tgg001','13800044400');  
insert into MobileMessage values('200707','010','04','ghk001','13800000000');  
insert into MobileMessage values('200708','012','24','tgg001','13800044400');  
insert into MobileMessage values('200709','014','29','zjj001','13100000000');  
insert into MobileMessage values('200710','014','29','zjj001','13100000000');  
insert into MobileMessage values('200711','014','29','zjj001','13100000000');  
insert into MobileMessage values('200711','013','30','wgc001','13444000000');  
insert into MobileMessage values('200712','013','30','wgc001','13444000000');  
insert into MobileMessage values('200712','010','30','ghk001','13800000000');  
insert into MobileMessage values('200801','015','22','myx001','13633330000');

范围-列表分区查询

--分区p1查询结果如下：
C##SCOTT@LHRCDB> select * from MobileMessage partition(p1);  

ACCT_MONTH         AREA_NO                        DAY_ID SUBSCRBID                                                    SVCNUM
------------------ ------------------------------ ------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------
200701             010                            04     ghk001                                                       13800000000
200704             010                            04     ghk001                                                       13800000000
200705             010                            04     ghk001                                                       13800000000
200702             015                            12     myx001                                                       13633330000
200705             011                            18     sxl001                                                       13222000000
200703             015                            24     hjd001                                                       13300000000

已选择 6 行。
--分区p2查询结果如下：
查询结果如下：

C##SCOTT@LHRCDB> select * from MobileMessage partition(p2);  

ACCT_MONTH         AREA_NO                        DAY_ID SUBSCRBID                                                    SVCNUM
------------------ ------------------------------ ------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------
200707             010                            04     ghk001                                                       13800000000
200706             012                            11     tgg001                                                       13800044400
200706             011                            21     sxl001                                                       13222000000
200708             012                            24     tgg001                                                       13800044400

C##SCOTT@LHRCDB> 
--子分区xiaxun2查询结果如下：
C##SCOTT@LHRCDB>  select * from MobileMessage subpartition(xiaxun2);  

ACCT_MONTH         AREA_NO                        DAY_ID SUBSCRBID                                                    SVCNUM
------------------ ------------------------------ ------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------
200706             011                            21     sxl001                                                       13222000000
200708             012                            24     tgg001                                                       13800044400

C##SCOTT@LHRCDB>

说明：范围分区 range(A,B)的分区法则，范围分区都是 values less than(A,B)的，通常情况下以A为准，如果小于A的不用考虑B，直接插进去，如果等于A那么考虑B，要是满足B的话也插进去。

另一种范围-列表分区，包含模板的（比较繁琐，但是更加精确，处理海量存储数据十分必要）：

范围-列表分区，包含模板基础数据

create table MobileMessage  
(  
 ACCT_MONTH VARCHAR2(6), -- 帐期 格式：年月 YYYYMM  
 AREA_NO VARCHAR2(10), -- 地域号码  
 DAY_ID VARCHAR2(2), -- 本月中的第几天 格式 DD  
 SUBSCRBID VARCHAR2(20), -- 用户标识   
 SVCNUM VARCHAR2(30) -- 手机号码  
)  
partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID)  
subpartition template  
(  
 subpartition sub1 values('01'),subpartition sub2 values('02'),  
 subpartition sub3 values('03'),subpartition sub4 values('04'),  
 subpartition sub5 values('05'),subpartition sub6 values('06'),  
 subpartition sub7 values('07'),subpartition sub8 values('08'),  
 subpartition sub9 values('09'),subpartition sub10 values('10'),  
 subpartition sub11 values('11'),subpartition sub12 values('12'),  
 subpartition sub13 values('13'),subpartition sub14 values('14'),  
 subpartition sub15 values('15'),subpartition sub16 values('16'),  
 subpartition sub17 values('17'),subpartition sub18 values('18'),  
 subpartition sub19 values('19'),subpartition sub20 values('20'),  
 subpartition sub21 values('21'),subpartition sub22 values('22'),  
 subpartition sub23 values('23'),subpartition sub24 values('24'),  
 subpartition sub25 values('25'),subpartition sub26 values('26'),  
 subpartition sub27 values('27'),subpartition sub28 values('28'),  
 subpartition sub29 values('29'),subpartition sub30 values('30'),  
 subpartition sub31 values('31')  
)  
(  
  partition p_0701_010 values less than('200701','011'),  
  partition p_0701_011 values less than('200701','012'),  
  partition p_0701_012 values less than('200701','013'),  
  partition p_0701_013 values less than('200701','014'),  
  partition p_0701_014 values less than('200701','015'),  
  partition p_0701_015 values less than('200701','016'),  
  partition p_0702_010 values less than('200702','011'),  
  partition p_0702_011 values less than('200702','012'),  
  partition p_0702_012 values less than('200702','013'),  
  partition p_0702_013 values less than('200702','014'),  
  partition p_0702_014 values less than('200702','015'),  
  partition p_0702_015 values less than('200702','016'),  
  partition p_0703_010 values less than('200703','011'),  
  partition p_0703_011 values less than('200703','012'),  
  partition p_0703_012 values less than('200703','013'),  
  partition p_0703_013 values less than('200703','014'),  
  partition p_0703_014 values less than('200703','015'),  
  partition p_0703_015 values less than('200703','016'),    
  partition p_0704_010 values less than('200704','011'),  
  partition p_0704_011 values less than('200704','012'),  
  partition p_0704_012 values less than('200704','013'),  
  partition p_0704_013 values less than('200704','014'),  
  partition p_0704_014 values less than('200704','015'),  
  partition p_0704_015 values less than('200704','016'),    
  partition p_0705_010 values less than('200705','011'),  
  partition p_0705_011 values less than('200705','012'),  
  partition p_0705_012 values less than('200705','013'),  
  partition p_0705_013 values less than('200705','014'),  
  partition p_0705_014 values less than('200705','015'),  
  partition p_0705_015 values less than('200705','016'),    
  partition p_0706_010 values less than('200706','011'),  
  partition p_0706_011 values less than('200706','012'),  
  partition p_0706_012 values less than('200706','013'),  
  partition p_0706_013 values less than('200706','014'),  
  partition p_0706_014 values less than('200706','015'),  
  partition p_0706_015 values less than('200706','016'),    
  partition p_0707_010 values less than('200707','011'),  
  partition p_0707_011 values less than('200707','012'),  
  partition p_0707_012 values less than('200707','013'),  
  partition p_0707_013 values less than('200707','014'),  
  partition p_0707_014 values less than('200707','015'),  
  partition p_0707_015 values less than('200707','016'),    
  partition p_0708_010 values less than('200708','011'),  
  partition p_0708_011 values less than('200708','012'),  
  partition p_0708_012 values less than('200708','013'),  
  partition p_0708_013 values less than('200708','014'),  
  partition p_0708_014 values less than('200708','015'),  
  partition p_0708_015 values less than('200708','016'),    
  partition p_0709_010 values less than('200709','011'),  
  partition p_0709_011 values less than('200709','012'),  
  partition p_0709_012 values less than('200709','013'),  
  partition p_0709_013 values less than('200709','014'),  
  partition p_0709_014 values less than('200709','015'),  
  partition p_0709_015 values less than('200709','016'),    
  partition p_0710_010 values less than('200710','011'),  
  partition p_0710_011 values less than('200710','012'),  
  partition p_0710_012 values less than('200710','013'),  
  partition p_0710_013 values less than('200710','014'),  
  partition p_0710_014 values less than('200710','015'),  
  partition p_0710_015 values less than('200710','016'),    
  partition p_0711_010 values less than('200711','011'),  
  partition p_0711_011 values less than('200711','012'),  
  partition p_0711_012 values less than('200711','013'),  
  partition p_0711_013 values less than('200711','014'),  
  partition p_0711_014 values less than('200711','015'),  
  partition p_0711_015 values less than('200711','016'),    
  partition p_0712_010 values less than('200712','011'),  
  partition p_0712_011 values less than('200712','012'),  
  partition p_0712_012 values less than('200712','013'),  
  partition p_0712_013 values less than('200712','014'),  
  partition p_0712_014 values less than('200712','015'),  
  partition p_0712_015 values less than('200712','016'),    
  partition p_0801_010 values less than('200801','011'),  
  partition p_0801_011 values less than('200801','012'),  
  partition p_0801_012 values less than('200801','013'),  
  partition p_0801_013 values less than('200801','014'),  
  partition p_0801_014 values less than('200801','015'),  
  partition p_0801_015 values less than('200801','016'),    
  partition p_other values less than(maxvalue, maxvalue)  
);  
insert into MobileMessage values('200701','010','04','ghk001','13800000000');  
insert into MobileMessage values('200702','015','12','myx001','13633330000');  
insert into MobileMessage values('200703','015','24','hjd001','13300000000');  
insert into MobileMessage values('200704','010','04','ghk001','13800000000');  
insert into MobileMessage values('200705','010','04','ghk001','13800000000');  
insert into MobileMessage values('200705','011','18','sxl001','13222000000');  
insert into MobileMessage values('200706','011','21','sxl001','13222000000');  
insert into MobileMessage values('200706','012','11','tgg001','13800044400');  
insert into MobileMessage values('200707','010','04','ghk001','13800000000');  
insert into MobileMessage values('200708','012','24','tgg001','13800044400');  
insert into MobileMessage values('200709','014','29','zjj001','13100000000');  
insert into MobileMessage values('200710','014','29','zjj001','13100000000');  
insert into MobileMessage values('200711','014','29','zjj001','13100000000');  
insert into MobileMessage values('200711','013','30','wgc001','13444000000');  
insert into MobileMessage values('200712','013','30','wgc001','13444000000');  
insert into MobileMessage values('200712','010','30','ghk001','13800000000');  
insert into MobileMessage values('200801','015','22','myx001','13633330000');

这个是带有模板子分区的，模板子分区详细到月中的天。这种分区模式只要建立了分区就会自动创建子分区的。

插入上面不带模板分区实验相同的数据，随机查询分区数据：

范围-列表分区，包含模板查询

--查询分区p_0701_010的数据：
C##SCOTT@LHRCDB> select * from MobileMessage partition(p_0701_010);  

ACCT_MONTH         AREA_NO                        DAY_ID SUBSCRBID                                                    SVCNUM
------------------ ------------------------------ ------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------
200701             010                            04     ghk001                                                       13800000000

C##SCOTT@LHRCDB> 
--查询子分区p_0701_010_sub4的数据：
C##SCOTT@LHRCDB> select * from MobileMessage subpartition(p_0701_010_sub4);  

ACCT_MONTH         AREA_NO                        DAY_ID SUBSCRBID                                                    SVCNUM
------------------ ------------------------------ ------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------
200701             010                            04     ghk001                                                       13800000000

C##SCOTT@LHRCDB> 
--查询分区p_0706_011的数据：
C##SCOTT@LHRCDB> select * from MobileMessage partition(p_0706_011);  

ACCT_MONTH         AREA_NO                        DAY_ID SUBSCRBID                                                    SVCNUM
------------------ ------------------------------ ------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------
200706             011                            21     sxl001                                                       13222000000

C##SCOTT@LHRCDB> 
--查询子分区p_0706_011_sub21的数据：
C##SCOTT@LHRCDB> select * from MobileMessage subpartition(p_0706_011_sub21);  

ACCT_MONTH         AREA_NO                        DAY_ID SUBSCRBID                                                    SVCNUM
------------------ ------------------------------ ------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------
200706             011                            21     sxl001                                                       13222000000

C##SCOTT@LHRCDB>

列表哈希组合分区

列表哈希基础数据

create table WORKER_202307
(
  id        VARCHAR2(100) not null,
  name      VARCHAR2(200),
  technology      VARCHAR2(100),
  save_date DATE
)
partition by list (technology) SUBPARTITION BY HASH (id) 
(
  partition technology_java values ('java')
  (
   SUBPARTITION worker_id_1,
     SUBPARTITION worker_id_2,
     SUBPARTITION worker_id_3,
   SUBPARTITION worker_id_4
    ),
  partition technology_python values ('python')
  (
   SUBPARTITION worker_id_5,
     SUBPARTITION worker_id_6,
     SUBPARTITION worker_id_7,
   SUBPARTITION worker_id_8
    )
);
insert into worker_202307 (id,name,technology,save_date) values ('101','哪吒','java',to_date('2023/7/28 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('102','云韵','java',to_date('2023/7/28 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('103','美杜莎','java',to_date('2023/7/28 00:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('104','哪吒','java',to_date('2023/7/28 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('105','云韵1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('106','美杜莎1','python',to_date('2023/7/29 00:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('107','哪吒1','python',to_date('2023/7/29 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('108','云韵1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('109','云韵1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('110','云韵1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));

列表-哈希查询分区数据

select count(1) from worker_202307 PARTITION (technology_java);
select count(1) from worker_202307 PARTITION (technology_python);
select count(1) from worker_202307 SUBPARTITION (worker_id_1);
select count(1) from worker_202307 SUBPARTITION (worker_id_2);
select count(1) from worker_202307 SUBPARTITION (worker_id_5);
select count(1) from worker_202307 SUBPARTITION (worker_id_6);

风和雪的爱情

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Oracle分区

参考：http://blog.itpub.net/70024420/viewspace-2933268/大家好，我是哪吒，最近项目有一个新的需求，按月建表，按天分区。不都是分库分表吗？怎么又来个分区？让我们一起，一探究竟，深入理解一下Oracle分区表技术，实现快速入门，丰富个人简历，提高面试level，给自己增加一点谈资，秒变面试小达人，BAT不是梦。
复制链接

扫一扫