当前位置:Gxlcms > 数据库问题 > Oracle 分区表的新增、修改、删除、合并。普通表转分区表方法

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

时间:2021-07-01 10:21:17 帮助过:34人阅读

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;
提交完成。
  1. 收集表的统计信息
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   

人气教程排行