oracle所有分区表分区,Oracle分区表及分区目录

Dear 丶 2023-01-18 11:23 58阅读 0赞

一、分区表

分区表的几种分类：

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、 全局索引(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)删除分区索引

DROP INDEX IDX\_RM\_CUST\_RANGE\_ID\_LOCAL;

oracle所有分区表分区,Oracle分区表及分区目录

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle所有分区表分区,Oracle分区表及分区目录

相关 oracle分区表

相关 Oracle分区表

相关 oracle表分区创建

相关 oracle分区表详解

相关 oracle 表分区

相关 oracle表分区详解

相关 Oracle表分区

相关 Oracle为分区表添加新分区

相关 Oracle锁及表分区