13 Replies Latest reply on Jun 25, 2014 7:07 PM by Moazzam

    Split last partition with maxvalue

    870002

      Hi,

       

      I have a table with partition as below:

       

      PARTITION_NAME       HIGH_VALUE

       

      PARTITION_10_2013TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
      PARTITION_11_2013TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
      PARTITION_12_2013TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
      PARTITION_01_2014MAXVALUE

       

      Now all the data from JAN 2014 are occupied in the last partition, so we are planning to split the last partition with month range.

      I have referred few docs and notes and in all the cases the last partition is created with some different name, so it is easier to split the partition starting from name PARTITION_01_2014.

      But in our case the last partition name itself is with PARTITION_01_2014, so how to split the range please help us in how to achieve this.

       

       

      Thanks

        • 1. Re: Split last partition with maxvalue
          Martin Preiss

          not sure if I understand the problem you expect, but you define the names of the partitions that result from the split yourself: http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin002.htm#i1008028. So you can split PARTITION_01_2014 into PARTITION_01_2014 and PARTITION_whatever_you_want.

          • 3. Re: Split last partition with maxvalue
            Hemant K Chitale

            Yes, you can split the partition and reuse/rename the new partitions.

             

            See :

             

             

            SQL>l
              1  create table hkc_test_part (date_col date, text_data_col varchar2(50))
              2  partition by range (date_col)
              3  (partition partition_10_2013 values less than (to_date('2013-11-01','YYYY-MM-DD')),
              4  partition partition_11_2013 values less than (to_date('2013-12-01','YYYY-MM-DD')),
              5  partition partition_12_2013 values less than (to_date('2014-01-01','YYYY-MM-DD')),
              6  partition partition_01_2014 values less than (MAXVALUE)
              7* )
            SQL>/

            Table created.

            SQL>select partition_name, high_value
              2  from user_tab_partitions
              3  where table_name = 'HKC_TEST_PART'
              4  order by partition_position
              5  /

            PARTITION_NAME                 HIGH_VALUE
            ------------------------------ --------------------------------------------------------------------------------
            PARTITION_10_2013              TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
            PARTITION_11_2013              TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
            PARTITION_12_2013              TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
            PARTITION_01_2014              MAXVALUE

            4 rows selected.

            SQL>insert into hkc_test_part values (to_date('2014-01-12','YYYY-MM-DD'), 'Dummy Row');

            1 row created.

            SQL>exec dbms_stats.gather_table_stats('','HKC_TEST_PART');

            PL/SQL procedure successfully completed.

            SQL>select partition_name, num_rows
              2  from user_tab_partitions
              3  where table_name = 'HKC_TEST_PART'
              4  order by partition_position
              5  /

            PARTITION_NAME                   NUM_ROWS
            ------------------------------ ----------
            PARTITION_10_2013                       0
            PARTITION_11_2013                       0
            PARTITION_12_2013                       0
            PARTITION_01_2014                       1

            4 rows selected.

            SQL>
            SQL>alter table hkc_test_part split partition partition_01_2014 at (to_date('2014-02-01','YYYY-MM-DD'))
              2  into (partition partition_01_2014, partition partition_02_2014);

            Table altered.

            SQL>select partition_name, num_rows
              2   from user_tab_partitions
              3   where table_name = 'HKC_TEST_PART'
              4   order by partition_position
              5   /

            PARTITION_NAME                   NUM_ROWS
            ------------------------------ ----------
            PARTITION_10_2013                       0
            PARTITION_11_2013                       0
            PARTITION_12_2013                       0
            PARTITION_01_2014                       1
            PARTITION_02_2014

            5 rows selected.

            SQL>exec dbms_stats.gather_table_stats('','HKC_TEST_PART');

            PL/SQL procedure successfully completed.

            SQL>select partition_name, num_rows
              2    from user_tab_partitions
              3    where table_name = 'HKC_TEST_PART'
              4    order by partition_position
              5    /

            PARTITION_NAME                   NUM_ROWS
            ------------------------------ ----------
            PARTITION_10_2013                       0
            PARTITION_11_2013                       0
            PARTITION_12_2013                       0
            PARTITION_01_2014                       1
            PARTITION_02_2014                       0

            5 rows selected.

            SQL>

             

            Hemant K Chitale

            • 4. Re: Split last partition with maxvalue
              Moazzam

              Try below:

               

              1) First rename the PARTITION_01_2014 partition to some name e.g. p_max:

               

              ALTER TABLE TABLE RENAME PARTITION PARTITION_01_2014 TO p_max;

               

              2) Now split this partition on monthly basis:

               

              ALTER TABLE TABLE SPLIT PARTITION

                    p_max at (TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

                   INTO ( PARTITION PARTITION_01_2014, PARTITION p_max)

                update global indexes;

              • 5. Re: Split last partition with maxvalue
                rp0428
                Now all the data from JAN 2014 are occupied in the last partition, so we are planning to split the last partition with month range.

                Why do you want to do that? You will STILL have a MAXVALUE partition - see Hemants' example. That means you will have to keep splitting that partition from now on whenever you get to whatever that boundary is.

                 

                Why do you need a MAXVALUE partition? If a user accidentally provides the wrong partition key Oracle will just put the row into that MAXVALUE partition as if nothing is wrong. Users may not even notice the data is in the wrong partition.

                 

                You also need to enable row movement so you can move those 'wrong' rows to the correct partition.

                 

                You might consider redefining the table to use monthly interval partitioning so that you don't need to create new partitions manually. That still allows you to use a 'special' partition value (e.g. '01/01/4000') for values that you want to keep separate. But you won't need to split that partition to create partitions in the middle since those partitions will be done automatically for you.

                • 6. Re: Split last partition with maxvalue
                  Hemant K Chitale

                  > If a user accidentally provides the wrong partition key Oracle will just put the row into that MAXVALUE partition as if nothing is wrong

                  Careful there !   Which partition Oracle puts the row into depends on the partition key value versus the various partition boundaries, which means that it might go into a "lower" partition, not the MAXVALUE partition.

                   

                  Hemant K Chitale

                  • 7. Re: Split last partition with maxvalue
                    rp0428

                    By wrong partition key I mean a data entry error that uses an incorrect value for an existing partition.

                     

                    OP only has three specified partitions. Any value higher than 2014 will go to the MAXVALUE partition even if it was intended for one of the other three.

                     


                    • 8. Re: Split last partition with maxvalue
                      Hemant K Chitale

                      >Any value higher than 2014 will go to the MAXVALUE partition

                      True.

                      >a data entry error

                      At the same time, any value lower than '2013-11-01'  would go into PARTITION_10_2013  .  Thus entering the Year as 2012 (for any month) would cause the row to go into PARTITION_10_2013.  Entering the Year as 2013 but the month as 09 would still have the row go into PARTITION_10_2013.

                       

                      Hemant K Chitale

                      • 9. Re: Split last partition with maxvalue
                        rp0428

                        Yes but MAXVALUE partitions are particularly problematic since they basically collect 'garbage'. That is very different than INTERVAL partitioning where Oracle will create the partition for the data if the partition doesn't exist.

                         

                        IMHO it is generally a better design to NOT use MAXVALUE and to force the use of a specific value to represent 'unknown' such as '01/01/4000'. Obviously there could be an end case where future dates are really used and MAXVALUE is needed. We had one at a major bank I worked at in the online bill pay application.

                         

                        If you use your account to schedule a payment to some vendor for next Thursday that payment request would go into the MAXVALUE partition. Each night the batch process would process payments scheduled for that night - these would be in the MAXVALUE partition and, by changing the partition key, be moved to the proper partition for payment processing.

                        • 10. Re: Split last partition with maxvalue
                          870002

                          Thank you,

                           

                          I renamed the last partition PARTITION_01_2014 into PARTITION_MAXVALUE and then splitted the partition with date range.

                          • 11. Re: Split last partition with maxvalue
                            870002

                            Now I'm planning to implement this production database, so could you please assist me on the points which I need to be cautious during partition split.

                            Like pre and post check.

                             

                            Thanks in advance!

                            • 12. Re: Split last partition with maxvalue
                              Hemant K Chitale

                              The renamed PARTITION_MAXVALUE partition may have data (rows) for multiple months (Jan, Feb, ... Jun).  SPLITting it would require Oracle to read all the rows and then write the targetted month rows to a new partition.  So you have to estimate the I/O and time required.

                               

                               

                              Hemant K Chitale

                              • 13. Re: Split last partition with maxvalue
                                Moazzam

                                As global indexes shall be rebuilt as part of this process, so this process of splitting may take time if global index is huge or there are many such indexes on the table.