Re: Changing partitioning strategy from range to list for parent table in reference partition setup

From: rjamya <rjamya_at_gmail.com>
Date: Thu, 18 Jun 2015 20:56:15 -0400
Message-ID: <CAGurbTPns-5TqqH+o97s+yZidwFTy5Yd=UwwpN0y4BpM78z+cw_at_mail.gmail.com>

Thanks Connor, now i have to write a script to test this and automate this partition exchange for approx 400+ partitions. I am surely going to try this in next few days.

Raj

On Thu, Jun 18, 2015 at 8:40 PM, Connor McDonald <mcdonald.connor_at_gmail.com> wrote:

> Maybe dont do any rebuilding at all ?
>
>
> SQL> drop table OLDC;
>
> Table dropped.
>
> SQL> drop table OLDP;
>
> Table dropped.
>
> SQL> drop table NEWC;
>
> Table dropped.
>
> SQL> drop table NEWP;
>
> Table dropped.
>
> SQL> drop table EXCC;
>
> Table dropped.
>
> SQL> drop table EXCP;
>
> Table dropped.
>
> SQL> purge recyclebin;
>
> Recyclebin purged.
>
> SQL>
> SQL> create table OLDP (
> 2 p int primary key,
> 3 data int )
> 4 partition by range ( p )
> 5 (
> 6 partition p1 values less than (2),
> 7 partition p2 values less than (3)
> 8 );
>
> Table created.
>
> SQL>
> SQL> insert into OLDP values (1,1000);
>
> 1 row created.
>
> SQL> insert into OLDP values (2,2000);
>
> 1 row created.
>
> SQL>
> SQL> create table OLDC (
> 2 c int primary key,
> 3 p int not null,
> 4 data int,
> 5 constraint OLDC_FK foreign key (p)
> 6 references OLDP ( p )
> 7 on delete cascade
> 8 )
> 9 partition by reference ( OLDC_FK )
> 10 /
>
> Table created.
>
> SQL>
> SQL> insert into OLDC values (10,1,1000);
>
> 1 row created.
>
> SQL> insert into OLDC values (11,2,2000);
>
> 1 row created.
>
> SQL> insert into OLDC values (12,1,3000);
>
> 1 row created.
>
> SQL> insert into OLDC values (13,2,4000);
>
> 1 row created.
>
> SQL> insert into OLDC values (14,1,5000);
>
> 1 row created.
>
> SQL>
> SQL>
> SQL> create table NEWP (
> 2 p int primary key,
> 3 data int )
> 4 partition by list ( p )
> 5 (
> 6 partition p1 values (1),
> 7 partition p2 values (2)
> 8 );
>
> Table created.
>
> SQL>
> SQL>
> SQL> create table NEWC (
> 2 c int primary key,
> 3 p int not null,
> 4 data int,
> 5 constraint NEWC_FK foreign key (p)
> 6 references NEWP ( p )
> 7 on delete cascade
> 8 )
> 9 partition by reference ( NEWC_FK )
> 10 /
>
> Table created.
>
> SQL>
> SQL> create table EXCP (
> 2 p int primary key,
> 3 data int )
> 4 /
>
> Table created.
>
> SQL>
> SQL> create table EXCC (
> 2 c int primary key,
> 3 p int not null,
> 4 data int
> 5 )
> 6 /
>
> Table created.
>
> SQL>
> SQL> alter table OLDC exchange partition P1 with table EXCC;
>
> Table altered.
>
> SQL> alter table OLDP exchange partition P1 with table EXCP;
>
> Table altered.
>
> SQL> alter table NEWP exchange partition P1 with table EXCP;
>
> Table altered.
>
> SQL> alter table EXCC add constraint FK foreign key (p) references NEWP (
> p ) on delete cascade;
>
> Table altered.
>
> SQL> alter table NEWC exchange partition P1 with table EXCC;
>
> Table altered.
>
> SQL> alter table EXCC drop constraint FK ;
>
> Table altered.
>
> SQL>
> SQL> alter table OLDC exchange partition P2 with table EXCC;
>
> Table altered.
>
> SQL> alter table OLDP exchange partition P2 with table EXCP;
>
> Table altered.
>
> SQL> alter table NEWP exchange partition P2 with table EXCP;
>
> Table altered.
>
> SQL> alter table EXCC add constraint FK foreign key (p) references NEWP (
> p ) on delete cascade;
>
> Table altered.
>
> SQL> alter table NEWC exchange partition P2 with table EXCC;
>
> Table altered.
>
> SQL> alter table EXCC drop constraint FK ;
>
> Table altered.
>
> SQL>
> SQL> select * from OLDP;
>
> no rows selected
>
> SQL> select * from OLDC;
>
> no rows selected
>
> SQL>
> SQL> select * from NEWP;
>
> P DATA
> ---------- ----------
> 1 1000
> 2 2000
>
> SQL> select * from NEWC;
>
> C P DATA
> ---------- ---------- ----------
> 10 1 1000
> 12 1 3000
> 14 1 5000
> 11 2 2000
> 13 2 4000
>
>
>

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

Received on Fri Jun 19 2015 - 02:56:15 CEST