`
这些年
  • 浏览: 399845 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

分区表(转)

 
阅读更多

一、分区表

分区表的几种分类:
1、Range(范围)分区
是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的
range分区中。比如按照时间划分,2012 年1 季度的数据放到a 分区,12年2 季度的数据放到b分区,
因此在创建的时候呢,需要你指定基于的列,以及分区的范围值,如果某些记录暂无法预测范围,
可以创建maxvalue 分区,所有不在指定范围内的记录都会被存储到maxvalue 所在分区中,
并且支持指定多列做为依赖列。

创建RANGE分区事例
需要我们指定的有:
 column:分区依赖列(如果是多个,以逗号分隔);
 partition:分区名称;
 values less than:后跟分区范围值(如果依赖列有多个,范围对应值也应是多个,中间以逗号分隔);
 tablespace_clause:分区的存储属性,例如所在表空间等属性(可为空),默认继承基表所在表空间的属性。

例:

CREATE TABLE RM_CUST_MONTH_RANGE
(
  YEAR_MONTH                   NUMBER(6) NOT NULL,
  ORG_CODE                     VARCHAR2(8),
  REGIE_ORG_CODE          VARCHAR2(8),
  CUST_CODE                    VARCHAR2(12),
  CUST_NAME                    VARCHAR2(200), 
  CUST_LICENCE_CODE       VARCHAR2(18)
  )
PARTITION BY RANGE(YEAR_MONTH)(
PARTITION YEAR_MONTH_1201 VALUES LESS THAN(201201) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_1202 VALUES LESS THAN(201202) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_1203 VALUES LESS THAN(201203) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_1204 VALUES LESS THAN(201204) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_1205 VALUES LESS THAN(201205) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_1206 VALUES LESS THAN(201206) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_MAXVALUE VALUES LESS THAN(MAXVALUE) TABLESPACE RMS_DATA);

 

2、Hash(哈希)分区
对于那些无法有效划分范围的表,可以使用hash 分区。
hash 分区会将表中的数据平均分配到你指定的几个分区中,列所在分区是依据分区列的hash 值自动分配,
因此你并不能控制也不知道哪条记录会被放到哪个分区中,hash 分区也可以支持多个依赖列。

创建hash分区事例
column:分区依赖列(支持多个,中间以逗号分隔);
partition:指定分区,有两种方式:
 (1)直接指定分区名,分区所在表空间等信息。
 (2)只指定分区数量,和可供使用的表空间。
例:

CREATE TABLE RM_CUST_MONTH_HASH
(
  YEAR_MONTH        NUMBER(6) NOT NULL,
  ORG_CODE          VARCHAR2(8),
  REGIE_ORG_CODE    VARCHAR2(8),
  CUST_CODE         VARCHAR2(12),
  CUST_NAME         VARCHAR2(200),
  CUST_LICENCE_CODE VARCHAR2(18)
)
 PARTITION BY HASH (YEAR_MONTH)(
 PARTITION T_HASH_P1 TABLESPACE RMS_DATA,
 PARTITION T_HASH_P2 TABLESPACE RMS_DATA,
 PARTITION T_HASH_P3 TABLESPACE RMS_DATA);

 

或者:

CREATE TABLE RM_CUST_MONTH_HASH_2
(
  YEAR_MONTH        NUMBER(6) NOT NULL,
  ORG_CODE          VARCHAR2(8),
  REGIE_ORG_CODE    VARCHAR2(8),
  CUST_CODE         VARCHAR2(12),
  CUST_NAME         VARCHAR2(200),
  CUST_LICENCE_CODE VARCHAR2(18)
)
PARTITION BY HASH (YEAR_MONTH)
PARTITIONS 3 STORE IN(RMS_DATA,RMS_DATA,RMS_DATA);

 

3、List(列表)分区
List分区与range 分区和hash 分区都有类似之处,
该分区与range分区类似的是也需要你指定列的值,但这又不同与range分区的范围式列值,其分区值
必须明确指定。也不同与hash分区,通过明确指定分区值,你能控制记录存储在哪个分区。
它的分区列只能有一个,而不能像range 或者hash 分区那样同时指定多个列做为分区依赖列。
它的单个分区对应值可以是多个,你在分区时必须确定分区列可能存在的值,一旦插入的列值不在分区范围内,则插入/更新就会失败,因此通常建议使用list分区时,要创建一个default分区存储那些不在指定范围内的
记录,类似range 分区中的maxvalue分区。

--创建list分区事例
需要我们指定的有:
column:分区依赖列,注意:只能是一个;
partition:分区名称;
literal:分区对应值,注意:每个分区可以对应多个值;
tablespace_clause:分区的存储属性,例如所在表空间等属性(可为空),默认继承基表所在表空间的属性。
例:

CREATE TABLE RM_CUST_MONTH_LIST
(
  YEAR_MONTH        NUMBER(6) NOT NULL,
  ORG_CODE          VARCHAR2(8),
  REGIE_ORG_CODE    VARCHAR2(8),
  CUST_CODE         VARCHAR2(12),
  CUST_NAME         VARCHAR2(200),
  CUST_LICENCE_CODE VARCHAR2(18)
)
PARTITION BY LIST (YEAR_MONTH)(
PARTITION T_LIST_P1 VALUES (201201,201202,201203) TABLESPACE RMS_DATA,
PARTITION T_LIST_P2 VALUES (201204,201205,201206) TABLESPACE RMS_DATA,
PARTITION T_LIST_P3 VALUES (201207,201208,201209) TABLESPACE RMS_DATA,
PARTITION T_LIST_PD VALUES (DEFAULT) TABLESPACE RMS_DATA);

 

4、组合分区:Range-Hash,Range-List
如果某表按照某列分区之后,仍然较大,或者是一些其它的需求,还可以通过分区内再建子分区的方式将分区再分区,即组合分区的方式。要注意顺序,根分区只能是range 分区,子分区可以是hash 分区或list 分区。

(1)创建range-hash组合分区
需要我们指定的有:
column_list:分区依赖列(支持多个,中间以逗号分隔);
subpartition:子分区方式,有两处:
Subpartition_by_list:语法与list 分区完全相同,只不过把关键字partition 换成subpartition
Subpartition_by_hash:语法与hash 分区完全相同,只不过把关键字partition 换成subpartition
partition:分区名称;
range_partition_values_clause:与range 分区范围值的语法;
tablespace_clause:分区的存储属性,例如所在表空间等属性(可为空),默认继承基表所在表空间的属性。

例:  

CREATE TABLE RM_CUST_MONTH_RANGE_HASH(
  YEAR_MONTH        NUMBER(6) NOT NULL,
  ORG_CODE          VARCHAR2(8),
  REGIE_ORG_CODE    VARCHAR2(8),
  CUST_CODE         VARCHAR2(12),
  CUST_NAME         VARCHAR2(200),
  CUST_LICENCE_CODE VARCHAR2(18)
)
PARTITION BY RANGE(YEAR_MONTH) 
SUBPARTITION BY HASH(CUST_CODE)
SUBPARTITIONS 3 STORE IN (RMS_DATA, RMS_DATA, RMS_DATA)(
PARTITION YEAR_MONTH_1201 VALUES LESS THAN(201201) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_1301 VALUES LESS THAN(201301) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_1401 VALUES LESS THAN(201401) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_MAXVALUE VALUES LESS THAN(MAXVALUE) TABLESPACE RMS_DATA);

 

(2)创建range-list组合分区
例:

CREATE TABLE RM_CUST_MONTH_RANGE_LIST(
  YEAR_MONTH        NUMBER(6) NOT NULL,
  ORG_CODE          VARCHAR2(8),
  REGIE_ORG_CODE    VARCHAR2(8),
  CUST_CODE         VARCHAR2(12),
  CUST_NAME         VARCHAR2(200),
  CUST_LICENCE_CODE VARCHAR2(18)
)
PARTITION BY RANGE(YEAR_MONTH) 
SUBPARTITION BY LIST(ORG_CODE)
SUBPARTITION TEMPLATE (
SUBPARTITION T_LIST_P1 VALUES ('13500200') TABLESPACE RMS_DATA,
SUBPARTITION T_LIST_P2 VALUES ('13500221') TABLESPACE RMS_DATA,
SUBPARTITION T_LIST_P3 VALUES ('13500222') TABLESPACE RMS_DATA)(
PARTITION YEAR_MONTH_1201 VALUES LESS THAN(201201) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_1301 VALUES LESS THAN(201301) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_1401 VALUES LESS THAN(201401) TABLESPACE RMS_DATA,
PARTITION YEAR_MONTH_MAXVALUE VALUES LESS THAN(MAXVALUE) TABLESPACE RMS_DATA);

 

5、分区信息的查询
--查询记录分区的表的信息(USER_PART_TABLES表)

SELECT TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT
  FROM USER_PART_TABLES
 WHERE TABLE_NAME = 'RM_CUST_MONTH';

 

--查询记录表的分区的信息(USER_TAB_PARTITIONS表)

SELECT PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
 FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME='RM_CUST_MONTH'
 ORDER BY PARTITION_POSITION;

 

6、表分区的相关操作
(1)增加表分区(add partition)
需要注意对于像list,range 这种存在范围值的分区,所要增加的分区值必须要大于当前分区中的最大值。
如果当前存在maxvalue 或default 的分区,add partition 会报错,这种情况只能使用split,
hash 分区则无此限制。

--增加表分区事例
例:

ALTER TABLE RM_CUST_MONTH_RANGE 
ADD PARTITION YEAR_MONTH_1207 VALUES LESS THAN(201207);

 

(2)删除表分区(drop partition)
删除表分区包含两种操作,分别是:
 删除分区:alter table [tbname] drop partition [ptname];
 删除子分区:alter table [tbname] drop subpartition [ptname];
除hash 分区和hash 子分区外,其它的分区格式都可以支持这项操作。
--删除表分区事例
例:

ALTER TABLE RM_CUST_MONTH_RANGE DROP PARTITION YEAR_MONTH_1207;

 

(3)重命名表分区(Rename Partition)  

ALTER TABLE RM_CUST_MONTH_RANGE RENAME PARTITION YEAR_MONTH_1207 TO YEAR_MONTH_1207_NEW;

 

(4)合并表分区(Merge Partitions)
合并两个分区成一个,适用于除hash 之外的其它所有分区形式。
语法:

alter table tbname merge partitions/subpartitions pt1,pt2 into partition/subpartition pt3;

 

同样也支持update indexes 子句以避免单独执行造成索引失效的问题。
需要注意一点,要合并的两个分区必须是连续的,这点是由分区本身的特性所决定的。
--合并表分区事例:

ALTER TABLE  RM_CUST_MONTH_RANGE MERGE PARTITIONS
YEAR_MONTH_1205,YEAR_MONTH_1206 INTO PARTITION YEAR_MONTH_120506;

 

(5)拆分表分区(split Partitions)
通常用于发现某个分区过大,就可以通过这种方式将该分区分解成多个小分区,最常用到的
是split maxvalue/default 的分区。该命令的语法针对不同分区会有不同的形式,
range分区

alter table tbname split partition ptname at (value) into 
(partition newpt1 tbs_clause,partition newpt2 tbs_clause);

 

list分区 :

alter table tbname split partition ptname values (v1,v2...vn) 
into (partition newpt1 tbs_clause,partition newpt2 tbs_clause);

 

上述两项,如果是操作子分区,则将partition 关键字换成subpartition 即可。
旧分区中符合新定义值的记录会存储到指定的第一个分区中,其它的记录存储到第二个分区。
--RANGE拆分表分区事例:
例:

ALTER TABLE RM_CUST_MONTH_RANGE SPLIT PARTITION YEAR_MONTH_MAXVALUE
AT (201206) INTO (PARTITION YEAR_MONTH_1206 TABLESPACE RMS_DATA,
              PARTITION  YEAR_MONTH_MAXVALUE   TABLESPACE RMS_DATA);

ALTER TABLE RM_CUST_MONTH_RANGE SPLIT PARTITION YEAR_MONTH_MAXVALUE 
AT (201207) INTO (PARTITION YEAR_MONTH_1207 TABLESPACE RMS_DATA,
              PARTITION  YEAR_MONTH_MAXVALUE   TABLESPACE RMS_DATA);

 

--LIST拆分表分区事例:

 
ALTER TABLE RM_CUST_MONTH_LIST SPLIT PARTITION T_LIST_PD
VALUES(201210,201211,201212)
INTO (PARTITION T_LIST_P4 TABLESPACE RMS_DATA,PARTITION T_LIST_PD);

ALTER TABLE RM_CUST_MONTH_LIST SPLIT PARTITION T_LIST_PD
VALUES(201301,201302,201303)
INTO (PARTITION T_LIST_P5 TABLESPACE RMS_DATA,PARTITION T_LIST_PD);

 

三.查看表分区信息

    1.DBA查看表分区

select * from dba_tab_partitions

    2.USER查看表分区

select * from user_tab_partitions

    3.DBA查看表分区类型

select * from dba_part_tables

    4.USER查看表分区类型 

select * from user_part_tables

    5.查询分区表信息

Sql代码 复制代码 收藏代码
  1. select * from table partition(part_01)  

二、分区索引
对于分区索引,需要区分创建的是全局索引,或本地索引:
1、 全局索引(global index)
即可以分区,也可以不分区。即可以建range 分区,也可以建hash 分区,
即可建于分区表,又可创建于非分区表上,就是说,全局索引是完全独立的,因此它也需要我们更多的维护操作。

(1)全局索引的特点:
1. 全局索引的分区键和分区数和表的分区键和分区数可能都不相同,表和全局索引的分区机制不一样。
2.全局索引可以分区,也可以是不分区索引,全局索引必须是前缀索引,即全局索引的索引列必须是以索引
分区键作为其前几列。
3.全局分区索引的索引条目可能指向若干个分区,因此,对于全局分区索引,即使只动,截断一个分区中
的数据,都需要rebulid若干个分区甚至是整个索引。
4. 全局索引多应用于oltp系统中。
5.全局分区索引只按范围或者散列hash分区,hash分区是10g以后才支持。
6. oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步
更新全局分区索引,用消耗一定资源来换取高度的可用性。
7. 表用a列作分区,索引用b做局部分区索引,若where条件中用b来查询,那么oracle会扫描所有的
表和索引的分区,成本会比分区更高,此时可以考虑用b做全局分区索引 。

(2)创建全局索引range分区事例
例:

CREATE INDEX IDX_RM_CUST_RANGE_ID ON RM_CUST_MONTH_TEST(YEAR_MONTH)
GLOBAL PARTITION BY RANGE(YEAR_MONTH)(
PARTITION I_RANGE_P1 VALUES LESS THAN (201201) TABLESPACE RMS_DATA,
PARTITION I_RANGE_P2 VALUES LESS THAN (201202) TABLESPACE RMS_DATA,
PARTITION I_RANGE_P3 VALUES LESS THAN (201203) TABLESPACE RMS_DATA,
PARTITION I_RANGE_P4 VALUES LESS THAN (201204) TABLESPACE RMS_DATA,
PARTITION I_RANGE_P5 VALUES LESS THAN (201205) TABLESPACE RMS_DATA,
PARTITION I_RANGE_PMAX VALUES LESS THAN (MAXVALUE) TABLESPACE RMS_DATA);

 

创建global 索引的分区与创建表的分区语句格式完全相同,而且其分区形式与索引
所在表的分区形式没有关联关系。


2、本地索引(local index)
其分区形式与表的分区完全相同,依赖列相同,存储属性也相同。
对于本地索引,其索引分区的维护自动进行,就是说你add/drop/split/truncate 表的分区时,
本地索引会自动维护其索引分区。

(1)本地索引的特点:
1、局部索引一定是分区索引,分区键等同于表的分区键,分区数等同于表的分区说,一句话,局部索引的
分区机制和表的分区机制一样。
2、如果局部索引的索引列以分区键开头,则称为前缀局部索引。
3、如果局部索引的列不是以分区键开头,或者不包含分区键列,则称为非前缀索引。
4、前缀和非前缀索引都可以支持索引分区消除,前提是查询的条件中包含索引分区键。
5、局部索引只支持分区内的唯一性,无法支持表上的唯一性,因此如果要用局部索引去给表做唯一性约束,
则约束中必须要包括分区键列。
6、局部分区索引是对单个分区的,每个分区索引只指向一个表分区,全局索引则不然,一个分区索引能指向n个表分区,同时,一个表分区,也可能指向n个索引分区,对分区表中的某个分区做truncate
或者move,shrink等,可能会影响到n个全局索引分区,正因为这点,局部分区索引具有更高的可用性。
7、位图索引只能为局部分区索引。
8、局部索引多应用于数据仓库环境中。
(2)创建本地分区索引事例
(分区形式与表分区完全相同,依赖列也相同)

CREATE INDEX IDX_RM_CUST_RANGE_ID_LOCAL ON RM_CUST_MONTH_TEST(CUST_CODE) LOCAL;

 

3、分区索引的相关操作
(1)查询分区索引的信息
可以通过user_part_indexes、user_ind_partitions 两个数据字典:

SELECT INDEX_NAME, PARTITIONING_TYPE, PARTITION_COUNT,LOCALITY
  FROM USER_PART_INDEXES
 WHERE INDEX_NAME = 'IDX_RM_CUST_RANGE_ID'; 
 
 SELECT PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
   FROM USER_IND_PARTITIONS
  WHERE INDEX_NAME = 'IDX_RM_CUST_RANGE_ID'
  ORDER BY PARTITION_POSITION;

 

(2)删除分区索引

Sql代码 复制代码 收藏代码
  1. DROP INDEX IDX_RM_CUST_RANGE_ID_LOCAL;  
分享到:
评论

相关推荐

    BLOG_如何将一个普通表转换为分区表.pdf

    从提供的文件内容可以看出,本文主要介绍的是如何将一个非分区表转换为分区表,其中涉及了四种不同的方法,并且强调了每种方法的执行细节以及注意事项。 1. Export/Import方法 Export/Import方法是最传统的转换策略...

    oracle普通表转化为分区表的方法

    Oracle数据库中的分区表是一种高级的表组织形式,它将大表分成多个较小的部分,每个部分称为一个分区,以提高查询性能和管理效率。当表的大小超过2GB时,官方推荐使用分区表,因为它们在处理大量数据时具有显著优势...

    oracle 普通表转分区表方式

    Oracle 普通表转分区表方式 Oracle 分区表(Partitioned Table)是一种特殊的表结构,可以根据不同的条件将数据分割成多个独立的分区,以提高查询效率和减少存储空间。本文将详细介绍将 Oracle 普通表转换为分区表...

    PTDD分区表医生3.5

    《PTDD分区表医生3.5》是一款专为硬盘分区管理设计的专业软件,它主要用于修复和维护硬盘的MBR(主引导记录)和GPT(GUID分区表)。这款工具在IT行业中具有较高的实用价值,特别是在数据恢复和系统维护领域。 一、...

    Oracle堆表转换成分区表方案

    2. **创建分区表**:根据选定的分区规则创建新的分区表`tb_order_new`。同时,需要在新表中创建与原表相同的约束条件及权限设置。 3. **数据迁移**:将原表`tb_order`中的数据批量迁移到新创建的分区表`tb_order_new...

    分区表解释-深入浅出硬盘分区表

    硬盘分区表是计算机存储系统中的一个重要组成部分,它负责组织硬盘空间并将其划分为多个独立的区域,以便操作系统管理和存储数据。本文将详细解释硬盘分区表的位置、结构以及如何通过理解这些知识来修复受损的分区表...

    oracle数据表分区知识

    ### Oracle 数据表分区详解 #### 一、分区表理论知识 Oracle 提供了表分区功能以支持非常大型的数据库(VLDB)。...通过这种方式,可以平滑地将非分区表转换为分区表,从而提升数据库性能和管理效率。

    ORACLE19c中的混合分区表的使用.pdf

    在Oracle 19c数据库中,混合分区表是一种高级特性,它允许用户将内部分区(存储在数据库中)与外部分区(存储在操作系统文件中)相结合,以优化大规模数据管理和查询性能。混合分区表的设计目的是为了更好地处理大量...

    windows操作系统分区表

    在深入探讨Windows操作系统的分区表之前,我们首先要理解分区表的重要性。分区表是硬盘上的一个关键组成部分,它存储了关于硬盘如何被划分为不同逻辑区域的信息。这些逻辑区域即为分区,每个分区都有独立的文件系统...

    oracle分区表

    【Oracle分区表详解】 Oracle分区表是Oracle数据库中一种高级的数据组织形式,它将大型表的数据分布到多个逻辑部分,称为分区。这种技术主要用于管理和优化大规模数据存储,以提升查询性能、增强可用性和简化管理...

    Oracle分区表用法

    【Oracle分区表用法】 Oracle分区表是一种高级的数据库管理技术,它将大型表的数据分散存储在不同的物理区域,以提升查询效率和系统的整体性能。本文将详细介绍分区表的概念、作用、优缺点,以及各种类型的分区表...

    linux分区表修复工具

    除了基本的分区表修复,testdisk还支持其他高级功能,如创建新的分区、转换分区类型(例如从FAT转NTFS,或者从EXT3转EXT4)以及处理启动问题。然而,这些操作需要谨慎进行,因为错误的操作可能导致数据丢失。 总的...

    Oracle分区表详解

    ### Oracle 分区表详解 #### 一、Oracle 分区简介 Oracle 的分区技术是一种用于管理和优化超大型表和索引的有效手段。通过将一个大型的表或者索引分割成多个较小且可管理的部分,分区技术能够显著提升数据库的性能...

    MySQL分区表自动创建及删除存储过程

    MySQL分区表是一种优化大型数据表查询效率的技术,它将一个大表分成多个逻辑上相连但物理上独立的部分,每个部分称为一个分区。分区可以按照不同的策略进行,如范围、哈希、列表或复合分区。这样做有助于提高数据...

    SQL2008分区表的建立

    本文将详细介绍 SQL Server 中的分区表,包括分区表的概念、创建分区表、操作分区表、将普通表转换为分区表等内容。 一、分区表简介 分区表是 SQL Server 中的一种数据存储方式,它将大型表拆分成多个小的、独立的...

    FAT32文件分区表说明 FAT32文件分区表说明

    1. 分区表:每个硬盘都有一个主引导记录(Master Boot Record,MBR),其中包含了分区表信息。分区表由4个分区项组成,每个分区项占用16个字节,可以表示一个主分区或扩展分区。FAT32分区在此表中被定义。 2. 引导...

    MI2S小米2手机(32G)的分区表备份

    备份这些分区表是为了防止数据丢失或系统故障时能够恢复到正常状态,进行“救转”。 描述中的“分区表备份”是关键点,这意味着这个压缩包中包含了MI2S手机的分区信息。分区表通常是一个包含所有分区信息的文件,...

    ORACLE分区表的概念及操作

    然而,分区也有其缺点,如已存在的非分区表无法直接转换为分区表,需要使用Oracle提供的在线重定义功能。此外,分区会增加数据库的复杂性,可能需要更多的存储空间,并可能对某些类型的DML操作(如INSERT、UPDATE、...

    Oracle 表分区

    分区表则是对大数据量表的一种优化策略,将表的数据分散存储在不同的表空间中,每个分区对应一个特定的条件或范围,使得查询和维护操作能针对特定的分区进行,减少不必要的数据扫描。 2. **表分区的具体作用** - *...

Global site tag (gtag.js) - Google Analytics