这里我已范围分区表为例子
创建一张普通表
create table t (id int,name varchar2(100));
插入数据
begin
for i in 1..1000
loop
insert into ws.t values (i,'ws');
end loop;
commit;
end;
/
方法1:使用中间表--bad
创建分区表
Create table p_t (id int,name varchar2(100))
Partition by range(id)
(partition p100 values less than(200),
partition p200 values less than(300),
partition p300 values less than(600),
partition pmax values less than(maxvalue)
);
创建中间表
create table temp_t200 as select * from t where 1=2;
create table temp_t300 as select * from t where 1=2;
在系统层上设置并行
alter session force parallel->表,索引上的设定-> 系统参数 注意设置之前使用TOP命令查看性能
采用并行或者直接加载的方法来加速
Insert /*+parallel(temp_t200 2) */ into temp_t200 select /*+parallel(t 2) */ * from t where id <200;
直接路径
Insert /*+append */ into temp_t200 select * from t where id <200;
使用分区交换
alter table p_t exchange partition p100 with table temp_t200;
方法2:直接导入到分区表 -- normal
Insert /*+parallel(p_t 2) */ into p_t select /*+parallel(t 2) */ * from t where id >600;
commit;
方法3: -- normal
分区切割
Create table p_t1 (id int,name varchar2(100))
Partition by range(id)
(
partition pmax values less than(maxvalue)
);
分区交换
alter table p_t1 exchange partition pmax with table t;
分区切割
alter table p_t1 SPLIT PARTITION PMAX at (200) into (PARTITION p200 ,PARTITION P_MAX);
select max(id),min(id) from p_t1 partition(p200);
select max(id),min(id) from p_t1 partition(p_max);
alter table p_t1 SPLIT PARTITION P_MAX at (300) into (PARTITION p300 ,PARTITION P_MAX);
alter table p_t1 SPLIT PARTITION P_MAX at (400) into (PARTITION p400 ,PARTITION P_MAX);
查看现有分区情况
select partition_name,
segment_type,
bytes
from user_segments
where segment_name ='D_METER';
方法4:impdp --normal
Create table p_t2 (id int,name varchar2(100))
Partition by range(id)
(partition p100 values less than(200),
partition p200 values less than(300),
partition p300 values less than(600),
partition pmax values less than(maxvalue)
);
expdp system/adminadmin dumpfile=t.dmp directory=test tables=ycy.t
impdp system/adminadmin dumpfile=t.dmp directory=test remap_table=t:p_t2 table_exists_action=append
remeber table ycy.* don't need ,always need table_name only
impdp system/adminadmin dumpfile=t.dmp directory=test remap_data=ycy.t:ycy.p_t2
导入到分区中
方法5:refine_table -- dangers
SELECT * FROM Sys.Dba_Tab_Modifications WHERE Table_Owner = 'IMRA';
ALTER SYSTEM SET recyclebin = OFF;
------------------------------------------------------------------------------------------------------------
创建时间分区
create table part_p1 (name varchar2(30),time date)
partition by range(time)
(
partition p1 values less than (to_date('2002-10-22','yyyy-mm-dd')),
partition p2 values less than (to_date('2003-10-22','yyyy-mm-dd')),
partition p3 values less than (to_date('2004-10-22','yyyy-mm-dd')),
partition p4 values less than (to_date('2005-10-22','yyyy-mm-dd')),
partition pmax values less than(maxvalue)
);
增加时间分区:
alter table part split partition pmax at(to_date('2011-10-22','yyyy-mm-dd'))into(partition p10,partition pmax);
-------------------------------------------------------------------------------------
做测试:
insert into part values ('ws','11-oct-06');
insert into part values ('ws','13-Jan-08');
insert into part values ('ws','06-oct-09');
insert into part values ('ws','22-Aug-01');
insert into part values ('ws','15-oct-02');
insert into part values ('ws','03-Dec-11');
insert into part values ('ws','03-Dec-12');
insert into part values ('ws','03-Dec-13');
insert into part values ('ws','03-Dec-14');
insert into part values ('ws','03-Dec-15');
insert into part values ('ws','03-Dec-16');
--------------------------------------------------------------------------------
分别查询是否正确:
select partition_name from user_segments where segment_name ='PART';
P1
P23
P3
P4
PMAX
P7
P5
P6
select * from part partition ();
---------------------------------------------------------------------------------
创建global索引
create table t_partition_range (id number,name varchar2(50))
partition by range(id)(
partition t_range_p1 values less than (10) tablespace tbspart01,
partition t_range_p2 values less than (20) tablespace tbspart02,
partition t_range_p3 values less than (30) tablespace tbspart03,
partition t_range_pmax values less than (maxvalue) tablespace tbspart04
);
创建分区索引
create index IDX_PARTI_RANGE_ID on T_PARTITION_RANGE(id) local;
local索引的分区维护完全依赖于其索引所在表。
创建一张普通表
create table t (id int,name varchar2(100));
插入数据
begin
for i in 1..1000
loop
insert into ws.t values (i,'ws');
end loop;
commit;
end;
/
方法1:使用中间表--bad
创建分区表
Create table p_t (id int,name varchar2(100))
Partition by range(id)
(partition p100 values less than(200),
partition p200 values less than(300),
partition p300 values less than(600),
partition pmax values less than(maxvalue)
);
创建中间表
create table temp_t200 as select * from t where 1=2;
create table temp_t300 as select * from t where 1=2;
在系统层上设置并行
alter session force parallel->表,索引上的设定-> 系统参数 注意设置之前使用TOP命令查看性能
采用并行或者直接加载的方法来加速
Insert /*+parallel(temp_t200 2) */ into temp_t200 select /*+parallel(t 2) */ * from t where id <200;
直接路径
Insert /*+append */ into temp_t200 select * from t where id <200;
使用分区交换
alter table p_t exchange partition p100 with table temp_t200;
方法2:直接导入到分区表 -- normal
Insert /*+parallel(p_t 2) */ into p_t select /*+parallel(t 2) */ * from t where id >600;
commit;
方法3: -- normal
分区切割
Create table p_t1 (id int,name varchar2(100))
Partition by range(id)
(
partition pmax values less than(maxvalue)
);
分区交换
alter table p_t1 exchange partition pmax with table t;
分区切割
alter table p_t1 SPLIT PARTITION PMAX at (200) into (PARTITION p200 ,PARTITION P_MAX);
select max(id),min(id) from p_t1 partition(p200);
select max(id),min(id) from p_t1 partition(p_max);
alter table p_t1 SPLIT PARTITION P_MAX at (300) into (PARTITION p300 ,PARTITION P_MAX);
alter table p_t1 SPLIT PARTITION P_MAX at (400) into (PARTITION p400 ,PARTITION P_MAX);
查看现有分区情况
select partition_name,
segment_type,
bytes
from user_segments
where segment_name ='D_METER';
方法4:impdp --normal
Create table p_t2 (id int,name varchar2(100))
Partition by range(id)
(partition p100 values less than(200),
partition p200 values less than(300),
partition p300 values less than(600),
partition pmax values less than(maxvalue)
);
expdp system/adminadmin dumpfile=t.dmp directory=test tables=ycy.t
impdp system/adminadmin dumpfile=t.dmp directory=test remap_table=t:p_t2 table_exists_action=append
remeber table ycy.* don't need ,always need table_name only
impdp system/adminadmin dumpfile=t.dmp directory=test remap_data=ycy.t:ycy.p_t2
导入到分区中
方法5:refine_table -- dangers
SELECT * FROM Sys.Dba_Tab_Modifications WHERE Table_Owner = 'IMRA';
ALTER SYSTEM SET recyclebin = OFF;
------------------------------------------------------------------------------------------------------------
创建时间分区
create table part_p1 (name varchar2(30),time date)
partition by range(time)
(
partition p1 values less than (to_date('2002-10-22','yyyy-mm-dd')),
partition p2 values less than (to_date('2003-10-22','yyyy-mm-dd')),
partition p3 values less than (to_date('2004-10-22','yyyy-mm-dd')),
partition p4 values less than (to_date('2005-10-22','yyyy-mm-dd')),
partition pmax values less than(maxvalue)
);
增加时间分区:
alter table part split partition pmax at(to_date('2011-10-22','yyyy-mm-dd'))into(partition p10,partition pmax);
-------------------------------------------------------------------------------------
做测试:
insert into part values ('ws','11-oct-06');
insert into part values ('ws','13-Jan-08');
insert into part values ('ws','06-oct-09');
insert into part values ('ws','22-Aug-01');
insert into part values ('ws','15-oct-02');
insert into part values ('ws','03-Dec-11');
insert into part values ('ws','03-Dec-12');
insert into part values ('ws','03-Dec-13');
insert into part values ('ws','03-Dec-14');
insert into part values ('ws','03-Dec-15');
insert into part values ('ws','03-Dec-16');
--------------------------------------------------------------------------------
分别查询是否正确:
select partition_name from user_segments where segment_name ='PART';
P1
P23
P3
P4
PMAX
P7
P5
P6
select * from part partition ();
---------------------------------------------------------------------------------
创建global索引
create table t_partition_range (id number,name varchar2(50))
partition by range(id)(
partition t_range_p1 values less than (10) tablespace tbspart01,
partition t_range_p2 values less than (20) tablespace tbspart02,
partition t_range_p3 values less than (30) tablespace tbspart03,
partition t_range_pmax values less than (maxvalue) tablespace tbspart04
);
创建分区索引
create index IDX_PARTI_RANGE_ID on T_PARTITION_RANGE(id) local;
local索引的分区维护完全依赖于其索引所在表。