This discussion is archived
12 Replies Latest reply: Jan 25, 2013 2:04 PM by 767217 RSS

Trying to convert Interval Partitioned Table to Range..Exchange Partition..

user8941550 Newbie
Currently Being Moderated
Requirement:

Replace Interval partitioned Table by Range Partitioned Table
DROP TABLE A;

CREATE TABLE A
(
   a              NUMBER,
   CreationDate   DATE
)
PARTITION BY RANGE (CreationDate)
   INTERVAL ( NUMTODSINTERVAL (30, 'DAY') )
   (PARTITION P_FIRST
       VALUES LESS THAN (TIMESTAMP ' 2001-01-01 00:00:00'));


INSERT INTO A
     VALUES (1, SYSDATE);

INSERT INTO A
     VALUES (1, SYSDATE - 30);

INSERT INTO A
     VALUES (1, SYSDATE - 60);
I need to change this Interval Partitioned Table to a Range Partitioned Table. Can I do it using EXCHANGE PARTITION. As if I use the conventional way of creating another Range Partitioned table and then :

DROP TABLE A_Range
CREATE TABLE A_Range
(
a NUMBER,
CreationDate DATE
)
PARTITION BY RANGE (CreationDate)
   (partition MAX values less than (MAXVALUE));

Insert  /*+ append */  into A_Range Select * from A; --This Step takes very very long..Trying to cut it short using Exchange Partition.
Problems:

I can't do
 ALTER TABLE A_Range
  EXCHANGE PARTITION MAX
  WITH TABLE A
  WITHOUT VALIDATION;
 
ORA-14095: ALTER TABLE EXCHANGE requires a non-partitioned, non-clustered table
This is because both the tables are partitioned. So it does not allow me.

If I do instead :


create a non partitioned table for exchanging the data through partition.
  Create Table A_Temp as Select * from A;
  
   ALTER TABLE A_Range
  EXCHANGE PARTITION MAX
  WITH TABLE A_TEMP
  WITHOUT VALIDATION;
   
  select count(*) from A_Range partition(MAX);
 
-Problem is that all the data goes into MAX Partition.
Even after creating a lot of partitions by Splitting Partitions, still the data is in MAX Partition only.

So:

-- Is it that we can't Replace an Interval Partitioned Table by Range Partitioned Table using EXCHANGE PARTITION. i.e. We will have to do Insert into..
-- We can do it but I am missing something over here.
-- If all the data is in MAX Partition because of "WITHOUT VALIDATION" , can we make it be redistributed in the right kind of range partitions.
  • 1. Re: Trying to convert Interval Partitioned Table to Range..Exchange Partition..
    John Spencer Oracle ACE
    Currently Being Moderated
    You will need to pre-create the partitions in a_range, then exchange them one by one from a to a tmp then then to arange. Using your sample (thanks for proviing the code by the way).
    SQL> CREATE TABLE A
      2  (
      3     a              NUMBER,
      4     CreationDate   DATE
      5  )
      6  PARTITION BY RANGE (CreationDate)
      7     INTERVAL ( NUMTODSINTERVAL (30, 'DAY') )
      8     (PARTITION P_FIRST
      9         VALUES LESS THAN (TIMESTAMP ' 2001-01-01 00:00:00'));
    
    Table created.
    
    SQL> INSERT INTO A VALUES (1, SYSDATE);
    
    1 row created.
    
    SQL> INSERT INTO A VALUES (1, SYSDATE - 30);
    
    1 row created.
    
    SQL> INSERT INTO A VALUES (1, SYSDATE - 60);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    You can find the existing partitions form a using:
    SQL> select table_name, partition_name, high_value
      2  from user_tab_partitions
      3  where table_name = 'A';
    
    TABLE_NAME PARTITION_NAME HIGH_VALUE
    ---------- -------------- --------------------------------------------------------------------------------
    A          P_FIRST        TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
    A          SYS_P44        TO_DATE(' 2013-01-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
    A          SYS_P45        TO_DATE(' 2012-12-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
    A          SYS_P46        TO_DATE(' 2012-11-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
    You can then create table a_range with the apporopriate partitions. Note that you may need to create additional partitions in a_range because interval partitioning does not create partitions that it has no data for, even if that leaves "holes" in the partitioning scheme. So, based on the above:
    SQL> CREATE TABLE A_Range (
      2     a NUMBER,
      3     CreationDate DATE)
      4  PARTITION BY RANGE (CreationDate)
      5     (partition Nov_2012 values less than (to_date('30-nov-2012', 'dd-mon-yyyy')),
      6      partition Dec_2012 values less than (to_date('31-dec-2012', 'dd-mon-yyyy')),
      7      partition Jan_2013 values less than (to_date('31-jan-2013', 'dd-mon-yyyy')),
      8      partition MAX values less than (MAXVALUE));
    
    Table created.
    Now, create a plain table to use in the exchanges:
    SQL> CREATE TABLE A_tmp (
      2     a              NUMBER,
      3     CreationDate   DATE);
    
    Table created.
    and exchange all of the partitions:
    SQL> ALTER TABLE A
      2    EXCHANGE PARTITION sys_p44
      3    WITH TABLE A_tmp;
    
    Table altered.
    
    SQL> ALTER TABLE A_Range
      2    EXCHANGE PARTITION jan_2013
      3    WITH TABLE A_tmp;
    
    Table altered.
    
    SQL> ALTER TABLE A
      2    EXCHANGE PARTITION sys_p45
      3    WITH TABLE A_tmp;
    
    Table altered.
    
    SQL> ALTER TABLE A_Range
      2    EXCHANGE PARTITION dec_2012
      3    WITH TABLE A_tmp;
    
    Table altered.
    
    SQL> ALTER TABLE A
      2    EXCHANGE PARTITION sys_p46
      3    WITH TABLE A_tmp;
    
    Table altered.
    
    SQL> ALTER TABLE A_Range
      2    EXCHANGE PARTITION nov_2012
      3    WITH TABLE A_tmp;
    
    Table altered.
    
    SQL> select * from a;
    
    no rows selected
    
    SQL> select * from a_range;
    
             A CREATIOND
    ---------- ---------
             1 23-NOV-12
             1 23-DEC-12
             1 22-JAN-13
    John
  • 2. Re: Trying to convert Interval Partitioned Table to Range..Exchange Partition..
    user8941550 Newbie
    Currently Being Moderated
    Hi,

    Thanks for the answer John.
    Now I am having following things in mind before moving to production.
    DROP TABLE A;
    
    CREATE TABLE A
    (
       a              NUMBER,
       CreationDate   DATE
    )
    PARTITION BY RANGE (CreationDate)
       INTERVAL ( NUMTODSINTERVAL (30, 'DAY') )
       (PARTITION P_FIRST
           VALUES LESS THAN (TIMESTAMP ' 2001-01-01 00:00:00'));
    
    
    INSERT INTO A
         VALUES (1, SYSDATE);
    
    INSERT INTO A
         VALUES (1, SYSDATE - 30);
    
    INSERT INTO A
         VALUES (1, SYSDATE - 60);
    
    
    COMMIT;
    Please replace the Partition names for table A_Range below, by what we get from the query:

    SELECT table_name, partition_name, high_value
    FROM user_tab_partitions
    WHERE table_name = 'A';
    DROP TABLE A_Range;
    
    
    CREATE TABLE A_Range
    (
       a              NUMBER,
       CreationDate   DATE
    )
    PARTITION BY RANGE (CreationDate)
       (PARTITION P_FIRST
           VALUES LESS THAN (TO_DATE ('01-Jan-2001', 'dd-mon-yyyy')),
        PARTITION SYS_P352387
           VALUES LESS THAN (TO_DATE ('29-Nov-2012', 'dd-mon-yyyy')),
        PARTITION SYS_P352386
           VALUES LESS THAN (TO_DATE ('29-Dec-2012', 'dd-mon-yyyy')),
        PARTITION SYS_P352385
           VALUES LESS THAN (TO_DATE ('28-Jan-2013', 'dd-mon-yyyy')),
        PARTITION MAX
           VALUES LESS THAN (MAXVALUE));
    
    SELECT table_name, partition_name, high_value
      FROM user_tab_partitions
     WHERE table_name = 'A_RANGE';
     DROP TABLE A_tmp;
    
    CREATE TABLE A_tmp
    (
       a              NUMBER,
       CreationDate   DATE
    );
    Now I can replace the data from Table A to A_RANGE successfully as:
    BEGIN
       FOR Cur_Partitions IN (SELECT table_name, partition_name, high_value
                                     FROM user_tab_partitions
                                    WHERE table_name = 'A')
        LOOP
          EXECUTE IMMEDIATE
                'ALTER TABLE A
        EXCHANGE PARTITION '
             || Cur_Partitions.partition_name
             || ' WITH TABLE A_tmp';
     EXECUTE IMMEDIATE
                'ALTER TABLE A_RANGE
        EXCHANGE PARTITION '
             || Cur_Partitions.partition_name
             || ' WITH TABLE A_tmp';
       END LOOP;
    END;
    1) I need to run this script in Production. So is there any good Exception Handling I can add to it.
    2) Good thing is number of partitions is not much so I can easily create the partitions with same name in the New Table, but Data Volume is Very High (In Millions or rows)
    Now If this script fails midway..Since it's Alter Statement, so some of the data may have been transferred by replacing some partitions whereas other partitions may remain.
    What can I do to take care of it.

    Thanks.

    Edited by: user8941550 on Jan 23, 2013 1:32 AM
  • 3. Re: Trying to convert Interval Partitioned Table to Range..Exchange Partition..
    user8941550 Newbie
    Currently Being Moderated
    The reason to mark it unanswered is as I need to have some further information before implementing this as mentioned in the post above.

    Thanks..
  • 4. Re: Trying to convert Interval Partitioned Table to Range..Exchange Partition..
    John Spencer Oracle ACE
    Currently Being Moderated
    user8941550 wrote:
    Hi,

    Thanks for the answer John.
    Now I am having following things in mind before moving to production.
    <snip>
    1) I need to run this script in Production. So is there any good Exception Handling I can add to it.
    2) Good thing is number of partitions is not much so I can easily create the partitions with same name in the New Table, but Data Volume is Very High (In Millions or rows)
    Now If this script fails midway..Since it's Alter Statement, so some of the data may have been transferred by replacing some partitions whereas other partitions may remain.
    What can I do to take care of it.

    Thanks.

    Edited by: user8941550 on Jan 23, 2013 1:32 AM
    Well, throughout the process, the data for each partition is gonig to be in one of three places, table a, table a_tmp, or table a_range. So, if there is an error of some kind you will just need to fix the source of the error then determine which partitions have already been exchanged and continue on from there.


    The partition exchange is really only a data dictionary update, ther is no actual data movement involved. When you exchange a partiton with a standalone table, the partiton is unlinked from the partitioned table and renamed to the standalone table name, then the standalone table is linked to the partitioned table and renamed to the "old" partition name.

    John
  • 5. Re: Trying to convert Interval Partitioned Table to Range..Exchange Partition..
    user8941550 Newbie
    Currently Being Moderated
    Thanks John,

    Now the actual situation is:
    Local Index + GLobal Index on PK + GLobal Index on Partitioning Key
    DROP TABLE A;
     
    CREATE TABLE A
    (
       NUM_A              NUMBER,
       CHR_B             Varchar2(10),
       CreationDate   DATE
    )
    PARTITION BY RANGE (CreationDate)
       INTERVAL ( NUMTODSINTERVAL (30, 'DAY') )
       (PARTITION P_FIRST
           VALUES LESS THAN (TIMESTAMP ' 2001-01-01 00:00:00'));
     
     
    INSERT INTO A
         VALUES (1, 'A',SYSDATE);
     
    INSERT INTO A
         VALUES (2,'B', SYSDATE - 30);
     
    INSERT INTO A
         VALUES (3,'C' ,SYSDATE - 60);
     
     
    COMMIT;
    
    --Index 1 is LOCAL
    Create Index Indx_1  on A(CHR_B) LOCAL;
    
    --Index 2
    CREATE UNIQUE INDEX  PK_A ON  A
    (NUM_A);
    
    ALTER TABLE  A ADD (
      CONSTRAINT PK_A
      PRIMARY KEY
      (NUM_A)
      USING INDEX  PK_A);
    
    -- Index 3 is on Partitioning Key
     
    Create Index Indx_2  on A(CreationDate) ;
    Now if I create Target Table as:
    DROP TABLE A_Range;
     
    CREATE TABLE A_Range
    (
       NUM_A              NUMBER,
       CHR_B             Varchar2(10),
       CreationDate   DATE
    )
    PARTITION BY RANGE (CreationDate)
      (partition Nov_2012 values less than (to_date('30-nov-2012', 'dd-mon-yyyy')),
          partition Dec_2012 values less than (to_date('31-dec-2012', 'dd-mon-yyyy')),
          partition Jan_2013 values less than (to_date('31-jan-2013', 'dd-mon-yyyy')),
          partition MAX values less than (MAXVALUE));
    
    
    --Index 1 is LOCAL
    Create Index Indx_New1  on A_Range(CHR_B) LOCAL;
    
    --Index 2
    CREATE UNIQUE INDEX  PK_NewA ON  A_Range
    (NUM_A);
    
    ALTER TABLE  A_Range ADD (
      CONSTRAINT PK_NewA
      PRIMARY KEY
      (NUM_A)
      USING INDEX  PK_NewA);
    
    -- Index 3 is on Partitioning Key
     
    Create Index Indx_New2  on A_Range(CreationDate) ;
    And then Temp Table as :
    DROP TABLE A_TEMP;
     
    CREATE TABLE A_TEMP
    (
       NUM_A              NUMBER,
       CHR_B             Varchar2(10),
       CreationDate   DATE
    );
    Now I need to create same PK on table A_TEMP as both A and A_Range have it.
    Otherwise Oracle throws error while performing Exchange Partition operation.

    Also the Indexes get invalidated after I have successfully performed the Exchange Partition.

    - So what is recommended. Should I perform the operation and then Create the Indexes on the New Table. Anyways It's a New Empty Table to start with so I don't need to have indexes in the begigning.
    OR
    Should I create the Indexes like shown above first in A_Range and then Update the Indexes in the END after I have inserted the data in the end through Exchange Partition.


    Thanks..

    Edited by: user8941550 on Jan 24, 2013 5:01 AM
  • 6. Re: Trying to convert Interval Partitioned Table to Range..Exchange Partition..
    John Spencer Oracle ACE
    Currently Being Moderated
    The exchange table, a_temp, needs to have the same indexes as the partitions do in the two partitioned tables. The global indexes do not matter. If you want to pre-create the PK constraint on a_range, then yes, a_temp will also need to have a PK declared, but without the PK on a_range it will work without a PK on a_temp.

    If you think about how exchange partition works as I explained in my previous post, it is not surprising that the global indexes on a_range get marked unsuable. Without running a create (or rebuild) index statement, how is the global index going to get updatedon what is effectively a data dictionary update?

    If a_range is really a new table, then I would not pre-create the global indexes, I would leave it until after all of the data is there. If you really need to leave the global indexes in place for the exchange, then you have two options. Either do all of the partition exchanges then rebuild then at the end, or rebuild the global indexes ater each partition exchange.

    John
  • 7. Re: Trying to convert Interval Partitioned Table to Range..Exchange Partition..
    user8941550 Newbie
    Currently Being Moderated
    Thanks John,

    So I am implementing this as

    1) Since Global Indexes ae going to be updated in the end. And I have a fresh table 'a_range' to start with, so I will not create the Global Index on 'A_RANGE' in the begining.
    After transferring data through Exchange Partition, I will create the Global Index.
    2) Since PK is also a Global Index, but I will create the PK in A_TEMP and A_RANGE both. So that PK constraint is also there. I can rebuild the PK Index in the end.
    3) And for the Local Index, I will have to keep 'A_TEMP' as UNPARTITIONED as I am doing Exchange Partition on this.
    So I can't create any LOCAL Indexes on this. Therefore I can't avoid Rebuilding the Local Indexes in 'A_RANGE'.
    Thus, I am not creating any Local Indexes also on 'A_RANGE' in the begining. And will create them after exchanging partition as I am doing for the GLobal Indexes.

    Thanks..
  • 8. Re: Trying to convert Interval Partitioned Table to Range..Exchange Partition..
    John Spencer Oracle ACE
    Currently Being Moderated
    user8941550 wrote:
    Thanks John,

    So I am implementing this as

    1) Since Global Indexes ae going to be updated in the end. And I have a fresh table 'a_range' to start with, so I will not create the Global Index on 'A_RANGE' in the begining.
    After transferring data through Exchange Partition, I will create the Global Index.
    2) Since PK is also a Global Index, but I will create the PK in A_TEMP and A_RANGE both. So that PK constraint is also there. I can rebuild the PK Index in the end.
    3) And for the Local Index, I will have to keep 'A_TEMP' as UNPARTITIONED as I am doing Exchange Partition on this.
    So I can't create any LOCAL Indexes on this. Therefore I can't avoid Rebuilding the Local Indexes in 'A_RANGE'.
    Thus, I am not creating any Local Indexes also on 'A_RANGE' in the begining. And will create them after exchanging partition as I am doing for the GLobal Indexes.

    Thanks..
    That sounds fairly reasonable, but you can "move" the local indexes that are already defined on a to a_range as long as you define them on both a_tmp and a_range (before starting the partition exchange). Assuming that you want a_range to have the same local indexes as a, that should save you the time required to build the local indexes on a_range after the exchange. A regular index on a non-partitioned table is equivalent to a local index on a partition, so the two will map directly.

    John
  • 9. Re: Trying to convert Interval Partitioned Table to Range..Exchange Partition..
    rp0428 Guru
    Currently Being Moderated
    >
    PARTITION BY RANGE (CreationDate)
    (partition Nov_2012 values less than (to_date('30-nov-2012', 'dd-mon-yyyy')),
    partition Dec_2012 values less than (to_date('31-dec-2012', 'dd-mon-yyyy')),
    partition Jan_2013 values less than (to_date('31-jan-2013', 'dd-mon-yyyy')),
    partition MAX values less than (MAXVALUE));
    >
    That's an ODD way to partition don't you think?

    Why would you want the last day of each month to be in a different partition than the rest of the month?

    Your 'Nov_2012' partition WILL NOT include 30-nov-2012 if you partition that way. It will only include values 'less than' the partition date.
  • 10. Re: Trying to convert Interval Partitioned Table to Range..Exchange Partition..
    767217 Newbie
    Currently Being Moderated
    If you need only to replace Interval partitioning to range partitioning you could use
    alter table some_table set INTERVAL();

    Btw, what is wrong with Interval partitioning... why do you want to replace it with simple range partitioning?
  • 11. Re: Trying to convert Interval Partitioned Table to Range..Exchange Partition..
    767217 Newbie
    Currently Being Moderated
    .

    Edited by: Alex Smirnov on 25/1/2013 14:04
  • 12. Re: Trying to convert Interval Partitioned Table to Range..
    767217 Newbie
    Currently Being Moderated
    .

    Edited by: Alex Smirnov on 25/1/2013 14:03

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points