ORACLE分区表的使用和管理

蔚落 2022-04-12 12:23 281阅读 0赞

**1创建和使用分区表**

Oracle分区表分为四类：范围分区表；列表分区表；哈希分区表；组合分区表

### 范围分区表 ###

创建一个按字段数据范围分区的表，分区置于指定的不同表空间中

示例代码：

\--为各个分区准备独立的表空间

create tablespace test\_space01 datafile‘d:/tbs01.dbf’ size 50m

create tablespace test\_space02 datafile‘d:/tbs02.dbf’ size 50m

create tablespace test\_space03 datafile‘d:/tbs03.dbf’ size 50m

create tablespace test\_space04 datafile‘d:/tbs04.dbf’ size 50m

\--创建分区表，

CREATE TABLE range\_example(

range\_key\_column DATE,

DATA VARCHAR2(20),

ID integer

)

PARTITION BY RANGE(range\_key\_column)

(

PARTITION part01 VALUES LESS THAN(TO\_DATE('2008-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACEtest\_space01,

PARTITION part02 VALUES LESS THAN(TO\_DATE('2008-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test\_space02,

PARTITION part03 VALUES LESS THAN(TO\_DATE('2008-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test\_space03,

PARTITION part04 VALUES LESS THAN (MAXVALUE)TABLESPACE test\_space04

);

\--插入测试数据

insert into range\_examplevalues(TO\_DATE('2008-06-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), '1111', 1);

insert into range\_examplevalues(TO\_DATE('2008-07-20 00:00:00','yyyy-mm-dd hh24:mi:ss'), '2222', 2);

insert into range\_examplevalues(TO\_DATE('2008-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), '3333', 3);

commit;

\--在表上执行查询

**select** \* **from** range\_example;

\--在表分区上执行查询

**select** \* **from** range\_example **partition**(part01);

### 列表分区表 ###

创建一个按字段数据列表固定可枚举值分区的表。插入记录分区字段的值必须在列表中，否则不能被插入。

示例代码：

CREATE TABLE list\_example(

dnameVARCHAR2(10),

DATA VARCHAR2(20)

)

PARTITION BY LIST(dname)

(

PARTITION part01VALUES('初始登记','转移登记'),

PARTITION part02 VALUES('更名登记','楼盘变更'),

PARTITION part03 VALUES('抵押登记'),

PARTITION part03 VALUES('限制登记')

);

### 哈希分区表 ###

创建一个按字段数据Hash值分区的表

示例代码：

CREATE TABLE hash\_example(

hash\_key\_column DATE,

DATA VARCHAR2(20)

)

PARTITION BY HASH(hash\_key\_cloumn)

(

PARTITION part01,

PARTITION part02

);

### 组合分区表 ###

在分区中可以再建立子分区，以实现分区组合。可任意对上述各类分区进行组合分区。

此例中创建了一个由范围分区和哈希分区组合实现分区的表。

示例代码：

CREATE TABLE range\_hash\_example(

range\_column\_key int,

hash\_column\_key INT,

DATAVARCHAR2(20)

)

PARTITION BY RANGE(range\_column\_key)

SUBPARTITION BY HASH(hash\_column\_key)SUBPARTITIONS 2

(

PARTITION part\_1 VALUES LESS THAN (100000000)

(

SUBPARTITION part\_1\_sub\_1,

SUBPARTITION part\_1\_sub\_2,

SUBPARTITION part\_1\_sub\_3

PARTITION part\_2 VALUES LESS THAN (200000000)

(

SUBPARTITION part\_2\_sub\_1,

SUBPARTITION part\_2\_sub\_2

)

);

\--注 subpartitions 2 并不是指定subpartition的个数一定为2，实际上每个分区的子分区个数可以不同。如果不指定subpartition的具体明细，则系统按照subpartitions的值指定subpartition的个数生成子分区，名称由系统定义 。

## 2增加表分区 ##

\-- range partitioned table

ALTER TABLE range\_example ADD PARTITIONpart04 VALUES LESS THAN (TO\_DATE('2008-10-1 00:00:00','yyyy-mm-ddhh24:mi:ss'));

\--list partitioned table

ALTER TABLE list\_example ADD PARTITIONpart04 VALUES ('TE');

\--Adding Values for a List Partition

ALTER TABLE list\_example MODIFY PARTITION part04 ADD VALUES('MIS');

\--Dropping Values from a List Partition

ALTER TABLE list\_example MODIFY PARTITION part04 DROP VALUES('MIS');

\--hash partitioned table

ALTER TABLE hash\_example ADD PARTITIONpart03;

\--增加subpartition

ALTER TABLE range\_hash\_example MODIFY PARTITIONpart\_1 ADD SUBPARTITION part\_1\_sub\_4;

注:hash partitioned table 新增partition时，现有表的中所有data都有重新计算hash值，然后重新分配到分区中。所以被重新分配的分区的 indexes需要rebuild 。

## 3删除分区 ##

ALTER TABLE ... DROP PARTITION part\_name;

## 4分区合并 ##

### 合并父分区 ###

ALTER TABLE range\_example MERGE PARTITIONSpart01\_1, part01\_2 INTO PARTITION part01 UPDATE INDEXES;

\--如果省略update indexes子句的话，必须重建受影响的分区的index;

ALTER TABLE range\_example MODIFY PARTITIONpart02 REBUILD UNUSABLE LOCAL INDEXES;

### 合并子分区 ###

ALTER TABLE composite\_example

MERGE SUBPARTITIONS part\_1\_sub\_2, part\_1\_sub\_3INTO SUBPARTITION part\_1\_sub\_2 UPDATE INDEXES;

## 5转换分区 ##

可以将分区表转换成非分区表，或者几种不同分区表之间的转换。

如下：

CREATE TABLE hash\_part02 AS SELECT \* FROMhash\_example WHERE 1=2;

ALTER TABLE hash\_example EXCHANGE PARTITIONpart02 WITH TABLE hash\_part02;

这时，分区表hash\_example中的part02分区的资料将被转移到hash\_part02这个非分区表中。

## 6关于分区表和索引 ##

在分区表上可以建立三种类型的索引：1和普通表一样的全局索引；2.全局分区索引；3.本地分区索引。

它们之间的区别如下图示:

以表range\_example为例。

1.建立普通的索引

create index com\_index\_range\_example\_id onrange\_example(id);

2.建立本地分区索引

create index  local\_index\_range\_example\_id onrange\_example(id) local;

3.建立全局分区索引

create index gidx\_range\_exampel\_id onrange\_example(id)

GLOBAL partition by  range(id)

(

part\_01 values less than(1000),

part\_02 values less than(MAXVALUE)

);

对于分区索引的删除，local index 不能指定分区名称，单独的删除分区索引。

local index 对应的分区会伴随着data分区的删除而一起被删除。globalpartition index 可以指定分区名称，删除某一分区。但是有一点要注意，如果该分区不为空，则会导致更高一级的索引分区被置为UNUSABLE 。

ALTER INDEX gidx\_range\_exampel\_id drop partition part\_01 ; 此句将导致part\_02 状态为UNUSABLE

转自

[**在路上**][Link 1]

[ORACLE分区表的使用和管理][ORACLE]

[Link 1]: http://www.cnblogs.com/advocate/
[ORACLE]: http://www.cnblogs.com/advocate/archive/2010/09/03/1816628.html

ORACLE分区表的使用和管理

发表评论取消回复

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

相关阅读

相关 Oracle分区表管理误区解析

相关 Oracle分区表管理中常见问题

相关 Oracle分区表管理不当导致的问题

相关 Oracle表空间管理异常：动态分区使用不当

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

相关 ORACLE分区表的使用和管理

相关 ORACLE分区表的使用和管理

相关 SQL SERVER 分区表的总结--分区表的维护和管理

相关 oracle表分区查询的使用

相关 Oracle表的分区