Re: Partition Exchange Newbie Question

From: Timo Raitalaakso <rafu_at_iki.fi>
Date: Wed, 17 Jul 2013 01:07:31 +0300
Message-ID: <51E5C423.4010600_at_iki.fi>

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_at_gmail.com>
>
> I want to test a partition exchange as an alternative method of doing some
> work in the database.
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 17 2013 - 00:07:31 CEST