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

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) )

 2.Hash分区：

对于那些无法有效划分范围的表，可以使用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
);

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

 

 3.List分区：

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://blog.csdn.net/tianlesoftware/archive/2010/01/06/5134819.aspx
分区表 之 Interval分区 和 虚拟列 按星期分区表
http://blog.csdn.net/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://blog.csdn.net/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

在线重定义能保证数据的一致性，在大部分时间内，表都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。
关于DBMS_REDEFINITION的介绍，参考官方连接：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBFDJBC
关于用在线重定义创建分区表，参考：
How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218693.aspx

这个功能只在9.2.0.4以后的版本才有，在线重定义表具有以下功能：
（1）修改表的存储参数；
（2）将表转移到其他表空间；
（3）增加并行查询选项；
（4）增加或删除分区；
（5）重建表以减少碎片；
（6）将堆表改为索引组织表或相反的操作；
（7）增加或删除一个列。

在Oracle 10.2.0.4和11.1.0.7 版本下，在线重定义可能会遇到如下bug：
Bug 7007594 - ORA-600 [12261]
http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx

在线重定义的大致操作流程如下：
（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_stats.gather_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_redefinition.can_redef_table(‘icd‘, ‘unpar_table‘);
pl/sql 过程已成功完成。
4.2 如果4.1 没有问题，开始重定义，这个过程可能要等一会。
这里要注意：如果分区表和原表列名相同，可以用如下方式进行：
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => ‘ICD‘,
orig_table => ‘unpar_table‘,
int_table => ‘par_table‘);
END;
/
如果分区表的列名和原表不一致，那么在开始重定义的时候，需要重新指定映射关系：
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
‘ICD‘,
‘unpar_table‘,
‘par_table‘,
‘ID ID, create_date TIME‘, -- 在这里指定新的映射关系
DBMS_REDEFINITION.CONS_USE_PK);
这一步操作结束后，数据就已经同步到这个临时的分区表里来了。
4.3 同步新表，这是可选的操作
SQL> BEGIN
2 dbms_redefinition.sync_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_stats.gather_table_stats(‘icd‘, ‘par_table‘, cascade => true);
pl/sql 过程已成功完成。
4.6 结束重定义
SQL> BEGIN
2 dbms_redefinition.finish_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

来源 ：转自：Oracle 分区表的新增、修改、删除、合并。普通表转分区表方法 - 码不能停 - 博客园 (cnblogs.com)

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

标签：ber   管理操作   逻辑   映射   uname   不同   byte   date   add