Re: Partition Exchange Newbie Question
- From: Timo Raitalaakso <rafu@xxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 17 Jul 2013 01:07:31 +0300
Here is an approach using subpartitions for partition exhange loading.
Using 11g range-range composite partitioning and tested with 12c.
Hopefully you get some idea for your 10g environment. A stage table is
partitioned similarily than a target table. Also a tmp table is needed
in between. A loading sequence number is used in the target table as a
subpartitionin key.
create or replace function partitionname(i_rid rowid) return varchar2
/*a helper function to visualize the populated rows*/
is
ret varchar2(31);
begin
select subobject_name into ret
from user_objects where data_object_id = dbms_rowid.rowid_object(i_rid);
return ret;
end;
/
--Needed structure
create sequence loadingseq;
create table stage(pk_col1 number not null
, other_col1 number(9) not null
, other_col2 date
, other_col3 varchar2(20)
, loadingseq number not null
)
partition by range(pk_col1)
(partition p1 values less than(1000)
, partition p2 values less than(2000)
)
;
create table tmp(pk_col1 number not null
, other_col1 number(9) not null
, other_col2 date
, other_col3 varchar2(20)
, loadingseq number not null
)
;
create table target(pk_col1 number not null
, other_col1 number(9) not null
, other_col2 date
, other_col3 varchar2(20)
, loadingseq number not null
)
partition by range(pk_col1)
subpartition by range(loadingseq) subpartition template (subpartition
sp0 values less than (1))
( partition p1 values less than(1000)
, partition p2 values less than(2000)
)
;
create or replace procedure loadexchange as
m_seq number;
begin
m_seq := loadingseq.nextval;
insert into stage(pk_col1,other_col1,other_col2,other_col3,loadingseq)
select l
, l
, trunc(sysdate+l)
, to_char(l, 'XXX')
, m_seq
from (select level l from dual connect by level < 2000);
--exhange to target
execute immediate 'alter table target modify partition p1 add
subpartition P1_SP'||m_seq||' values less than ('||(m_seq+1)||')';
execute immediate 'alter table target modify partition p2 add
subpartition P2_SP'||m_seq||' values less than ('||(m_seq+1)||')';
execute immediate 'alter table stage exchange partition p1 with table
tmp';
execute immediate 'alter table target exchange subpartition
p1_sp'||m_seq||' with table tmp';
execute immediate 'alter table stage exchange partition p2 with table
tmp';
execute immediate 'alter table target exchange subpartition
p2_sp'||m_seq||' with table tmp';
end;
/
exec loadexchange
exec loadexchange
select * from user_tab_subpartitions;
select s.*,partitionname(rowid) from target s where pk_col1 in (1,1001);
select * from tmp;
select partitionname(rowid) pname,count(*) from stage s group by
partitionname(rowid) order by 1;
select partitionname(rowid) pname,count(*) from target s group by
partitionname(rowid) order by 1;
drop procedure loadexchange;
drop sequence loadingseq;
drop table stage;
drop table tmp;
drop table target;
drop function partitionname;
16.7.2013 20:55, David Fitzjarrell kirjoitti:
> Yes, each table to be exchanged needs to have data matching the partition
> range:
>
> From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
>
> I want to test a partition exchange as an alternative method of doing some
> work in the database.
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
