【三思笔记】 全面学习Oracle分区表及分区索引

2019年11月06日 阅读数:91
这篇文章主要向大家介绍【三思笔记】 全面学习Oracle分区表及分区索引,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

[三思笔记]全面学习Oracle分区表及分区索引html

2008-04-15web

关于分区表和分区索引(About PartitionedTables and Indexes)面试

对于 10gR2 而言,基本上能够分红几类:sql

v  Range(范围)分区数据库

v  Hash(哈希)分区微信

v  List(列表)分区网络

v  以及组合分区:Range-Hash,Range-Listoracle

对于表而言(常规意义上的堆组织表),上述分区形式均可以应用(甚至能够对某个分区指定 compress 属性),只不过度区依赖列不能是 lob,long 之类数据类型,每一个表的分区或子分区数的总数不能超过 1024K-1 个。less

对于索引组织表,只可以支持普通分区方式,不支持组合分区,常规表的限制对于索引组织表一样有效,除此以外呢,还有一些其实的限制,好比要求索引组织表的分区依赖列必须是主键才能够等。ide

注:本篇全部示例仅针对常规表,即堆组织表!

对于索引,须要区分建立的是全局索引,或本地索引:

l   全局索引(global index):便可以分区,也能够不分区。便可以建 range 分区,也能够建 hash 分区,便可建于分区表,又可建立于非分区表上,就是说,全局索引是彻底独立的,所以它也须要咱们更多的维护操做。

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

Oracle 建议若是单个表超过 2G 就最好对其进行分区,对于大表建立分区的好处是显而易见的,这里很少论述 why,而将重点放在 when 以及 how

WHEN

1、When 使用 Range 分区

Range 分区呢是应用范围比较广的表分区方式,它是以列的值的范围来作为分区的划分条件,将记录存放到列值所在的 range 分区中,好比按照时间划分,2008 1 季度的数据放到 a 分区,08 2 季度的数据放到 b 分区,所以在建立的时候呢,须要你指定基于的列,以及分区的范围值,若是某些记录暂没法预测范围,能够建立 maxvalue 分区,全部不在指定范围内的记录都会被存储到 maxvalue 所在分区中,而且支持指定多列作为依赖列,后面在讲 how 的时候会详细谈到。

2、When 使用 Hash 分区

一般呢,对于那些没法有效划分范围的表,可使用 hash 分区,这样对于提升性能仍是会有必定的帮助。 hash 分区会将表中的数据平均分配到你指定的几个分区中,列所在分区是依据分区列的 hash 值自动分配,所以你并不能控制也不知道哪条记录会被放到哪一个分区中,hash 分区也能够支持多个依赖列。

3、When 使用 List 分区

List 分区与 range 分区和 hash 分区都有相似之处,该分区与 range 分区相似的是也须要你指定列的值,但这又不一样与 range 分区的范围式列值---其分区值必须明确指定,也不一样与 hash 分区---经过明确指定分区值,你能控制记录存储在哪一个分区。它的分区列只能有一个,而不能像 range 或者 hash 分区那样同时指定多个列作为分区依赖列,不过呢,它的单个分区对应值能够是多个。你在分区时必须肯定分区列可能存在的值,一旦插入的列值不在分区范围内,则插入/更新就会失败,所以一般建议使用list 分区时,要建立一个default 分区存储那些不在指定范围内的记录,相似range 分区中的 maxvalue 分区。

4、When 使用组合分区

若是某表按照某列分区以后,仍然较大,或者是一些其它的需求,还能够经过分区内再建子分区的方式将分区再分区,即组合分区的方式。

组合分区呢在 10g 中有两种:range-hashrange-list。注意顺序哟,根分区只能是 range 分区,子分区能够是 hash 分区或 list 分区。

提示:11g 在组合分区功能这块有所加强,又推出了 range-range,list-range,list-list,list-hash,这就至关于除 hash 外三种分区方式的笛卡尔形式都有了。为何会没有 hash 作为根分区的组合分区形式呢,再仔细回味一下第二点,你必定可以想明白~~

HOW

1、如何建立

 
  clip_image002


若是想对某个表作分区,必须在建立表时就指定分区,咱们能够对一个包含分区的表中的分区作修改,但不能直接将一个未分区的表修改为分区表(起码在 10g 是不行的,固然你可能会说,能够经过在线重定义的方式,可是这不是直接哟,这也是借助临时表间接实现的)

建立表或索引的语法就不说了,你们确定比我还熟悉,而想在建表(索引)同时指定分区也很是容易,只须要把建立分区的子句放到";"前就行啦,同时须要注意表的 row movement 属性,它用来控制是否容许修改列值所形成的记录移动至其它分区存储,有 enable|disable 两种状态,默认是 disable row movement,当 disable 时,若是记录要被更新至其它分区,则更新语句会报错。

下面分别演示不一样分区方式的表和索引的建立:

1、建立 range 分区

语法以下,图:[range_partitioning.gif]

须要咱们指定的有:

l   column:分区依赖列(若是是多个,以逗号分隔);

l   partition:分区名称;

l   values less than:后跟分区范围值(若是依赖列有多个,范围对应值也应是多个,中间以逗号分隔);

l   tablespace_clause:分区的存储属性,例如所在表空间等属性(可为空),默认继承基表所在表空间的属性。

   建立一个标准的 range 分区表:

JSSWEB> create table t_partition_range (id number,name varchar2(50))

2    partition by range(id)(

3    partition t_range_p1 values less than (10) tablespace tbspart01,

4    partition t_range_p2 values less than (20) tablespace tbspart02, 5partition t_range_p3 values less than (30) tablespace tbspart03,

6       partition t_range_pmax values less than (maxvalue) tablespace tbspart04

7       );

表已建立。

要查询建立分区的信息,能够经过查询 user_part_tables,user_tab_partitions 两个数据字典(索引分区、组织分区等信息也有对应的数据字典,后续示例会逐步说起)。 user_part_tables:记录分区的表的信息; user_tab_partitions:记录表的分区的信息。

例如:

JSSWEB> select table_name,partitioning_type,partition_count

2              From user_part_tables where table_name='T_PARTITION_RANGE';

TABLE_NAME                                     PARTITI PARTITION_COUNT

------------------------------ ------- ---------------

T_PARTITION_RANGE                         RANGE                              4

JSSWEB> select partition_name,high_value,tablespace_name

2      from user_tab_partitions where table_name='T_PARTITION_RANGE' 3order by partition_position;

PARTITION_NAME                              HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------- --------------------

T_RANGE_P1                                     10                TBSPART01

T_RANGE_P2                                     20                TBSPART02

T_RANGE_P3                                     30                TBSPART03

T_RANGE_PMAX                                  MAXVALUE     TBSPART04

   建立 global 索引 range 分区:

JSSWEB> create index idx_parti_range_id on t_partition_range(id)

2              global partition by range(id)(

3              partition i_range_p1 values less than (10) tablespace tbspart01,

4              partition i_range_p2 values less than (40) tablespace tbspart02,

5              partition i_range_pmax values less than (maxvalue) tablespace tbspart03);

索引已建立。

由上例能够看出,建立 global 索引的分区与建立表的分区语句格式彻底相同,并且其分区形式与索引

所在表的分区形式没有关联关系。

注意:咱们这里借助上面的表 t_partition_range 来演示建立 range 分区的 global 索引,并不表示 range

分区的表,只能建立 range 分区的 global 索引,只要你想,也能够为其建立 hash 分区的 global 索引。

查询索引的分区信息能够经过 user_part_indexesuser_ind_partitions 两个数据字典:

JSSWEB> select index_name, partitioning_type, partition_count

2              From user_part_indexes

3              where index_name = 'IDX_PARTI_RANGE_ID';

INDEX_NAME                                     PARTITI PARTITION_COUNT

------------------------------ ------- ---------------

IDX_PARTI_RANGE_ID                       RANGE                              3

JSSWEB> select partition_name, high_value, tablespace_name

2              from user_ind_partitions

3              where index_name = 'IDX_PARTI_RANGE_ID'

4              order by partition_position;

PARTITION_NAME                              HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------- --------------------

I_RANGE_P1                                     10                TBSPART01

I_RANGE_P2                                     40                TBSPART02

I_RANGE_PMAX                                  MAXVALUE     TBSPART03

   Local 分区索引的建立最简单,例如:仍然借助 t_partition_range 表来建立索引

--首先删除以前建立的 global 索引

JSSWEB> drop index IDX_PARTI_RANGE_ID;

索引已删除。

JSSWEB> create index IDX_PARTI_RANGE_ID on T_PARTITION_RANGE(id) local;

索引已建立。

查询相关数据字典:

JSSWEB> select index_name, partitioning_type, partition_count

2              From user_part_indexes

3              where index_name = 'IDX_PARTI_RANGE_ID';

INDEX_NAME                                     PARTITI PARTITION_COUNT

------------------------------ ------- ---------------

IDX_PARTI_RANGE_ID                       RANGE                              4

JSSWEB> select partition_name, high_value, tablespace_name

2              from user_ind_partitions

3              where index_name = 'IDX_PARTI_RANGE_ID'

4              order by partition_position;

PARTITION_NAME                              HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------- --------------------

T_RANGE_P1                                     10                TBSPART01

T_RANGE_P2                                     20                TBSPART02

T_RANGE_P3       30    TBSPART03 T_RANGE_PMAX  MAXVALUE  TBSPART04

能够看出,local 索引的分区彻底继承表的分区的属性,包括分区类型,分区的范围值即不需指定也不

能更改,这就是前面说的:local 索引的分区维护彻底依赖于其索引所在表。

不过呢分区名称,以及分区所在表空间等信息是能够自定义的,例如:

SQL> create index IDX_PART_RANGE_ID ON T_PARTITION_RANGE(id) local (

2       partition i_range_p1 tablespace tbspart01,

3       partition i_range_p2 tablespace tbspart01,

4       partition i_range_p3 tablespace tbspart02,

5       partition i_range_pmax tablespace tbspart02 6    );

索引已建立。

SQL> select index_name, partitioning_type, partition_count

2           From user_part_indexes

3           where index_name = 'IDX_PART_RANGE_ID';

INDEX_NAME                                 PARTITI PARTITION_COUNT

------------------------------ ------- ---------------

IDX_PART_RANGE_ID                     RANGE                              4

SQL> select partition_name, high_value, tablespace_name

2           from user_ind_partitions

3           where index_name = 'IDX_PART_RANGE_ID'

4           order by partition_position;

PARTITION_NAME    HIGH_VALUE          TABLESPACE_NAME

--------------- --------------- --------------------

I_RANGE_P1           10                         TBSPART01

I_RANGE_P2           20                         TBSPART01

I_RANGE_P3           30                         TBSPART02

I_RANGE_PMAX       MAXVALUE              TBSPART02

二、建立 hash 分区

语法以下:[:hash_partitioning.gif]

clip_image003

语法看起来比 range 复杂,其实使用起来比 range 更简单,这里须要咱们指定的有:

l  column:分区依赖列(支持多个,中间以逗号分隔);

l  partition:指定分区,有两种方式:

n 直接指定分区名,分区所在表空间等信息 n 只指定分区数量,和可供使用的表空间。

建立 hash 分区表

JSSWEB> create table t_partition_hash (id number,name varchar2(50))

2       partition by hash(id)(

3       partition t_hash_p1 tablespace tbspart01,

4       partition t_hash_p2 tablespace tbspart02,

5       partition t_hash_p3 tablespace tbspart03);

表已建立。

要实现一样效果,你还能够这样:

JSSWEB> create table t_partition_hash2 (id number,name varchar2(50))

2       partition by hash(id)

3       partitions 3 store in(tbspart01,tbspart02,tbspart03);

表已建立。

这就是上面说的,直接指定分区数量和可供使用的表空间。

提示:这里分区数量和可供使用的表空间数量之间没有直接对应关系。分区数并不必定要等于表空间数。

要查询表的分区信息,仍然是经过 user_part_tables,user_tab_partitions 两个数据字典,这里再也不举例。

Global 索引 hash 分区

Hash 分区索引的子句与 hash 分区表的建立子句彻底相同,例如:

JSSWEB> create index idx_part_hash_id on t_partition_hash(id)

2       global partition by hash(id)

3       partitions 3 store in(tbspart01,tbspart02,tbspart03);

索引已建立。

 
  clip_image005


查询索引的分区信息也还是经过 user_part_indexesuser_ind_partitions 两个数据字典,再也不举例。

建立 Local 索引

在前面学习 range 分区时,咱们已经对 Local 索引的特性作了很是清晰的概述,所以这里也再也不举例,若有疑问,建议再仔细复习 range 分区的相关示例,若是还有疑问,当面问我好了:)

综上:

Ø对于 global 索引分区而言,在 10g 中只能支持 range 分区和 hash 分区,所以后续示例中不会再说起。

Ø对于 local 索引分区而言,其分区形式彻底依赖于索引所在表的分区形式,无论从建立语法仍是理解难度均无技术含量,所以后续也再也不提供示例。

Ø注意,在建立索引时若是不显式指定 global local,则默认是 global

Ø注意,在建立 global 索引时若是不显式指定分区子句,则默认不分区(废话)

三、建立 list 分区

建立语法以下:[图:list_partitioning.gif]

须要咱们指定的有:

l   column:分区依赖列,注意:只能是一个;

l   partition:分区名称;

l   literal:分区对应值,注意:每一个分区能够对应多个值;

l   tablespace_clause:分区的存储属性,例如所在表空间等属性(可为空),默认继承基表所在表空间的属性。

建立 list 分区表示例:

JSSWEB> create table t_partition_list (id number,name varchar2(50))

2       partition by list(id)(

3       partition t_list_p1 values (1,2,3,4,5,6,7,8,9) tablespace tbspart01,

4       partition t_list_p2 values (10,11,12,13,14,15,16,17,18,19) tablespace tbspart02,

5       partition t_list_p3 values (20,21,22,23,24,25,26,27,28,29) tablespace tbspart03, 6 partition t_list_pd values (default) tablespace tbspart04);

表已建立。

上例可以实现与前面 range 分区示例相同的效果,固然针对本示例而言,list 分区显然很差用啊~~~

四、建立 range-hash 组合分区

语法以下:图[composite_partitioning.gif]

clip_image006

须要咱们指定的有:

l  column_list:分区依赖列(支持多个,中间以逗号分隔);

l  subpartition:子分区方式,有两处:

n  Subpartition_by_list:语法与 list 分区彻底相同,只不过把关键字 partition 换成 subpartition

n  Subpartition_by_hash:语法与 hash 分区彻底相同,只不过把关键字 partition 换成 subpartition

l   partition:分区名称;

l   range_partition_values_clause:range 分区范围值的语法;

l   tablespace_clause:分区的存储属性,例如所在表空间等属性(可为空),默认继承基表所在表空间的属性。

组合分区相对于普通分区,语法上稍稍复杂了一些,但也正因如此,其子分区的建立能够很是灵活,

下面分别举几个例子(注:仅示例,并不是穷举全部形式)

   为全部分区各建立 4 hash 子分区

 

JSSWEB> create table t_partition_rh (id number,name varchar2(50))

2       partition by range(id) subpartition by hash(name)

3       subpartitions 4 store in (tbspart01, tbspart02, tbspart03,tbspart04)(

4       partition t_r_p1 values less than (10) tablespace tbspart01,

5       partition t_r_p2 values less than (20) tablespace tbspart02,

6       partition t_r_p3 values less than (30) tablespace tbspart03,

7       partition t_r_pd values less than (maxvalue) tablespace tbspart04);

表已建立。

JSSWEB> select partitioning_type,subpartitioning_type,partition_count,def_subpartition_count

              2   From user_part_tables where table_name='T_PARTITION_RH';

PARTITI SUBPART PARTITION_COUNT DEF_SUBPARTITION_COUNT

------- ------- --------------- ----------------------

         RANGE     HASH                                4                                       4

JSSWEB> select partition_name,subpartition_count,high_value

              2   from user_tab_partitions where table_name='T_PARTITION_RH';

         PARTITION_NAME    SUBPARTITION_COUNT HIGH_VALUE

--------------- ------------------ ----------

          T_R_P2                                               4 20

          T_R_P3                                               4 30

         T_R_PD                                               4 MAXVALUE

          T_R_P1                                               4 10

JSSWEB> select partition_name,subpartition_name,tablespace_name

              2   from user_tab_subpartitions where table_name='T_PARTITION_RH';

         PARTITION_NAME    SUBPARTITION_NAME                        TABLESPACE_NAME

--------------- ------------------------------ --------------------

          T_R_P2                 SYS_SUBP140                                   TBSPART02

          T_R_P2                 SYS_SUBP139                                   TBSPART02

          T_R_P2                 SYS_SUBP138                                   TBSPART02

          T_R_P2                 SYS_SUBP137                                   TBSPART02

          T_R_P3                 SYS_SUBP144                                   TBSPART03

          T_R_P3                 SYS_SUBP143                                   TBSPART03

          T_R_P3                 SYS_SUBP142                                   TBSPART03

          T_R_P3                 SYS_SUBP141                                   TBSPART03

         T_R_PD                  SYS_SUBP148                                   TBSPART04

         T_R_PD                  SYS_SUBP147                                   TBSPART04

         T_R_PD                  SYS_SUBP146                                   TBSPART04

         T_R_PD                  SYS_SUBP145                                   TBSPART04

T_R_P1

SYS_SUBP133

TBSPART01

T_R_P1

SYS_SUBP136

TBSPART01

T_R_P1

SYS_SUBP135

TBSPART01

T_R_P1

已选择 16 行。

SYS_SUBP134

TBSPART01

这里咱们要学到一个新的数据字典:user_tab_subpartitions,用于查询表的子分区信息。

   对某个分区建立 hash 子分区

JSSWEB> create table t_partition_rh (id number,name varchar2(50))

2           partition by range(id) subpartition by hash(name)(

3           partition t_r_p1 values less than (10) tablespace tbspart01,

4           partition t_r_p2 values less than (20) tablespace tbspart02,

5           partition t_r_p3 values less than (30) tablespace tbspart03

6           (subpartition t_r_p3_h1 tablespace tbspart01,

7           subpartition t_r_p3_h2 tablespace tbspart02,

8           subpartition t_r_p3_h3 tablespace tbspart03),

9           partition t_r_pd values less than (maxvalue) tablespace tbspart04);

表已建立。

JSSWEB> select partitioning_type,subpartitioning_type,partition_count,def_subpartition_count

2       From user_part_tables where table_name='T_PARTITION_RH';

PARTITI SUBPART PARTITION_COUNT DEF_SUBPARTITION_COUNT

------- ------- --------------- ----------------------

RANGE      HASH                                4                                       1

JSSWEB> select partition_name,subpartition_count,high_value

2       from user_tab_partitions where table_name='T_PARTITION_RH';

PARTITION_NAME    SUBPARTITION_COUNT HIGH_VALUE

--------------- ------------------ ----------

T_R_P1                                               1 10

T_R_P2                                               1 20

T_R_P3                                               3 30

T_R_PD                                                1 MAXVALUE

JSSWEB> select partition_name,subpartition_name,tablespace_name

2       from user_tab_subpartitions where table_name='T_PARTITION_RH';

PARTITION_NAME    SUBPARTITION_NAME                         TABLESPACE_NAME

--------------- ------------------------------ --------------------


T_R_P1

SYS_SUBP149

TBSPART01

T_R_P2

SYS_SUBP150

TBSPART02

T_R_P3

T_R_P3_H3

TBSPART03

T_R_P3

T_R_P3_H2

TBSPART02

T_R_P3

T_R_P3_H1

TBSPART01

T_R_PD

已选择 6 行。

SYS_SUBP151

TBSPART04

固然,还能够给各个分区指定不一样的子分区

JSSWEB> create table t_partition_rh (id number,name varchar2(50))

2              partition by range(id) subpartition by hash(name)(

3              partition t_r_p1 values less than (10) tablespace tbspart01,

4              partition t_r_p2 values less than (20) tablespace tbspart02

5              (subpartition t_r_p2_h1 tablespace tbspart01,

6              subpartition t_r_p2_h2 tablespace tbspart02),

7              partition t_r_p3 values less than (30) tablespace tbspart03

8              subpartitions 3 store in (tbspart01,tbspart02,tbspart03),

9              partition t_r_pd values less than (maxvalue) tablespace tbspart04

10          (subpartition t_r_p3_h1 tablespace tbspart01,

11          subpartition t_r_p3_h2 tablespace tbspart02,

12          subpartition t_r_p3_h3 tablespace tbspart03) 13       );

表已建立。

JSSWEB> select partitioning_type,subpartitioning_type,partition_count,def_subpartition_count

2     From user_part_tables where table_name='T_PARTITION_RH';

PARTITI SUBPART PARTITION_COUNT DEF_SUBPARTITION_COUNT

------- ------- --------------- ----------------------

RANGE    HASH                                4                                       1

JSSWEB> select partition_name,subpartition_count,high_value

2     from user_tab_partitions where table_name='T_PARTITION_RH';

PARTITION_NAME  SUBPARTITION_COUNT HIGH_VALUE

--------------- ------------------ ----------

T_R_P1                                              1 10

          T_R_P2                                               2 20

          T_R_P3                                               3 30

         T_R_PD                                               3 MAXVALUE

JSSWEB> select partition_name,subpartition_name,tablespace_name

2       from user_tab_subpartitions where table_name='T_PARTITION_RH';

PARTITION_NAME    SUBPARTITION_NAME                        TABLESPACE_NAME

--------------- ------------------------------ --------------------

T_R_P1                  SYS_SUBP152                                   TBSPART01

T_R_P2                  T_R_P2_H2                                      TBSPART02

T_R_P2                  T_R_P2_H1                                      TBSPART01

T_R_P3                  SYS_SUBP155                                   TBSPART03

T_R_P3                  SYS_SUBP154                                   TBSPART02

T_R_P3                  SYS_SUBP153                                   TBSPART01

T_R_PD                  T_R_P3_H3                                      TBSPART03

T_R_PD                  T_R_P3_H2                                      TBSPART02

T_R_PD                  T_R_P3_H1                                      TBSPART01

已选择 9 行。

提示:由上两例能够看出,未显式指定子分区的分区,系统会自动建立一个子分区。

分区模板的应用

oracle 还提供了一种称为分区模板的功能,在指定子分区信赖列以后,制订子分区的存储模板,各个

分区即会按照子分区模式建立子分区,例如:

JSSWEB> create table t_partition_rh (id number,name varchar2(50))

2           partition by range(id) subpartition by hash(name)

3           subpartition template (

4           subpartition h1 tablespace tbspart01,

5           subpartition h2 tablespace tbspart02,

6           subpartition h3 tablespace tbspart03,

7           subpartition h4 tablespace tbspart04)(

8           partition t_r_p1 values less than (10) tablespace tbspart01,

9           partition t_r_p2 values less than (20) tablespace tbspart02,

10       partition t_r_p3 values less than (30) tablespace tbspart03,

11       partition t_r_pd values less than (maxvalue) tablespace tbspart04);

表已建立。

JSSWEB> select partition_name,subpartition_name,tablespace_name

2   from user_tab_subpartitions where table_name='T_PARTITION_RH';

PARTITION_NAMESUBPARTITION_NAME                        TABLESPACE_NAME

 

--------------- ------------------------------ --------------------

 

 

 

T_R_P1                  T_R_P1_H4

TBSPART01

 

 

T_R_P1                  T_R_P1_H3

TBSPART01

 

 

T_R_P1                  T_R_P1_H2

TBSPART01

 

 

T_R_P1                  T_R_P1_H1

TBSPART01

 

T_R_P2

T_R_P2_H4

TBSPART02

T_R_P2

T_R_P2_H3

TBSPART02

T_R_P2

T_R_P2_H2

TBSPART02

T_R_P2

T_R_P2_H1

TBSPART02

T_R_P3

T_R_P3_H4

TBSPART03

T_R_P3

T_R_P3_H3

TBSPART03

T_R_P3

T_R_P3_H2

TBSPART03

T_R_P3

T_R_P3_H1

TBSPART03

T_R_PD

T_R_PD_H4

TBSPART04

T_R_PD

T_R_PD_H3

TBSPART04

T_R_PD

T_R_PD_H2

TBSPART04

T_R_PD

已选择 16 行。

T_R_PD_H1

TBSPART04

         

五、建立 range-list 组合分区

Range-list 组合分区的建立与 range-hash 极为类似,只是子分区为 list 分区,固然一样也能够应用分区

模板,下面也举一个示例:

JSSWEB> create table t_partition_rl (id number,name varchar2(50))

2           partition by range(id) subpartition by list(name)

3           subpartition template (

4           subpartition l1 values ('aa') tablespace tbspart01,

5           subpartition l2 values ('bb') tablespace tbspart02,

6           subpartition l3 values ('cc') tablespace tbspart03,

7           subpartition l4 values ('dd') tablespace tbspart04)(

8           partition t_r_p1 values less than (10) tablespace tbspart01,

9           partition t_r_p2 values less than (20) tablespace tbspart02,

10       partition t_r_p3 values less than (30) tablespace tbspart03,

11       partition t_r_pd values less than (maxvalue) tablespace tbspart04);

表已建立。

JSSWEB> select partition_name,subpartition_name,tablespace_name

2   from user_tab_subpartitions where table_name='T_PARTITION_RL';

PARTITION_NAMESUBPARTITION_NAME                        TABLESPACE_NAME

--------------- ------------------------------ --------------------

T_R_P1

T_R_P1_L4

TBSPART01

T_R_P1

T_R_P1_L3

TBSPART01

T_R_P1

T_R_P1_L2

TBSPART01

T_R_P1

T_R_P1_L1

TBSPART01

T_R_P2

T_R_P2_L4

TBSPART02


T_R_P2

T_R_P2_L3

TBSPART02

T_R_P2

T_R_P2_L2

TBSPART02

T_R_P2

T_R_P2_L1

TBSPART02

T_R_P3

T_R_P3_L4

TBSPART03

T_R_P3

T_R_P3_L3

TBSPART03

T_R_P3

T_R_P3_L2

TBSPART03

T_R_P3

T_R_P3_L1

TBSPART03

T_R_PD

T_R_PD_L4

TBSPART04

T_R_PD

T_R_PD_L3

TBSPART04

T_R_PD

T_R_PD_L2

TBSPART04

T_R_PD

已选择 16 行。

T_R_PD_L1

TBSPART04

其它方式的建立对于 range-list 一样好使,这里再也不举例,若有不明,请自学复习前章 range_hash 组合

分区。

对于复合分区的 local 索引,咱们也举一个示例,查看其分区状况:

SQL> create index idx_part_rl_id on t_partition_rl(id) local;

索引已建立。

又能够学几个数据字典:user_part_indexesuser_ind_partitions 前面已经认识了,user_ind_subpartitions 用来查询索引的子分区信息。

SQL> select table_name,partitioning_type,

2                                partition_count,def_subpartition_count

3                                from user_part_indexes

4                                where index_name = 'IDX_PART_RL_ID';

TABLE_NAME                                     PARTITI PARTITION_COUNT DEF_SUBPARTITION_COUNT

------------------------------ ------- --------------- ----------------------

T_PARTITION_RL                              RANGE                              4                                       4

SQL> select partition_name, subpartition_count, high_value

2              from user_ind_partitions

3              where index_name = 'IDX_PART_RL_ID';

PARTITION_NAME    SUBPARTITION_COUNT HIGH_VALUE

--------------- ------------------ ---------------

T_R_P1                                                4 10

T_R_P2                                                4 20

T_R_P3                                                4 30

T_R_PD                                                4 MAXVALUE

SQL> select partition_name, subpartition_name, high_value, tablespace_name

2              from user_ind_subpartitions

3              where index_name = 'IDX_PART_RL_ID';

         PARTITION_NAME    SUBPARTITION_NAME                        HIGH_VALUE

TABLESPACE_NAME

--------------- ------------------------------ --------------- --------------------

          T_R_P1                 T_R_P1_L1                                       'aa'                     TBSPART01

T_R_P1  T_R_P1_L2    'bb'  TBSPART01 T_R_P1    T_R_P1_L3    'cc'   TBSPART01

T_R_P1  T_R_P1_L4    'dd'  TBSPART01 T_R_P2    T_R_P2_L1    'aa'   TBSPART02

T_R_P2  T_R_P2_L2    'bb'  TBSPART02 T_R_P2    T_R_P2_L3    'cc'   TBSPART02

T_R_P2  T_R_P2_L4    'dd'  TBSPART02 T_R_P3    T_R_P3_L1    'aa'   TBSPART03

T_R_P3  T_R_P3_L2    'bb'  TBSPART03 T_R_P3    T_R_P3_L3    'cc'   TBSPART03

          T_R_P3                 T_R_P3_L4                                       'dd'                     TBSPART03

         T_R_PD                  T_R_PD_L1                                       'aa'                     TBSPART04

T_R_PD  T_R_PD_L2   'bb'  TBSPART04 T_R_PD   T_R_PD_L3   'cc'   TBSPART04

         T_R_PD                  T_R_PD_L4                                       'dd'                     TBSPART04

已选择 16 行。

仍是与表的分区格式同样,无论是普通分区仍是复合分区,local 索引都没啥自主权啊。

六、公共准则

一、         若是选择的分区不能确保各分区内记录量的基本平均,则这种分区方式有多是不恰当的。

好比对于 range 分区,假设分了 10 个分区,而其中一个分区中的记录数占总记录数的 90%,其它 9 个分区只占总记录数的 10%,则这个分区方式就起不到数据平衡的做用。固然,若是你的目的并非为了平衡,只是为了区分数据,ok,对于这种状况,我想说的是,你务必要意识到存在这个问题。

二、         对于分区的表或索引,其所涉及的全部分区,其块大小必须一致。

最后,建议对于上面建立的表或建表脚本妥善保存并记忆,后面咱们须要频繁用到,后续示例将均主要依赖前文中建立的表进行:)

2、如何管理

对于分区的表的操做不少,其中某些操做仅针对某些分区有效,为了不在演示过程当中浪费过多口水标注哪些操做适用于哪些分区,我们先在这儿列个表,哪一个操做适用于哪一种分区格式具体能够先参考下面这个表格:

分区表

Range

List

Hash

Range-Hash

Range-List

是否带来IO操做

增长分区

(add partition)

支持

支持

支持

支持

支持

hash类型外,均不变带来大量IO

收缩分区

(coalesce partitions)

/

/

支持

分区:/

子分区:支持

/

删除分区

(drop partition)

支持

支持

/

分区:支持子分区:/

支持

交换分区

(exchange partition)

支持

支持

支持

支持

支持

合并分区

(merge partition)

支持

支持

/

分区:支持子分区:/

支持

修改默认属性

(modify default attributes)

支持

支持

支持

支持

支持

修改分区当前属性

(modify partition)

支持

支持

支持

支持

支持

List分区增长值

(modify partition add values)

/

支持

/

/

分区:/

子分区:支持

List分区删除值

(modify partition drop values)

/

支持

/

/

分区:/

子分区:支持

单纯删除操做无,但可能为了实现成功删除,以前的准备操做会带来必定量的IO

修改子分区模板

(set subpartition template)

/

/

/

支持

支持

移动分区

(move partition)

支持

支持

支持

分区:支持子分区:/

分区:支持子分区:/

重命名分区

(rename partition)

支持

支持

支持

支持

支持

拆分分区

(split partition)

支持

支持

/

分区:支持子分区:/

支持

截断分区

(truncate partition)

支持

支持

支持

支持

支持

注:上述 IO 列的评估创建在假设分区中均存在必定量数据,并忽略修改数据字典可能触发的 IO,忽略形成的索引的重编译带来的 IO

分区索引的操做也有一张表黑黑,以下:

分区索引

索引类型

Range

List

Hash

组合分区

是否带来IO操做

增长分区

(add partition)

全局

/

/

支持

/

本地

/

/

/

/

 

删除分区

(drop partition)

全局

支持

/

/

/

本地

/

/

/

/

 

修改默认属性

(modify default attributes)

全局

支持

/

/

/

本地

支持

支持

支持

支持

修改分区当前属性

全局

支持

/

/

/

(modify partition)

本地

支持

支持

支持

支持

重编译分区

(rebuild partition)

全局

支持

/

/

/

本地

支持

支持

支持

支持

重命名分区

(rename partition)

全局

支持

/

/

/

本地

支持

支持

支持

支持

拆分分区

(split partition)

全局

支持

/

/

/

本地

/

/

/

/

 

另外 local 索引前头咱们屡次提到了,其维护会在 oracle 操做表分区的时候自动进行,须要注意的是 global

索引,当 global 索引所在表执行 alter table 涉及下列操做时,会导至该索引失效:

Ø  ADD PARTITION | SUBPARTITION

Ø  COALESCE PARTITION | SUBPARTITION

Ø  DROP PARTITION | SUBPARTITION

Ø  EXCHANGE PARTITION | SUBPARTITION

Ø  MERGE PARTITION | SUBPARTITION

Ø  MOVE PARTITION | SUBPARTITION

Ø  SPLIT PARTITION | SUBPARTITION

Ø  TRUNCATE PARTITION | SUBPARTITION

所以,建议用户在执行上述操做 sql 语句后附加 update indexes 子句,oracle 即会自动维护全局索引,固然,须要注意这中间有一个平衡,你要平衡操做 ddl 的时间和重建索引哪一个时间更少,以决定是否须要附加 update indexes 子句。

分区表的管理

1、增长表分区(add partition) 增长表分区适应于全部的分区形式,其语法是 alter table tbname add partition .....

可是,须要注意对于像 list,range 这种存在范围值的分区,所要增长的分区值必需要大于当前分区中的最大值(若是当前存在 maxvalue default 的分区,add partition 会报错,这种状况只能使用 split,后面会讲到),hash 分区则无此限制。

例如:

JSSWEB> create table t_partition_range (id number,name varchar2(50))

2           partition by range(id)(

3           partition t_range_p1 values less than (10) tablespace tbspart01,

4           partition t_range_p2 values less than (20) tablespace tbspart02,

5           partition t_range_p3 values less than (30) tablespace tbspart03

6           );

表已建立。

JSSWEB> alter table t_partition_range

2   add partition t_range_p4 values less than(40);

表已更改。

Hash list 的语法与上相似,这里再也不举例。

注意:

一、    对于 hash 分区,当你执行 add partition 操做的时候,oracle 会自动选择一个分区,并从新分配部分记录到新建的分区,这也意味着有可能带来一些 IO 操做。

二、    执行 alter table 时未指定 update indexes 子句:若是是 range/list 分区,其 local 索引和 global 索引不会受影响;

若是是 hash 分区,新加分区及有数据移动的分区的 local 索引和 glocal 索引会被置为 unuseable,须要从新编译。

三、    复合分区彻底适用上述所述规则。

二、收缩表分区(coalesce partitions)

Coalesce partition 是个颇有意思的分区功能,仅能被应用于 hash 分区或复合分区的 hash 子分区,执行以后,会自动收缩当前的表分区,好比某表当前有 5 hash 分区,执行 alter table tbname coalesce partitions 后就变成 4 个,再执行一次就变成 3 个,再执行一次就变 2 个,再执行一次就...........就报错了:),对于已分区的表至少要有一个分区存在的嘛!

例如:

JSSWEB> select table_name,partition_name from user_tab_partitions

2         where table_name='T_PARTITION_HASH';

TABLE_NAME                                     PARTITION_NAME

------------------------------ ------------------------------

T_PARTITION_HASH                          T_HASH_P2

T_PARTITION_HASH                          T_HASH_P3

T_PARTITION_HASH                          T_HASH_P4

T_PARTITION_HASH                          T_HASH_P5

T_PARTITION_HASH                          T_HASH_P1

JSSWEB> alter table t_partition_hash coalesce partition;

表已更改。

JSSWEB> select table_name,partition_name from user_tab_partitions

2         where table_name='T_PARTITION_HASH';

TABLE_NAME                                     PARTITION_NAME

------------------------------ ------------------------------

T_PARTITION_HASH                          T_HASH_P2

T_PARTITION_HASH                          T_HASH_P3

T_PARTITION_HASH                          T_HASH_P4

T_PARTITION_HASH  T_HASH_P1 注意,收缩的只是分区,并不会影响到数据,可是视被收缩分区中数据的多少,收缩表分区也会涉及

IO 操做。

另外若是你在执行该语句时没有指定 update indexes 子句,收缩过程当中有数据改动的分区其 local 索引

glocal 索引都会失效,须要从新编译。

三、删除表分区(drop partition)

删除表分区包含两种操做,分别是:

Ø  删除分区:alter table [tbname] drop partition [ptname];

Ø  删除子分区:alter table [tbname] drop subpartition [ptname];

hash 分区和 hash 子分区外,其它的分区格式均可以支持这项操做。

例如,删除分区:

JSSWEB> select table_name,partition_name

2       from user_tab_partitions where table_name='T_PARTITION_LIST';

TABLE_NAME                                     PARTITION_NAME

------------------------------ ------------------------------

T_PARTITION_LIST                          T_LIST_P1

T_PARTITION_LIST                          T_LIST_P2

T_PARTITION_LIST                          T_LIST_P3

T_PARTITION_LIST                          T_LIST_PD

JSSWEB> alter table t_partition_list drop partition t_list_p2;

表已更改。

提示,drop partition 时,该分区内存储的数据也将同时删除,例如:

JSSWEB> insert into t_partition_list values (1,'a');

..........

--插入一批记录,分布于当前各个分区

..........

JSSWEB> commit;

提交完成。

JSSWEB> select *from t_partition_list;

ID NAME

---------- --------------------------------------------------

1  a

2  b

21  a

22  b

--单独查询 t_list_p3 分区,当前有数据

JSSWEB> select *from t_partition_list partition(t_list_p3);

ID NAME

---------- --------------------------------------------------

21  a

22  b

--删除 t_list_p3 分区,数据会被同时删除

JSSWEB> alter table t_partition_list drop partition t_list_p3;

表已更改。

JSSWEB> select *from t_partition_list partition(t_list_p3);

select *from t_partition_list partition(t_list_p3)

*

1 行出现错误:

ORA-02149: 指定的分区不存在

JSSWEB> select *from t_partition_list;

ID NAME

---------- --------------------------------------------------

1  a

2  b

因为是 ddl 操做,这种删除也会是很是迅速的,所以若是你确认某个分区的数据都要被删除,使用 drop partition 会比 delete 更加高效。若是你的本意是但愿删除掉指定的分区但保留数据,你应该使用 merge partition,后面也会讲到。

一样,若是你在执行该语句时没有指定 update indexes 子句,也会致使 glocal 索引的失效,至于 local 索引嘛,删除分区时对应的索引分区会被同时删除,但其它分区的 local 索引不会受到影响。

4、交换表分区(Exchange Partitions)

直白的说就是迁移数据。迁移数据的方式不少,为何要使用 exchange partition 的方式呢,表急,听三思慢慢道来。

Exchange partition 提供了一种方式,让你在表与表或分区与分区之间迁移数据,注意不是将表转换成分区或非分区的形式,而仅只是迁移表中数据(互相迁移),因为其号称是采用了更改数据字典的方式,所以效率最高(几乎不涉及 io 操做)Exchange partition 适用于全部分区格式,你能够将数据从分区表迁移到非分区表,也能够从非分区表迁移至分区表,或者从 hash partition range partition 诸如此类吧。

其语法很简单:alter table tbname1 exchange partition/subpartition ptname with table tbname2;

Exchange partition 迁移的方式也颇有意思,言语表达怕你们听不明白,下面直接经过示例来表达:借用前文中建立的空分区表:t_partition_range,并插入几条记录

JSSWEB> create table t_partition_range (id number,name varchar2(50))

2    partition by range(id)(

3    partition t_range_p1 values less than (10) tablespace tbspart01,

4    partition t_range_p2 values less than (20) tablespace tbspart02, 5partition t_range_p3 values less than (30) tablespace tbspart03,

6           partition t_range_pmax values less than (maxvalue) tablespace tbspart04

7           );

表已建立。

JSSWEB> insert into t_partition_range values (11,'a');

已建立 1 行。

JSSWEB> insert into t_partition_range values (12,'b');

已建立 1 行。

JSSWEB> insert into t_partition_range values (13,'c');

已建立 1 行。

JSSWEB> commit;

提交完成。

再建立一个非分区表,结构与 t_partition_range 相同

JSSWEB> create table t_partition_range_tmp (id number,name varchar2(50));

表已建立。

执行交换分区(咱们知道刚插入到 range 分区表的数据都在分区 t_range_p2 中,所以这里指定交换该分区)

JSSWEB> alter table t_partition_range exchange partition t_range_p2 2 with table t_partition_range_tmp;

表已更改。

看看效果如何:

JSSWEB> select * from t_partition_range partition(t_range_p2);

未选定行

JSSWEB> select * from t_partition_range_tmp;

ID NAME

---------- --------------------------------------------------

11  a

12  b

13  c

记录成功交换到未分区的表中。

咱们再执行一次 exchange partition 的命令,看看又会发生什么呢

JSSWEB> select *from t_partition_range partition(t_range_p2);

ID NAME

---------- --------------------------------------------------

11  a

12  b

13  c

JSSWEB> select *from t_partition_range_tmp;

未选定行

又交换回来了,有点儿意思。

再作个更加明确的测试,咱们往未分区的表中加入一些记录后再执行 exchange partition,看看会发生什

么呢:

JSSWEB> insert into t_partition_range_tmp values (15,'d');

已建立 1 行。

JSSWEB> insert into t_partition_range_tmp values (16,'e');

已建立 1 行。

JSSWEB> insert into t_partition_range_tmp values (17,'d');

已建立 1 行。

JSSWEB> alter table t_partition_range exchange partition t_range_p2 2 with table t_partition_range_tmp;

表已更改。

JSSWEB> select *from t_partition_range partition(t_range_p2);

ID NAME

---------- --------------------------------------------------

15  d

16  e

17  d

JSSWEB> select *from t_partition_range_tmp;

ID NAME

---------- --------------------------------------------------

11  a

12  b

13  c

这就是前面所说的,互相交换的意思~~

注意:

l  涉及交换的两表之间表结构必须一致,除非附加 with validation 子句;

l  若是是从非分区表向分区表作交换,非分区表中的数据必须符合分区表中指定分区的规则,除非附加 without validation 子句;

l  若是从分区表向分区表作交换,被交换的分区的数据必须符合分区规则,除非附加 without validation 子句;

l  Global 索引或涉及到数据改动了的 global 索引分区会被置为 unusable,除非附加 update indexes 子句。

提示:

一旦附加了 without validation 子句,则表示再也不验证数据有效性,所以指定该子句时务必慎重。

例如:

JSSWEB> insert into t_partition_range_tmp values (8,'g');

已建立 1 行。

JSSWEB> alter table t_partition_range exchange partition t_range_p2 2 with table t_partition_range_tmp without validation;

表已更改。

JSSWEB> select *from t_partition_range partition(t_range_p2);

ID NAME

---------- --------------------------------------------------

11  a

12  b

13  c

8 g

虽然新插入的记录并不符合 t_range_p2 分区的范围值,但指定了 without validation 后,数据仍然转换


成功。

5、合并表分区(Merge Partitions)

合并两个分区成一个,适用于除 hash 以外的其它全部分区形式(hash 分区有 coalesce partition 的嘛,前头刚刚讲过)

语法很简单:alter table tbname merge partitions/subpartitions pt1,pt2 into partition/subpartition pt3; 一样也支持 update indexes 子句以免单独执行形成索引失效的问题。

须要注意一点,要合并的两个分区必须是连续的,这点是由分区自己的特性所决定的,如例:

JSSWEB> alter table t_partition_range merge partitions t_range_p1,t_range_p2 2 into partition t_range_pnew;

表已更改。

JSSWEB> select table_name,partition_name,high_value from user_tab_partitions

2       where table_name='T_PARTITION_RANGE';

TABLE_NAME                                     PARTITION_NAME                              HIGH_VALUE

------------------------------ ------------------------------ ----------------

T_PARTITION_RANGE                        T_RANGE_P3                                     30

T_PARTITION_RANGE                         T_RANGE_PMAX                                  MAXVALUE

T_PARTITION_RANGE                         T_RANGE_PNEW                                 20

JSSWEB> select *from t_partition_range partition(t_range_pnew);

ID NAME

---------- --------------------------------------------------

11  a

12  b

13  c

8 g

可见,合并分区操做不会形成数据丢失,另外若是你想为新分区指定属性的话,在语句末尾处增长存储属性便可(若是不指定,则新分区默认继续表的存储属性)。例如:

JSSWEB> select partition_name,high_value,tablespace_name from user_tab_partitions 2 where table_name='T_PARTITION_LIST';

PARTITION_NAME       HIGH_VALUE                                      TABLESPACE_NAME

--------------------              -------------------------------------------------- ---------------

T_LIST_P1    1, 2, 3, 4, 5, 6, 7, 8, 9, 10       TBSPART01 T_LIST_P2      11, 12, 13, 14, 15, 16, 17, 18, 19, 20    TBSPART02

T_LIST_P3                     21, 22, 23, 24, 25, 26, 27, 28, 29, 30         TBSPART03

         T_LIST_PD                     default                                                 TBSPART04

JSSWEB> alter table t_partition_list merge partitions t_list_p2,t_list_p3 2 into partition t_list_p2 tablespace tbspart02;

表已更改。

JSSWEB> select partition_name,high_value,tablespace_name from user_tab_partitions 2  where table_name='T_PARTITION_LIST';

PARTITION_NAME     HIGH_VALUE                                                        TABLESPACE_NAME

--------------------            --------------------------------------------------                   ---------------

T_LIST_P1                     1, 2, 3, 4, 5, 6, 7, 8, 9, 10                                      TBSPART01

T_LIST_P2                     21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 11, 12, 13       TBSPART02

, 14, 15, 16, 17, 18, 19, 20

T_LIST_PD                     default                                                               TBSPART04

注意,merge 分区操做与 coalesce 分区操做同样,视被合并的分区数据量多少,均可能涉及到大量的 IO 操做。

其它合并组合分区的操做与上相似,若是要合并组合分区,注意关键字是 merge subpartitions,这里就

不作演示了。

6、修改 list 表分区--AddValues

从标题便可得知,此命令仅应用于 list 分区或 list 子分区,语法也很是简单:

Alter table tbname modify partition/subpartition ptname add values (v1,v2....vn); 举个例子:

JSSWEB> select partition_name,high_value from user_tab_partitions

2       where table_name='T_PARTITION_LIST';

PARTITION_NAME            HIGH_VALUE

-------------------- --------------------------------------------------

T_LIST_P1                     1, 2, 3, 4, 5, 6, 7, 8, 9, 10

T_LIST_P2                     21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 11, 12, 13

, 14, 15, 16, 17, 18, 19, 20

T_LIST_PD                     default

JSSWEB> alter table t_partition_list modify partition t_list_p1 add values (31,33);

表已更改。

JSSWEB> select partition_name,high_value from user_tab_partitions 2  where table_name='T_PARTITION_LIST';

         PARTITION_NAME            HIGH_VALUE

-------------------- --------------------------------------------------

          T_LIST_P1                     1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 31, 33

          T_LIST_P2                     21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 11, 12, 13

 

, 14, 15, 16, 17, 18, 19, 20

T_LIST_PD

default

惟一的限制是注意要添加的新 value 值不能存在于当前任何分区中,而且当前表也不能存在记录值为新值的记录,特别是当你建立了 default 分区的时候,有必要先检查一下当前表不存在要添加的值,否则命令执行会出错,例如:

JSSWEB> insert into t_partition_list values (32,'a');

已建立 1 行。

JSSWEB> alter table t_partition_list modify partition t_list_p1 add values (32); alter table t_partition_list modify partition t_list_p1 add values (32)

*

1 行出现错误:

ORA-14324: 所要添加的值已存在于 DEFAULT 分区之中

提示,增长新的列表值不会影响到表中原有的记录,所以不会对索引形成影响。

7、修改 list 表分区--Drop Values

与上相似,也是只能应用于 list 分区,不过功能相反,该命令是用来删除指定分区的 value 值,语法如

下:

Alter table tbname modify partition/subpartition ptname drop values (v1,v2....vn);

一样在删除 list 分区 value 列值的时候,也必须确认当前分区存在指定的 value 值,可是没有任何应用

该值的记录,有点儿饶是吧,脑壳多转几圈就行了。

举个例子:

JSSWEB> alter table t_partition_list modify partition t_list_p1 drop values (31);

表已更改。

成功执行了是吧,接着来看

JSSWEB> alter table t_partition_list modify partition t_list_p1 drop values (31); alter table t_partition_list modify partition t_list_p1 drop values (31)

*

1 行出现错误:

ORA-14313: 31 不在分区 T_LIST_P1

出错了吧,这是其中的一种错误情形,即前面说的,要确保当前分区中存在指定的 value 值,再往下看