2023年11月26日发(作者：)

Oracle分区表的新增、修改、删除、合并。普通表转分区表⽅法

⼀、分区概念

Oracle允许将表、索引、索引组织表细分成更⼩的⽚，每个⽚我们称之为分区。分区有其⾃⼰的名字和存储参数。

每⾏数据只能属于⼀个分区，分区键决定数据⾏属于哪个分区。分区键由⼀个或多个列组成。Oracle⾃动的将数据的DML操作映射到相应的分区中。

⼆、分区的优点：

a.由于将数据分散到各个分区中，减少了数据损坏的可能性；

b.可以对单独的分区进⾏备份和恢复；

c.可以将分区映射到不同的物理磁盘上，来分散IO；

d.提⾼可管理性、可⽤性和性能。

三、Oracle 10g提供了以下⼏种分区类型：

a.范围分区（range）；

b.哈希分区（hash）；

c.列表分区（list）；

d.范围－哈希复合分区（range-hash）；

e.范围－列表复合分区（range-list）。

分区：

Range分区是应⽤范围⽐较⼴的表分区⽅式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。

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

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

如：

create table pdba (id number, time date) partition by range (time)

(

partition p1 values less than (to_date('2021-10-1', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2021-11-1', 'yyyy-mm-dd')),

partition p3 values less than (to_date('2021-12-1', 'yyyy-mm-dd')),

partition p4 values less than (maxvalue)

)

分区：

对于那些⽆法有效划分范围的表，可以使⽤hash分区，这样对于提⾼性能还是会有⼀定的帮助。hash分区会将表中的数据平均分配到你指定的⼏个分区

中，列所在分区是依据分区列的hash值⾃动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以⽀持多个依赖列。

如：

create table test

(

transaction_id number primary key,

item_id number(8) not null

)

partition by hash(transaction_id)

(

partition part_01 tablespace tablespace01,

partition part_02 tablespace tablespace02,

partition part_03 tablespace tablespace03

);

在这⾥，我们指定了每个分区的表空间。

分区：

List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有⼀个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单

个分区对应值可以是多个。

在分区时必须确定分区列可能存在的值，⼀旦插⼊的列值不在分区范围内，则插⼊/更新就会失败，因此通常建议使⽤list分区时，要创建⼀个default分区

存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

在根据某字段，如城市代码分区时，可以指定default，把⾮分区规则的数据，全部放到这个default分区。

如：

create table custaddr

(

id varchar2(15 byte) not null,

areacode varchar2(4 byte)

)

partition by list (areacode)

( partition t_list025 values ('025'),

partition t_list372 values ('372') ,

partition t_list510 values ('510'),

partition p_other values (default)

)

4.组合分区：

如果某表按照某列分区之后，仍然较⼤，或者是⼀些其它的需求，还可以通过分区内再建⼦分区的⽅式将分区再分区，即组合分区的⽅式。

组合分区呢在10g中有两种：range-hash，range-list。注意顺序，根分区只能是range分区，⼦分区可以是hash分区或list分区。

如：

create table test

(

transaction_id number primary key,

transaction_date date

)

partition by range(transaction_date) subpartition by hash(transaction_id)

subpartitions 3 store in (tablespace01,tablespace02,tablespace03)

(

partition part_01 values less than(to_date(’2009-01-01’,’yyyy-mm-dd’)),

partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)),

partition part_03 values less than(maxvalue)

);

create table emp_sub_template (deptno number, empname varchar(32), grade number)

partition by range(deptno) subpartition by hash(empname)

subpartition template

(subpartition a tablespace ts1,

subpartition b tablespace ts2,

subpartition c tablespace ts3,

subpartition d tablespace ts4

)

(partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than (maxvalue)

);

create table quarterly_regional_sales

(deptno number, item_no varchar2(20),

txn_date date, txn_amount number, state varchar2(2))

tablespace ts4

partition by range (txn_date)

subpartition by list (state)

(partition q1_1999 values less than (to_date('1-apr-1999','dd-mon-yyyy'))

(subpartition q1_1999_northwest values ('or', 'wa'),

subpartition q1_1999_southwest values ('az', 'ut', 'nm'),

subpartition q1_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q1_1999_southeast values ('fl', 'ga'),

subpartition q1_1999_northcentral values ('sd', 'wi'),

subpartition q1_1999_southcentral values ('ok', 'tx')

),

partition q2_1999 values less than ( to_date('1-jul-1999','dd-mon-yyyy'))

(subpartition q2_1999_northwest values ('or', 'wa'),

subpartition q2_1999_southwest values ('az', 'ut', 'nm'),

subpartition q2_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q2_1999_southeast values ('fl', 'ga'),

subpartition q2_1999_northcentral values ('sd', 'wi'),

subpartition q2_1999_southcentral values ('ok', 'tx')

),

partition q3_1999 values less than (to_date('1-oct-1999','dd-mon-yyyy'))

(subpartition q3_1999_northwest values ('or', 'wa'),

subpartition q3_1999_southwest values ('az', 'ut', 'nm'),

subpartition q3_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q3_1999_southeast values ('fl', 'ga'),

subpartition q3_1999_northcentral values ('sd', 'wi'),

subpartition q3_1999_southcentral values ('ok', 'tx')

),

partition q4_1999 values less than ( to_date('1-jan-2000','dd-mon-yyyy'))

(subpartition q4_1999_northwest values ('or', 'wa'),

subpartition q4_1999_southwest values ('az', 'ut', 'nm'),

subpartition q4_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q4_1999_southeast values ('fl', 'ga'),

subpartition q4_1999_northcentral values ('sd', 'wi'),

subpartition q4_1999_southcentral values ('ok', 'tx')

)

);

在Oracle 11g中，组合分区功能这块有所增强，⼜增加了range-range,list-range,

list-list,list-hash，并且 11g⾥⾯还⽀持Interval分区和虚拟列分区。

这块可以参考Blog：

Oracle 11g 新特性简介

http:///tianlesoftware/archive/2010/01/06/5134819.aspx

分区表 之 Interval分区 和 虚拟列 按星期分区表

http:///tianlesoftware/archive/2010/06/10/5662337.aspx

四. 普通表转分区表⽅法

1. Export/import method

2. Insert with a subquery method

3. Partition exchange method

4. DBMS_REDEFINITION

具体参考：

How to Partition a Non-partitioned Table [ID 1070693.6]

http:///tianlesoftware/archive/2011/03/02/6218704.aspx

逻辑导出导⼊这⾥就不做说明，我们看看其他三种⽅法。

4.1 插⼊： Insert with a subquery method

Oracle 11g的Interval

在11g⾥的Interval创建，这种⽅法对没有写全的分区会⾃动创建。 ⽐如我这⾥只写了1⽉⽇期，如果插⼊的数据有其他⽉份的，会⾃动⽣成对应的分区。

CREATE TABLE intervaldave

PARTITION BY RANGE (time_fee)

INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )

(PARTITION part1

VALUES LESS THAN (TO_DATE ('01/12/2010', 'MM/DD/YYYY')))

AS

SELECT ID, TIME_FEE FROM DAVE; --当作create table a as select * from b 去理解

4.2 Oracle 10g 版本

在10g⾥⾯，我需要写全所有的分区。

create table pdba (id, time) partition by range (time)

(partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

partition p4 values less than (maxvalue))

as select id, time_fee from dba;

4.3 交换分区：Partition exchange method

这种⽅法只是对数据字典中分区和表的定义进⾏了修改，没有数据的修改或复制，效率最⾼。适⽤于包含⼤数据量的表转到分区表中的⼀个分区的操

作。尽量在闲时进⾏操作。

交换分区的操作步骤如下：

1. 创建分区表，假设有2个分区，P1，P2.

2. 创建表A存放P1规则的数据。

3. 创建表B 存放P2规则的数据。

4. ⽤表A 和P1 分区交换。 把表A的数据放到到P1分区

5. ⽤表B 和p2 分区交换。 把表B的数据存放到P2分区。

创建分区表：

create table p_dba

(id number,time date)

partition by range(time)

(

partition p1 values less than (to_date('2010-09-1', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd'))

);

表已创建。

注意：我这⾥只创建了2个分区，没有创建存放其他数据的分区。

创建2个分别对应分区的基表：

SQL> CREATE TABLE dba_p1 as SELECT id,time_fee FROM dba_old WHERE time_fee<TO_DATE('2010-09-1', 'YYYY-MM-DD');

表已创建。

SQL> CREATE TABLE dba_p2 as SELECT id,time_fee FROM dba_old WHERE time_fee<TO_DATE('2010-11-1', 'YYYY-MM-DD') and time_fee>TO_DATE('2010-09-1', 'YYYY-MM-DD');

表已创建。

SQL> select count(*) from dba_p1;

COUNT(*)

----------

1536020

SQL> select count(*) from dba_p2;

COUNT(*)

----------

365932

SQL>

将2个基表与2个分区进⾏交换：

SQL> alter table p_dba exchange partition p1 with table dba_p1;

表已更改。

SQL> alter table p_dba exchange partition p2 with table dba_p2;

表已更改。

查询2个分区：

SQL> select count(*) from p_dba partition(p1);

COUNT(*)

----------

1536020

SQL> select count(*) from p_dba partition(p2);

COUNT(*)

----------

365932

注意：数据和之前的基表⼀致。

查询原来的2个基表：

SQL> select count(*) from dba_p2;

COUNT(*)

----------

0

SQL> select count(*) from dba_p1;

COUNT(*)

----------

0

注意： 2个基表的数据变成成0。

在这⾥我们看⼀个问题，⼀般情况下，我们在创建分区表的时候，都会有⼀个其他分区，⽤来存放不匹配分区规则的数据。 在这个例⼦中，我只创建了2

个分区，没有创建maxvalue分区。 现在我来插⼊⼀条不满⾜规则的数据，看结果：

SQL> insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd'));

insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd'))

*

第 1 ⾏出现错误:

ORA-14400: 插⼊的分区关键字未映射到任何分区

SQL> insert into p_dba values(999999,to_date('2009-12-29','yyyy-mm-dd'));

已创建 1 ⾏。

SQL> select * from p_dba where id=999999;

ID TIME

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

999999 29-12⽉-09

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

会话已更改。

SQL> select * from p_dba where id=999999;

ID TIME

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

999999 2009-12-29 00:00:00

SQL>

通过这个测试可以清楚，如果插⼊的数据不满⾜分区规则，会报ORA-14400错误。

4.4 使⽤在线重定义：DBMS_REDEFINITION

这个功能只在9.2.0.4以后的版本才有，在线重定义表具有以下功能：

（1）修改表的存储参数；

（2）将表转移到其他表空间；

（3）增加并⾏查询选项；

（4）增加或删除分区；

（5）重建表以减少碎⽚；

（6）将堆表改为索引组织表或相反的操作；

（7）增加或删除⼀个列。

在线重定义的⼤致操作流程如下：

（1）创建基础表A，如果存在，就不需要操作。

（2）创建临时的分区表B。

（3）开始重定义，将基表A的数据导⼊临时分区表B。

（4）结束重定义，此时在DB的 Name Directory⾥，已经将2个表进⾏了交换。即此时基表A成了分区表，我们创建的临时分区表B 成了普通表。 此时我

们可以删除我们创建的临时表B。它已经是普通表。

下⾯看⼀个⽰例：

1. 创建基本表和索引

create table unpar_table (

id number(10) primary key,

create_date date

);

表已创建。

sql> insert into unpar_table select rownum, created from dba_objects;

已创建72288⾏。

sql> create index create_date_ind on unpar_table(create_date);

索引已创建。

sql> commit;

提交完成。

2. 收集表的统计信息

sql> exec dbms__table_stats('icd', 'unpar_table', cascade => true);

3. 创建临时分区表

sql> create table par_table (id number primary key, time date) partition by range (time)

2 (partition p1 values less than (to_date('2004-7-1', 'yyyy-mm-dd')),

3 partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),

4 partition p3 values less than (to_date('2005-7-1', 'yyyy-mm-dd')),

5 partition p4 values less than (maxvalue));

表已创建。

4.进⾏重定义操作

4.1 检查重定义的合理性

sql> exec dbms__redef_table('icd', 'unpar_table');

pl/sql 过程已成功完成。

4.2 如果4.1 没有问题，开始重定义，这个过程可能要等⼀会。

这⾥要注意：如果分区表和原表列名相同，可以⽤如下⽅式进⾏：

SQL> BEGIN

DBMS__redef_table(

uname => 'ICD',

orig_table => 'unpar_table',

int_table => 'par_table');

END;

/

如果分区表的列名和原表不⼀致，那么在开始重定义的时候，需要重新指定映射关系：

SQL> EXEC DBMS__REDEF_TABLE(

'ICD',

'unpar_table',

'par_table',

'ID ID, create_date TIME', -- 在这⾥指定新的映射关系

DBMS__USE_PK);

这⼀步操作结束后，数据就已经同步到这个临时的分区表⾥来了。

4.3 同步新表，这是可选的操作

SQL> BEGIN

2 dbms__interim_table(

3 uname => 'ICD',

4 orig_table => 'unpar_table',

5 int_table => 'par_table');

6 END;

7 /

PL/SQL 过程已成功完成。

4.4 创建索引，在线重定义只重定义数据，索引还需要单独建⽴。

sql> create index create_date_ind2 on par_table(time);

索引已创建。

4.5 收集新表的统计信息

sql> exec dbms__table_stats('icd', 'par_table', cascade => true);

pl/sql 过程已成功完成。

4.6 结束重定义

SQL> BEGIN

2 dbms__redef_table(

3 uname => 'ICD',

4 orig_table => 'unpar_table',

5 int_table => 'par_table');

6 END;

7 /

PL/SQL 过程已成功完成。

结束重定义的意义：

基表unpar_table 和临时分区表par_table 进⾏了交换。 此时临时分区表par_table成了普通表，我们的基表unpar_table成了分区表。

我们在重定义的时候，基表unpar_table是可以进⾏DML操作的。 只有在2个表进⾏切换的时候会有短暂的锁表。

删除临时表

SQL> DROP TABLE par_table;

表已删除。

索引重命名

SQL> ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

索引已更改。

验证

sql> select partitioned from user_tables where table_name = 'UNPAR_TABLE';

par

来源 ：转⾃：