1 2 Previous Next 19 Replies Latest reply on May 27, 2019 11:49 AM by Ken18

    Add Sub-partitions

    Ken18

      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_name
      modify 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).

        • 2. Re: Add Sub-partitions
          Ken18

          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)

          • 3. Re: Add Sub-partitions
            John Thorton

            Ken18 wrote:

             

            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)

            why?

            What problem will sub partition solve?

            • 4. Re: Add Sub-partitions
              Jonathan Lewis

              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

              • 5. Re: Add Sub-partitions
                Ken18

                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,

                alter table PART_TEST 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;

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

                ALTER TABLE PART_TEST MODIFY PARTITION P1 ADD SUBPARTITION SP1 VALUES ('1') tablespace TEST_PART;

                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

                • 6. Re: Add Sub-partitions
                  Jonathan Lewis

                  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

                  • 7. Re: Add Sub-partitions
                    Ken18

                    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

                    • 8. Re: Add Sub-partitions
                      Donatello Settembrino

                      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

                      • 9. Re: Add Sub-partitions
                        Ken18

                        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.

                        • 10. Re: Add Sub-partitions
                          Donatello Settembrino

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

                           

                          Regards,

                          DS

                          • 11. Re: Add Sub-partitions
                            Jonathan Lewis

                            If space is the biggest issue then (quick thumbnail sketch).

                             

                            Convert the partitioned table into a composite partitioned table with one subpartition per partition by using an EXCHANGE strategy

                            Split each partition in turn - and 12.2 can split one partition into multiple subpartitions in one command.

                            Change the subpartition template to handle future partitions.

                             

                            This avoids the need of ever having two copies of the data anywhere. (Otherwise for 12.2 you can do an "alter table modify" to convert the partitioned table to composite partitioned but that gives you two copies and a journal and a load of duplicated index space). [Update: My error - simple partitioned to composite partitioned only appeared in 18c and there's a bug that needs the user to have a quota on the SYSTEM tablespace. This is fixed by 19.2]

                             

                            Quick example:

                            create table pt_range (

                                    id              number(8,0)     not null,

                                    grp             varchar2(1)     not null,

                                    small_vc        varchar2(10),

                                    padding         varchar2(100)

                            )

                            partition by range(id) (

                                    partition p200 values less than (200),

                                    partition p400 values less than (400),

                                    partition p600 values less than (600)

                            )

                            ;

                             

                             

                            insert into pt_range

                            select

                                    rownum-1,

                                    mod(rownum,2),

                                    lpad(rownum,10,'0'),

                                    rpad('x',100,'x')

                            from

                                    all_objects

                            where

                                    rownum <= 600

                            ;

                             

                             

                            commit;

                             

                             

                             

                            create table pt_range_list (

                                    id              number(8,0)     not null,

                                    grp             varchar2(1)     not null,

                                    small_vc        varchar2(10),

                                    padding         varchar2(100)

                            )

                            partition by range(id)

                            subpartition by list (grp)

                            subpartition template (

                                    subpartition p_def      values(default)

                            )

                            (

                                    partition p200 values less than (200),

                                    partition p400 values less than (400),

                                    partition p600 values less than (600)

                            )

                            ;

                             

                             

                            create table t for exchange with table pt_range;

                             

                             

                             

                            alter table pt_range exchange partition p200 with table t;

                            alter table pt_range_list exchange subpartition p200_p_def with table t;

                             

                             

                            alter table pt_range exchange partition p400 with table t;

                            alter table pt_range_list exchange subpartition p400_p_def with table t;

                             

                             

                            alter table pt_range exchange partition p600 with table t;

                            alter table pt_range_list exchange subpartition p600_p_def with table t;

                             

                             

                            When you do the exchange you'll have to decide what to do about indexes and validation, of course, but for a quick "no-users" job I probably wouldn't maintain any global indexes I didn't have to, but I would take the local indexes into the exchansge, then rebuild the global indexes at the end.

                             

                             

                             

                            Regards

                            Jonathan Lewis

                            1 person found this helpful
                            • 12. Re: Add Sub-partitions
                              Ken18

                              Hi Sir,

                              Thank you very much for your precious time and for the detailed steps.

                              It really helped me, but at last step - EXCHANGE subpartition it fails (error states column type or size mismatch).

                               

                              (1) My existing table is IMAGE and i converted it using below to range partitioned table.

                               

                              alter table IMAGE_RANGE

                              modify PARTITION BY RANGE (CREATEDATE)

                              (

                              PARTITION p1 VALUES LESS THAN (TO_DATE('22-MAY-2019', 'DD-MON-YYYY')),

                              PARTITION p2 VALUES LESS THAN (TO_DATE('23-MAY-2019', 'DD-MON-YYYY'))

                              );

                               

                              (2)   Created Range-List subpartitioned table using  IMAGE Table DDL SQL,

                               

                              CREATE TABLE  IMAGE_RANGE_LIST"

                                 ( "ID" NUMBER(20,0) NOT NULL ENABLE,

                              "VERSION" NUMBER(38,0) NOT NULL ENABLE,

                              "CLASSID" NUMBER(38,0),

                              "ITEMID" NUMBER(20,0),

                              "ORGID" NUMBER(20,0),

                              "CREATEDATE" TIMESTAMP (6) NOT NULL ENABLE,

                              "LASTMODIFYDATE" TIMESTAMP (6) NOT NULL ENABLE,

                              "TYPECODE" NUMBER(38,0) NOT NULL ENABLE,

                              "ROTATION" NUMBER(38,0) NOT NULL ENABLE,

                              "CLASSIFICATIONNUMBER" NUMBER(38,0),

                              "SEQUENCENUMBER" NUMBER(38,0),

                              "DATA" BLOB,

                              "DATALEGACY" BLOB,

                              "SITEID" NUMBER(1,0) NOT NULL ENABLE,

                              "IMAGEPROCESS" VARCHAR2(32 BYTE),

                              "PURGEPROFILE" NUMBER(38,0) NOT NULL ENABLE,

                              "PURGEID" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE

                                 )

                              PARTITION BY RANGE(createdate)

                                SUBPARTITION BY LIST(PURGEID)

                                SUBPARTITION TEMPLATE

                                (

                                  SUBPARTITION s1 values(default)

                                )

                              (

                                   PARTITION p1 VALUES LESS THAN (TO_DATE('22-MAY-2019', 'DD-MON-YYYY')),

                                  PARTITION p2 VALUES LESS THAN (TO_DATE('23-MAY-2019', 'DD-MON-YYYY'))

                              )   TABLESPACE TEST_PART enable row movement;

                               

                               

                              (3) Created the interim table,

                               

                              create table IMAGE_T  TABLESPACE TEST_PART for exchange with table IMAGE_RANGE;

                               

                              (4)  Now when I tried to use exchange partition, for subpartition it fails saying column type or size mismatch.

                               

                               

                               

                              NOTE: I have thoroughly checked the columns and data types and other details, the tables have 17 columns with the same attributes.

                              Could it be a generic error by any chance? or am I missing anything?

                               

                              Kindly shed some light.

                              Thank you.

                              • 13. Re: Add Sub-partitions
                                AndrewSayer

                                This is exactly why Oracle created the create table for exchange with syntax. Are you able to create another table using this syntax and then convert it into a composite partitioned table? I've not tried this but I don't see it being ruled out based on what's been said here.

                                 

                                Otherwise:

                                Are the columns in the exact same order?

                                Are you making sure you are comparing using the same length semantics (bytes vs chars)?

                                Are you making sure columns have the same nullability?

                                Are there unused columns in the original table? (select * from dba_unused_col_tabs)

                                Share the DDL using dbms_metadata for both tables, we only need the column lists so don't paste in the huge chunk that follows it.

                                • 14. Re: Add Sub-partitions
                                  Jonathan Lewis

                                  You've run into a variation on a common problem that I'll be writing about in the next few days.

                                   

                                  Over time a table can end up with all sorts s of strange bits and pieces of history so that when you describe it and try to create a copy of it the stuff that ends up in the data dictionary for the copy doesn't match the original - even though to the external view things look identical. This is why Oracle introduced the "create table for exchange.." option.

                                   

                                  It's probably the case that your range/list definition is internally inconsistent with the range table, so try the following

                                   

                                  a) create a simple table for exchange

                                  b) modify the table to become a range/list partitioned table

                                  c) create another table for exchange

                                   

                                  You should now be able to exchange from the original to the second (c) table, then from the second (c) table to the range/list (a/b) table.

                                   

                                  e.g. - allowing for typos:

                                   

                                  create table image_range_list for exchange with table image_range;

                                   

                                   

                                  alter table image_range_list  modify

                                  PARTITION BY RANGE(createdate)

                                    SUBPARTITION BY LIST(PURGEID)

                                    SUBPARTITION TEMPLATE

                                    (

                                      SUBPARTITION s1 values(default)

                                    )

                                  (

                                       PARTITION p1 VALUES LESS THAN (TO_DATE('22-MAY-2019', 'DD-MON-YYYY')),

                                      PARTITION p2 VALUES LESS THAN (TO_DATE('23-MAY-2019', 'DD-MON-YYYY'))

                                  )

                                  ;

                                   

                                  create table image_simple for exchange with table image_range;

                                   

                                  alter table image_range exchange partition p1 with table image_simple;

                                  alter table image_range_list exchange partition p1 with table image_simple;

                                   

                                  etc.

                                   

                                  Don't forget you have to allow for indexes (if you have any) and there is a "validation" clause - so it's just occured to me that the error message you were getting might have been a little misleading and something to do with an indexing difference between image_range and image_range_list, so (e.g.)

                                   

                                  alter table image_range exchange partition p1 with table image_simple excluding indexes without validation;

                                   

                                   

                                  Regards

                                  Jonathan Lewis

                                  1 2 Previous Next