Forum Stats

  • 3,851,558 Users
  • 2,263,999 Discussions
  • 7,904,779 Comments

Discussions

Add Sub-partitions

Ken18
Ken18 Member Posts: 141 Red Ribbon
edited May 27, 2019 7:49AM in General Database Discussions

I used the below to create a partitioned table ,

alter table Table_name modify PARTITION BY RANGE (CREATEDATE)(PARTITION p1 VALUES LESS THAN (TO_DATE('15-MAY-2019', 'DD-MON-YYYY')),PARTITION p2 VALUES LESS THAN (TO_DATE('16-MAY-2019', 'DD-MON-YYYY')),PARTITION p3 VALUES LESS THAN (maxvalue)) online;

I tried the something like below,

1)

ALTER TABLE  mytable_name   MODIFY PARTITION P1 ADD SUBPARTITION SP1  VALUES ('0');

Error : Ensure that the table is partitioned by Composite Range method

2)

alter table mytable_namemodify PARTITION BY RANGE (CREATEDATE)SUBPARTITION BY LIST (PID)subpartition template(SUBPARTITION SP1 values (0),SUBPARTITION SP2 values (1),SUBPARTITION SP3 values (2),SUBPARTITION SP4 values (3));

Both doesn't work.

Can someone help me with correct way to add sub-partitions to an existing partitioned table (Range-List).

Ken18AndrewSayer

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,054 Blue Diamond
    edited May 27, 2019 5:05AM Answer ✓

    Re-read the thread.

    Note particularly my first quick thumbnail sketch of actions:

    Convert the partitioned table into a composite partitioned table with one subpartition per partition by using an EXCHANGE strategySplit each partition in turn - and 12.2 can split one partition into multiple subpartitions in one command.

    If you want to transfer the data from a composite partitioned table to a simple partitioned table don't you think you will have to do something to reverse the "split partition"  before you do the exchanges ?

    See also: https://jonathanlewis.wordpress.com/2019/05/23/re-partitioning/

    Regards

    Jonathan Lewis

«1

Answers

  • Ken18
    Ken18 Member Posts: 141 Red Ribbon
    edited May 16, 2019 7:49AM

    Of-course i understand,

    1) how to convert normal table to partitioned table

    2) how to convert normal table to composite range-list partition [creates partitions and sub-partitions]

    But i want to add sub-partitions (using list) to a table which already has partitions (range on createdate)

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 16, 2019 8:03AM
    Ken18 wrote:Of-course i understand,1) how to convert normal table to partitioned table2) how to convert normal table to composite range-list partition [creates partitions and sub-partitions]But i want to add sub-partitions (using list) to a table which already has partitions (range on createdate) 

    why?

    What problem will sub partition solve?

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,054 Blue Diamond
    edited May 16, 2019 9:39AM

    You can't add subpartiitons to a table that is not already composite partitioned.

    If you know how to convert a normal table to a partitioned table you can extend the method to convert a simple partitioned table to a composite partitioned table.

    Regards

    Jonathan Lewis

    Ken18
  • Ken18
    Ken18 Member Posts: 141 Red Ribbon
    edited May 21, 2019 4:55AM

    Thank you very much, It is great to receive a reply from Oracle guru like you.

    Thanks for the abundant info, I often go through your blogs when i need help.

    Actually I am looking to,

    Convert Pre-exisiting Partition tables (range) to be sub-partitioned (composite range-list)

    1) As part of testing, Range Partitioned existing table using the below,

    <span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">alter</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">table</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> PART_TEST modify </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">PARTITION</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">BY</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> RANGE </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">CREATEDATE</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">PARTITION</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> p1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">VALUES</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LESS THAN </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">TO_DATE</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'15-MAY-2019'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'DD-MON-YYYY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">PARTITION</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> p2 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">VALUES</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LESS THAN </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">TO_DATE</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'16-MAY-2019'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'DD-MON-YYYY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">PARTITION</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> p3 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">VALUES</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LESS THAN </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">maxvalue</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">))</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> online</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span>

    2) Then tried to alter the table to add sub-partitions (list) using the below,

    <span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ALTER</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">TABLE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> PART_TEST MODIFY </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">PARTITION</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> P1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ADD</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> SUBPARTITION SP1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">VALUES</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'1'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> tablespace TEST_PART</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span>

    But I see this below error,

    Error report - ORA-14253: table is not partitioned by composite partition method 14253. 00000 - "table is not partitioned by Composite Range method" *Cause: The table in a subpartition maintenance operation (ALTER TABLE EXCHANGE/MODIFY/MOVE/TRUNCATE SUBPARTITION, or ALTER TABLE MODIFY PARTITION ADD/COALESCE SUBPARTITION command must be partitioned by Composite Range method *Action: Ensure that the table is partitioned by Composite Range method

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,054 Blue Diamond
    edited May 21, 2019 5:27AM

    What version of Oracle are you on ?

    Do you want to do this with virtually no interruption to end-users  - or do you have a reasonable time-window when you can deny access to the database ?

    Regards

    Jonathan Lewis

  • Ken18
    Ken18 Member Posts: 141 Red Ribbon
    edited May 21, 2019 6:57AM

    Hi Sir,

    I am on testing this on Oracle 12c,  I am working on prototype.

    We intend to use these Oracle SQL scripts within our application, when user migrates from current application version to latest.. the existing partitions should have been sub-partitioned.

    So there is a reasonable time window, but i would be keen to know both ways with no interruption to end-users as well.

    And also would like to know on how to reverse migrate, sub-partitioned tables back to partitioned.

    I mean when user is reverse migrating to  previous version from latest , the data from all of the sub-partitions within each partition is moved to be at the partition level and no sub-partitions are left in the database

  • Donatello Settembrino
    Donatello Settembrino Member Posts: 121 Blue Ribbon
    edited May 21, 2019 7:38AM

    Hello,

    some important information is missing (eg how big the table is) but if you have space and a time window without giving overhead to your system, in your place I'd do a CTAS with the partitioning/subpartitioning scheme you indicated in nologging + parallel mode.
    At the end of the operations (at the right time) do the rename of the copy table with the new structure and gathered the statistics. The disservice time lasts only the time of rename table but you can do everything when and how you want.

    Obviously with the same principle you can create the sql script for rollback operations.

    Regards,

    DS

  • Ken18
    Ken18 Member Posts: 141 Red Ribbon
    edited May 21, 2019 8:05AM

    Thanks DS, but CTAS is not ideal for me as my schema is around 200GB (with BLOBS).

    Can you shed somelight with any other alternatives if you could, like can we use exchange partitions or similar to convert parition tables to be sub-partitioned and revert back to partitioned table from sub-partitioned.

    I am looking to Convert pre-existing Partitioned (range) table - to sub-partitioned (composite range-list)

    Below is what I am tasked with,

    Scenario - 1: ==> I want to have my existing partitions(range) subpartitioned (list) so that my old data is usable. The data in each of my partitions have to be moved to a sub-partition,

    Scenario - 2: Then revert back to partition level (reverse migrate), ==> I mean the data from all of the sub-partitions within each partition is moved to be at the partition level and no sub-partitions are left in the database.

    Is it possible and is there a way to do so ? If so how to implement the above scenarios - convert existing partitions(range) to sub-partitioned (Range-list) - and how to move back the data from sub-partitions to each of its partition back and no-sub partitions are left.

    Thanks a ton for your time and help in this regards.

  • Donatello Settembrino
    Donatello Settembrino Member Posts: 121 Blue Ribbon
    edited May 21, 2019 8:52AM

    have you tried to take a look at the dbms_redefinition package?

    Regards,

    DS