Oracle创建表分区

Oracle数据库分区技术详解

最新推荐文章于 2023-04-26 19:49:24 发布

原创最新推荐文章于 2023-04-26 19:49:24 发布 · 5.8k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #优化 #分区 #数据

SpringMVC+Hibernate+Spring 专栏收录该内容

15 篇文章

订阅专栏

本文介绍了Oracle数据库中创建表分区的必要性和好处，包括提高查询性能、可用性、维护便捷和均衡I/O。讨论了范围分区、散列分区和复合分区的概念，并通过实例展示了创建和查询这些分区的方法。分区对于大数据量表的管理和优化至关重要，但也存在如表转换为分区困难等缺点。

直接进入主题----可以这样理解，当一个数据表在插入数据的时候，在几百条或者几千条数据中查询目标数据的时候不会花费多长时间，最多一两秒！但是随着数据的不断增加，当达到上万条或者几百万条的时候，当你在查询某个数据，这是可能会花费几分钟，甚至会导致系统挂掉！这个时候就需要考虑分区！
分区条件：

表的大小超过2GB;

表中含有历史数据，新的数据被增加到新的分区中.

优点：

提高查询的性能:对分区对象的查询可以
提高可用性:如果某个分区出现故障，表在其他分区的数据依然可以使用
维护方便:如果某个表出现故障，需要修复数据，质修复该分区即可
均衡I/O:可以吧不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

缺点:
分区表相关:已经存在的表没有方法可以直接转化为分区，不过Oracle提供了在线重定义表的功能
分区的几种类型及操作方法:范围分区、散列分区、列表分区、复合分区

范围分区：

顾名思义，就是根据某个值，根据给定值的范围将整个表分区，就像分数等级一样；

create table partition_range (
    id number(19,0) not null,
    name varchar2(128 char) not null,
    score number(10,0) not null,
    primary key(id)
)partition by range(score)
(
  partition bujige values less than(60), --不及格  
  partition jige values less than(85), --及格  
  partition youxiu values less than(maxvalue) --优秀  
<p>);<span style="font-family:Microsoft YaHei;font-size:14px;">
</span></p><p>
</p>

根据字段‘score’的范围分成不及格、及格和优秀三个分区，给定值的范围
插入测试数据：

insert into partition_range values(1,'sd1',54);
insert into partition_range values(2,'sd2',65);
insert into partition_range values(3,'sd3',87);
insert into partition_range values(4,'sd4',90);
insert into partition_range values(5,'sd5',76);

数据不多，但是每个分区都有

查询语句：

select * from partition_range 
select * from partition_range partition(bujige)
select * from partition_range partition(jige)
select * from partition_range partition(youxiu)

结果分别是：

全部

不及格

及格

优秀

需要注意的三点：

1、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。

2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。

3、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。

列表分区：

根据数据表中的某个字段分区。还是上一个表的创表语句，分区语句改变了：

create table partition_list (
    id number(19,0) not null,
    name varchar2(128 char) not null,
    score number(10,0) not null,
    primary key(id)
)partition by list(name)
(
  partition sd1 values('sd1'), 
  partition sd2 values('sd2'), 
  partition sd3 values('sd3') 
);

根据字段'name'具体的值分区，不是给定范围而是给定值
插入语句：

insert into partition_list values(1,'sd1',60);
insert into partition_list values(2,'sd2',65);
insert into partition_list values(3,'sd2',60);
insert into partition_list values(4,'sd3',85);
insert into partition_list values(5,'sd3',87);
insert into partition_list values(6,'sd3',85);
insert into partition_list values(7,'sd3',100);

查询语句：

select * from partition_list 
select * from partition_list partition(sd1)
select * from partition_list partition(sd2)
select * from partition_list partition(sd3)

结果：

全部

分区sd1：

分区sd2：

分区sd3:

以上两个很好区别，根据字段值的范围和具体值来分区。

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

create table partition_hash (
    id number(19,0) not null,
    name varchar2(128 char) not null,
    score number(10,0) not null,
    primary key(id)
)partition by hash(id)
(
  partition sd1, 
  partition sd2, 
  partition sd3 
);

和以上有区别，直接给定分区名就可以了
查询结果
分区sd1

分区sd2

分区sd3

注意:散列分区即为哈希分区，Oracle采用哈希码技术分区，分区方式有Oracle的哈希算法为主，也可能下一次搜索就不是这个数据了

复合分区：

复合分区就是将上面的三个方法组合比如范围-列表，范围-散列...，Oracle 11g里还有个间隔分区于是复合也就多了间隔-列表，间隔散列等分区，这几天被范围和列表分区搞死了，就介绍范围---列表分区吧，对了列表分区不支持多列，但是范围分区和哈希分区支持多列

先看看一张图：

将数据分成ABC三个分区，然后再分别分成子分区如A又分成xyz三个小分区，注意调用分区使用的名字，下面会讲解

首先是普通的创建

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')  
  )  
)

注意上面的格式，partition后面跟着subpartition，这个就是对每个分区下建立三个小分区，然后插入数据

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');

查询：

全部数据

这样创建分区后，查询子分区就直接写子分区的名字，这样的方法对分区少的的很合适，但是要是遇到分区几千个，这就不适用了，所以就有了带模板的分区

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)  
);

注意这个创建格式，先将子分区的模板创建好，在写父分区，好处就是，没创建一个父分区的时候，子分区就会根据模板自动创建，不需要认为的添加，插入的数据和上面的一样，下面显示查询结果，注意查询子分区时候使用的名字

注意！！！

需要因为是自动生成的，所以格式就起了作用，父分区+“_”+子分区

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

仔细推敲这句话！小于或等于A的以等于为准，再考虑B！就为这花了我跟师傅一上午的时间！！！

产看表中分区信息需要用到的语句

查询父分区信息

select * from USER_TAB_PARTITIONS u where u.table_name = 'MOBILEMESSAGE'

注意表名大写
查询子分区信息

select * from USER_TAB_SUBPARTITIONS u where u.table_name = 'MOBILEMESSAGE'

目前使用分区就涉及了以上知识点！肯定不全面，在以后的学习中，我会继续跟新Oracle分区的使用方法！！！

感谢这篇博文http://love-flying-snow.iteye.com/blog/573303，文中数据就是引用次博主