普通表转分区表和交换分区（oracle）

最新推荐文章于 2025-02-20 17:44:40 发布

转载最新推荐文章于 2025-02-20 17:44:40 发布 · 610 阅读

本文介绍了将普通表转换为分区表的四种方法，包括Export/Import方法、使用子查询的Insert方法、分区交换方法及DBMS_REDEFINITION方法，并详细展示了分区交换的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[sql] view plain copy

普通表转分区表方法
将普通表转换成分区表有4种方法：
1. Export/import method
2. Insert with a subquery method
3. Partition exchange method
4. DBMS_REDEFINITION
select * from t_user_info_test;
--方法一
drop table t_phone_test purge;
create table t_phone_test(phone,part) nologging partition by list(part)
(
partition p0 values('0'),
partition p1 values('1'),
partition p2 values('2'),
partition p3 values('3'),
partition p4 values('4'),
partition p5 values('5'),
partition p6 values('6'),
partition p7 values('7'),
partition p8 values('8'),
partition p9 values('9')
)
as
select user_mobile phone,substr(user_mobile,-1,1) part
from t_user_info_test;
select * from t_phone_test partition(p0);
select * from t_phone_test where part='0';
--方法二交换分区
这种方法只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。
交换分区的操作步骤如下：
1. 创建分区表，假设有2个分区，P1，P2.
2. 创建表A存放P1规则的数据。
3. 创建表B 存放P2规则的数据。
4. 用表A 和P1 分区交换。把表A的数据放到到P1分区
5. 用表B 和p2 分区交换。把表B的数据存放到P2分区。
create table t_phone_test_0 nologging
as
select user_mobile phone,substr(user_mobile,-1,1) part
from t_user_info_test where substr(user_mobile,-1,1)='0';
select count(*) from t_phone_test where part='0';
--4410
select count(*) from t_user_info_test where substr(user_mobile,-1,1)='0';
--4410
alter table t_phone_test exchange partition p0 with table t_phone_test_0;
delete from t_phone_test_0;
select count(*) from t_phone_test where part='0';
select count(*) from t_phone_test_0;
insert into t_phone_test(phone,part) values('15267046070','0');
--p0一条数据，t_phone_test_0里4410条数据，交换之后p0是4410，t_phone_test_0是1，再执行一次数据又换回来了。
insert into t_phone_test_0(phone,part) values('15267046070','1');
alter table t_phone_test exchange partition p0 with table t_phone_test_0;
delete from t_phone_test_0 where part='1';
--合并分区
----alter table tbname merge partitions/subpartitions pt1,pt2 into partition/subpartition pt3;
alter table t_phone_test merge partitions p0,p1 into partition p0;
select count(*) from t_phone_test where part='0';
select count(*) from t_phone_test where part='1';
select count(*) from t_phone_test partition(p0);
select count(*) from t_phone_test partition(p1);
alter table t_phone_test add partition p10 values(default);
insert into t_phone_test(phone,part) values('15267046010','10');
insert into t_phone_test(phone,part) values('15267046020','20');
select * from
--
alter table t_phone_test drop partition p10;
alter table t_phone_test add partition p10 values( '10');
alter table t_phone_test exchange partition p10 with table t_phone_test_10;
--ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
alter table T_PHONE_TEST_10 modify PART VARCHAR2(2);
alter table t_phone_test merge partitions p0,p10 into partition p0;
--此时p0中有p0和p10的数据，但是p0的list不再是0而是0和10
partition P0 values ('10', '0')
tablespace APP_DATAN
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
alter table t_phone_test exchange partition p0 with table t_phone_test_10;
alter table t_phone_test drop partition p0;
alter table t_phone_test add partition p0 values( '0');
alter table t_phone_test exchange partition p0 with table t_phone_test_10;
drop table t_phone_test_10 purge;
create table t_phone_test_10 nologging
as
select user_mobile phone,substr(user_mobile,-2,2) part
from t_user_info_test where substr(user_mobile,-2,2)='10';
drop table t_phone_test_0 purge;
create table t_phone_test_0 nologging
as
select phone,substr(phone,-1,1) part
from t_phone_test_10;
alter table t_phone_test exchange partition p0 with table t_phone_test_0;
select * from t_phone_test_10;
select count(*) from t_phone_test partition(p0);
select count(*) from t_phone_test partition(p10);
select count(*) from t_phone_test_10;
select count(*) from t_phone_test_0;
select substr('123456',-1,1),substr('123456',-2,2),substr('123456',-3,2) from dual;
---------------------------------------------------------
1.创建分区表
drop table t_phone_test purge;
create table t_phone_test(phone,part) nologging partition by list(part)
(
partition p0 values('0'),
partition p1 values('1'),
partition p2 values('2'),
partition p3 values('3'),
partition p4 values('4'),
partition p5 values('5'),
partition p6 values('6'),
partition p7 values('7'),
partition p8 values('8'),
partition p9 values('9')
)
as
select user_mobile phone,substr(user_mobile,-1,1) part
from t_user_info_test;
select count(*) from t_phone_test partition(p0);--4410
select count(*) from t_phone_test partition(p10);
select count(*) from t_phone_test_10;
select count(*) from t_phone_test_0;
2.创建基表
drop table t_phone_test_10 purge;
create table t_phone_test_10 nologging
as
select phone,substr(phone,-2,2) part
from t_phone_test where substr(phone,-2,2)='10';
select count(*) from t_phone_test_10;--406
--ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
alter table T_PHONE_TEST_10 modify PART VARCHAR2(2);
3.添加分区
alter table t_phone_test add partition p10 values( '10');
select count(*) from t_phone_test partition(p10);--0
4.交换分区
alter table t_phone_test exchange partition p10 with table t_phone_test_10;
select count(*) from t_phone_test partition(p10);--406
5.合并分区
alter table t_phone_test merge partitions p0,p10 into partition p0;
select count(*) from t_phone_test partition(p0);--4816
--此时p0中有p0和p10的数据，但是p0的list不再是0而是0和10
partition P0 values ('10', '0')
tablespace APP_DATAN
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
6.交换分区
alter table t_phone_test exchange partition p0 with table t_phone_test_10;
select count(*) from t_phone_test partition(p0);--0
select count(*) from t_phone_test_10;--4816
6.删除分区和添加分区
alter table t_phone_test drop partition p0;
alter table t_phone_test add partition p0 values('0');
7.筛选数据
drop table t_phone_test_0 purge;
create table t_phone_test_0 nologging
as
select phone,substr(phone,-1,1) part
from t_phone_test_10 where substr(phone,-1,1)='0';
select count(*) from t_phone_test_0;--4816
8.交换分区
alter table t_phone_test exchange partition p0 with table t_phone_test_0;
select count(*) from t_phone_test partition(p0);--4816
select count(*) from t_phone_test_0;--0