ITPub博客

首页 > 数据库 > Oracle > Oracle 分区表 -- Range分区

Oracle 分区表 -- Range分区

Oracle 作者:宋祖强 时间:2016-01-11 17:37:03 0 删除 编辑

一、什么是分区表

       Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断,把分区列不同的记录,放到不同的分区中。分区完全对应用透明。

       Oracle的分区表可以包括多个分区, 每个分区都是一个独立的段( SEGMENT),可以存放到不同的表空间中 。查询时可以通过查询表来访问各个分区中的数据,也可以通过在查询时直接指定分区的方法来进行查询。

二、什么时候用分区表

When to Partition a Table 什么时候需要分区表,官网的 2 个建议如下:

(1)Tables greater than 2GB should always be considered for partitioning.

(2)Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.(历史数据可读,新数据可读可写)

二、分区表优点


       (1)由于将数据分散到各个分区中,减少了数据损坏的可能性;

       (2)可以对单独的分区进行备份和恢复;

       (3)可以将分区映射到不同的物理磁盘上,来分散IO;

       (4)提高可管理性、可用性和性能。

四、分区表类型

       (1)范围分区(range)——我们这篇博文的内容;

       (2)哈希分区(hash);

       (3)列表分区(list);

       (4)范围-哈希复合分区(range-hash);

       (5)范围-列表复合分区(range-list)。

五、Range 分区

Range分区是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中。

       如按照时间划分,2010年1月的数据放到a分区,2月的数据放到b分区,在创建的时候,需要指定基于的列,以及分区的范围值。

       在按时间分区时, 如果某些记录暂无法预测范围,可以创建 maxvalue 分区,所有不在指定范围内的记录都会被存储到 maxvalue 所在分区中。

六、Range分区实例说明

例子:创建一张表,表有两个字段(ID、TIME),我们完成对TIME列的范围分区,即将1-12月创建成对应的12个分区表,对分区表及分区表数据进行增删查改操作。

创建分区表:

创建表同时完成对表进行分区(按月份分区)

 
/*创建分区表*/ 
create table pdba (id number, time date) partition by range (time)
(  partition p1 values less than (to_date('2013-02-1', 'yyyy-mm-dd')),  
 partition p2 values less than (to_date('2013-03-1', 'yyyy-mm-dd')),  
 partition p3 values less than (to_date('2013-04-1', 'yyyy-mm-dd')),  
 partition p4 values less than (to_date('2013-05-1', 'yyyy-mm-dd')),
  partition p5 values less than (to_date('2013-06-1', 'yyyy-mm-dd')),
  partition p6 values less than (to_date('2013-07-1', 'yyyy-mm-dd')),
  partition p7 values less than (to_date('2013-08-1', 'yyyy-mm-dd')),
  partition p8 values less than (to_date('2013-09-1', 'yyyy-mm-dd')),
  partition p9 values less than (to_date('2013-10-1', 'yyyy-mm-dd')),
  partition p10 values less than (to_date('2013-11-1', 'yyyy-mm-dd')),
  partition p11 values less than (to_date('2013-12-1', 'yyyy-mm-dd')),
  partition p12 values less than (to_date('2014-01-1', 'yyyy-mm-dd'))  
 --partition p13 values less than (maxvalue)    --不建议使用
)

查询所有分区表,验证分区表是否创建成功。

select * from USER_TAB_PARTITIONS a;      --不能加任何where条件

结果如下图:

添加数据:

为了测试效果,我们添加120万条数据,每月添加10万条数据:

 
/*添加数据*/
SQL> declare   
  begin  
           for n in 1..100000 LOOP  
             insert into pdba VALUES(1,to_date('2013-01-05','yyyy-mm-dd'));
             insert into pdba VALUES(2,to_date('2013-02-05','yyyy-mm-dd'));
             insert into pdba VALUES(3,to_date('2013-03-05','yyyy-mm-dd'));
             insert into pdba VALUES(4,to_date('2013-04-05','yyyy-mm-dd'));
             insert into pdba VALUES(5,to_date('2013-05-05','yyyy-mm-dd'));
             insert into pdba VALUES(6,to_date('2013-06-05','yyyy-mm-dd'));
             insert into pdba VALUES(7,to_date('2013-07-05','yyyy-mm-dd'));
             insert into pdba VALUES(8,to_date('2013-08-05','yyyy-mm-dd'));
             insert into pdba VALUES(9,to_date('2013-09-05','yyyy-mm-dd'));
             insert into pdba VALUES(10,to_date('2013-10-05','yyyy-mm-dd'));
             insert into pdba VALUES(11,to_date('2013-11-05','yyyy-mm-dd'));
             insert into pdba VALUES(12,to_date('2013-12-05','yyyy-mm-dd'));
      end loop;   
 end;
 / 

测试数据是否添加正确:

先查看物理表记录:

/*验证*/ 
select count(*) from pdba
结果如下图:
 

再随机查看分区表,数据, 比如我们查看12月份的分区表数据,理论应该是10W条。

/*分区表查询*/ select count(*) from pdba partition(p12);     --一12月份
 
       

至此我们创建、验证、并向表中添加数据 完成。

查询分区表:

/*分区表查询*/ 
select * from pdba partition(p12);         --12月份

删除分区表:

( 数据也会被删除 )

/*删除分区表*/ 
ALTER TABLE pdba DROP PARTITION P12;

当我们再次查询十二月份的分区表时:
/*分区表查询*/
 select * from pdba partition(p12);        --12月份
 
结果如下:

而我们物理表中数据也会减少10w条。

select count(*) from pdba;

结果如下:
 

增加分区表:

现在的物理表只有1到11月份的分区表,那我们再次添加12月份的分区表并再次添加10w条数据

/*增加分区表*/ 
ALTER TABLE pdba ADD PARTITION P12 VALUES LESS THAN(TO_DATE('2014-01-01','YYYY-MM-DD'));


添加数据:

/*添加数据*/
declare 
 begin for n in 1..100000 LOOP   
 insert into pdba VALUES(12,to_date('2013-12-05','yyyy-mm-dd'));
  end loop;   
end;
/

再次查询十二月份的分区表验证操作是否成功

/*分区表查询*/ 
select count(*) from pdba partition(p12)  --12月份


结果如图:

 

合并分区表:

例子:将11月和12月的分区表合并在一起。

/*合并分区表*/ 
ALTER TABLE pdba MERGE PARTITIONS p11, p12 INTO PARTITION p13


分区表合并后,原分区表空间被释放,原数据均转移到新的表空间下。

现在查询新的分区表,数据应该为20w条。

select count(*) from pdba partition(p13);


结果如图:

分割分区表:

例子:将p13分区表分割成p11和p12分区表,即将11月和12月数据重新分割开。

/*一个分区表分割成两个分区表*/
 alter table pdba split Partition p13 at (to_date('2013-12-01','yyyy-mm-dd')) into (Partition p11,Partition p12);


过程和合并分区表下相反。

现在p11和p12表空间和数据重新恢复和添加。

11月和12月各10w条数据

更改分区表名:

--更改分区表名 
alter table pdba rename Partition p11 to p22;

增删查改分区表的数据:

操作与物理表几乎相同, 只是操作分区表时在分区表前加入 物理表名称即可。具体如下图:

 
--查询分区表数据 
select DISTINCT * from pdba partition(p12);  
--修改分区表数据 
update pdba partition(p12) p set p.time = to_date('2013-12-08','yyyy-mm-dd'); 
--增加分区表数据
insert into pdba partition(p12) p VALUES(1,to_date('2013-12-29','yyyy-mm-dd'));
--删除分区表数据 
delete from pdba partition(p12) t where t.id = 1


添加range分区索引
global类型的,维护教麻烦,建议使用local类型的
create index range_tab_id_index1 on range_tab(my_date) global partition by range(my_date)
    (         partition p1 values less than(to_date('2015-10-1', 'yyyy-mm-dd')) tablespace test,
              partition p2 values less than(to_date('2015-11-1', 'yyyy-mm-dd')) tablespace test,
              partition p3 values less than(to_date('2015-12-1', 'yyyy-mm-dd')) tablespace test,
              partition p4 values less than(to_date('2016-01-1', 'yyyy-mm-dd')) tablespace test,
              partition p5 values less than(maxvalue) tablespace test    
      )
create index range_tab_id_index1 on range_tab(id,my_date) local;
local索引也可以指定分区名和对应的表空间名,但是要注意分区个数必须与表的分区个数一致!
create index range_tab_id_index on range_tab(id,my_date) local
       (
              partition p1 tablespace test,
              partition p2 tablespace test,
              partition p3 tablespace test,
              partition p4 tablespace test,
              partition p5 tablespace test,
              partition p6 tablespace test,
              partition p7 tablespace test,
              partition p8 tablespace test
       )
有关分区索引视图

select * from user_part_indexes;

select * from user_ind_partitions;


hash分区

创建hash分区

create table hash_tab (id varchar(20),hash_value varchar2(50))

   partition by hash(id)(

   partition p1 tablespace test,

   partition p2 tablespace test,

   partition p3 tablespace test);

需要注意的是hash分区不存在split和drop操作,只能add分区,数据在各个分区的分布情况并不由我们控制,而是通过oracle中的hash函数来操作的,所以我们不能显示的指定某一个分区进行分裂。
alter table hash_tab add partition p4 tablespace test;

创建global类型的hash索引
create index hash_tab_id_index on hash_tab(id)
  global partition by hash(id)(
     partition p1 tablespace test, 
   partition p2 tablespace test,
   partition p3 tablespace test
)

创建local类型的hash索引
create index hash_tab_id_index on hash_tab(id) local;

list分区
创建list分区表
create table list_tab(id varchar2(20),list_data varchar2(50))
   partition by list(id)(
    partition p1 values('001'),
    partition p2 values('002'),
    partition p3 values('003'),
    partition p4 values(default)
  )


拆分一个list分区
alter table list_tab split partition p4 values ('004')
into (partition p5 tablespace test,partition p4 tablespace test);
删除一个list分区
alter table list_tab drop partition p5;
添加一个list分区(分区里边界不能有maxvalue或者default)
alter table list_tab add partition p5 values('008');

组合分区:
如果某表按照某列分区之后,仍然较大,或者是一些其它的需求,还可以通过分区内再建子分区的方式将分区再分区,即组合分区的方式。
组合分区主要有两种:range-hash,range-list。

range-hash
create table range_hash_tab
(  id varchar2(20), t_date date )
  partition by range(t_date) subpartition by hash(id)
  subpartition template
(   subpartition sub_p1 tablespace test,
    subpartition sub_p2 tablespace test,
    subpartition sub_p3 tablespace test
 )
(   partition p01 values less than(to_date('2015-01-01','yyyy-mm-dd')),
    partition p02 values less than(to_date('2016-01-01','yyyy-mm-dd')),
    partition p03 values less than(maxvalue)
);
或者
create table range_hash_tab
( id varchar2(20), t_date date )
partition by range(t_date) subpartition by hash(id)
subpartition template
(  subpartition sub_p1 tablespace test,
   subpartition sub_p2 tablespace test,
   subpartition sub_p3 tablespace test
)
(
partition p01 values less than(to_date('2015-01-01','yyyy-mm-dd')),
partition p02 values less than(to_date('2016-01-01','yyyy-mm-dd')),
partition p03 values less than(maxvalue)
);


range-list
create table range_list_tab
(
id varchar2(20),
t_date date
)
partition by range(t_date) subpartition by list(id)
(partition p01 values less than(to_date('2015-01-01','yyyy-mm-dd'))
(
subpartition p01_sub01 values('001','002'),
subpartition p01_sub02 values('003','004'),
subpartition p01_sub03 values('005','006'),
subpartition p01_sub04 values(default)
),
partition p02 values less than(to_date('2015-02-01','yyyy-mm-dd'))
(
subpartition p02_sub01 values('001','002'),
subpartition p02_sub02 values('003','004'),
subpartition p02_sub03 values('005','006'),
subpartition p02_sub04 values(default)
),
partition p03 values less than(to_date('2015-03-01','yyyy-mm-dd'))
(
subpartition p03_sub01 values('001','002'),
subpartition p03_sub02 values('003','004'),
subpartition p03_sub03 values('005','006'),
subpartition p03_sub04 values(default)
));

七、附录:


显示数据库所有分区表的信息:DBA_PART_TABLES 

显示当前用户可访问的所有分区表信息:ALL_PART_TABLES 

显示当前用户所有分区表的信息:USER_PART_TABLES 

显示表分区信息 显示数据库所有分区表的详细分区信息:DBA_TAB_PARTITIONS 

显示当前用户可访问的所有分区表的详细分区信息:ALL_TAB_PARTITIONS 

显示当前用户所有分区表的详细分区信息:USER_TAB_PARTITIONS 

显示子分区信息 显示数据库所有组合分区表的子分区信息:DBA_TAB_SUBPARTITIONS 

显示当前用户可访问的所有组合分区表的子分区信息:ALL_TAB_SUBPARTITIONS 

显示当前用户所有组合分区表的子分区信息:USER_TAB_SUBPARTITIONS 

显示分区列 显示数据库所有分区表的分区列信息:DBA_PART_KEY_COLUMNS 

显示当前用户可访问的所有分区表的分区列信息:ALL_PART_KEY_COLUMNS 

显示当前用户所有分区表的分区列信息:USER_PART_KEY_COLUMNS 

显示子分区列 显示数据库所有分区表的子分区列信息:DBA_SUBPART_KEY_COLUMNS 

显示当前用户可访问的所有分区表的子分区列信息:ALL_SUBPART_KEY_COLUMNS 

显示当前用户所有分区表的子分区列信息:USER_SUBPART_KEY_COLUMNS 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29289867/viewspace-1974938/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-08-13

  • 博文量
    176
  • 访问量
    277126