8 Replies Latest reply on Jun 16, 2014 3:56 PM by rp0428

    Best way to split big partition to few smaller

    cardel

      Hello,

      I have Oracle 11.2 EE on Redhat 5.9 and I need to solve problem with partitioning.

      Few tables of some system was prepared for partitioning few years ago. But no partitioning was made and all of these tables/global indexes have only one partition for all data.

      Now we have many tables and indexes with only one partition (with MAXVALUE limit) for all data. I would like to split this big partition to more smaller partitions by quarter of year.

      Example:

      Existing partition D0201_2008_1Q should be split to D0201_2008_2Q, D0201_2008_3Q ... MYTABLE_2014_4Q ... by DATE/NUMBER column

       

      I tried to generate script for splitting partitions

       

      ALTER INDEX I_D0201 MODIFY DEFAULT ATTRIBUTES TABLESPACE INDX_2008_1Q;

      ALTER TABLE D0201 SPLIT PARTITION D0201_2008_1Q AT (1000456)

      INTO (PARTITION D0201_2008_XX TABLESPACE DATA_2008_1Q , PARTITION D0201_MAX1) PARALLEL 16;

      ALTER TABLE D0201 MODIFY PARTITION D0201_2008_XX REBUILD UNUSABLE LOCAL INDEXES;

      ALTER INDEX I_D0201 MODIFY DEFAULT ATTRIBUTES TABLESPACE INDX_2008_2Q;

      ALTER TABLE D0201 SPLIT PARTITION D0201_MAX1 AT (1000547)

      INTO (PARTITION D0201_2008_2Q TABLESPACE DATA_2008_2Q , PARTITION D0201_MAX2) PARALLEL 16;

      ALTER TABLE D0201 MODIFY PARTITION D0201_2008_2Q REBUILD UNUSABLE LOCAL INDEXES;

      ALTER INDEX I_D0201 MODIFY DEFAULT ATTRIBUTES TABLESPACE INDX_2008_3Q;

      ALTER TABLE D0201 SPLIT PARTITION D0201_MAX2 AT (1000639)

      INTO (PARTITION D0201_2008_3Q TABLESPACE DATA_2008_3Q , PARTITION D0201_MAX3) PARALLEL 16;

      ALTER TABLE D0201 MODIFY PARTITION D0201_2008_3Q REBUILD UNUSABLE LOCAL INDEXES;

      ...

       

      It split big partition to two new partitions. One of these is next quarter and second will be split again.

       

      Some partitions have few GB and splitting takes a very long time (hours for one partition split) and big free disc space is also required.

       

      New partitions will be smaller, but size of first partition 2008_1Q will be unchanged and I will need to reclaim unused space somehow.

       

      Do you have some ideas better/faster solution?

        • 1. Re: Best way to split big partition to few smaller
          Ramin Hashimzadeh

          Size of table?

          Size of partition?

          did you read about DBMS_REDEFINITION ?

           

          ----

          Ramin Hashimzade

          • 2. Re: Best way to split big partition to few smaller
            Salman Qureshi

            Hi,

            Get some down time for each table and do datapump export and then recreate the tables with partitions and then do the import.

            If your table has a size of 10G and high-end storage, I can expect that whole task can finish between 30 - 40 minutes (give it a try in test environment first). This should be the fastest way to perform this task.

             

            If you don't have down time, the best way is to go for online table redefinition - new tables/indexes would be partitioned and also compact.

             

            Salman

            1 person found this helpful
            • 3. Re: Best way to split big partition to few smaller
              cardel

              I have used DBMS_REDEFINITION once for change non-partitioned table to partitioned. But now I have existing partitioned table with only one partition and I want to do some simple process to split it.

              DBMS_REDEFINITION or EXPDP/IMPDP can be faster for execution, but time consuming for preparation. I have aprox. 60 tables with some local and global indexes.

              • 4. Re: Best way to split big partition to few smaller
                Salman Qureshi

                Just analyze - if (time to prepare + time to execute) < time to split partitions - then go for it. Redefining would also save you a lot of space in the end

                 

                Salman

                • 5. Re: Best way to split big partition to few smaller
                  Ramin Hashimzadeh

                  cardel wrote:

                   

                  I have used DBMS_REDEFINITION once for change non-partitioned table to partitioned. But now I have existing partitioned table with only one partition and I want to do some simple process to split it.

                  DBMS_REDEFINITION or EXPDP/IMPDP can be faster for execution, but time consuming for preparation. I have aprox. 60 tables with some local and global indexes.

                  with DBMS_REDEFINITION you don't have any downtime.

                  ORACLE-BASE - Online Table Redefinition Enhancements in Oracle Database 10g Release 1

                  DBMS_REDEFINITION.sync_interim_table


                  ----

                  Ramin Hashimzade


                  • 6. Re: Best way to split big partition to few smaller
                    rp0428

                    I have Oracle 11.2 EE on Redhat 5.9 and I need to solve problem with partitioning.

                    Few tables of some system was prepared for partitioning few years ago. But no partitioning was made and all of these tables/global indexes have only one partition for all data.

                    Now we have many tables and indexes with only one partition (with MAXVALUE limit) for all data. I would like to split this big partition to more smaller partitions by quarter of year.

                    Why?

                     

                    What PROBLEM are you trying to solve?

                     

                    All I hear you saying is that the change to partitioning is "because I want to". You didn't tell us even ONE reason why you suddenly NEED to partition after all of these years.

                     

                    You should first identify an actual PROBLEM that needs to be addressed.

                     

                    Then you should look at potential solutions.

                     

                    Are you trying to make maintenance easier? Well those global indexes, partitioned or not, may just get in the way.

                     

                    I suggest that you first document your actual requirements and actual goals. Then if partitioning can help with that pursue partijtioning once you know what the ultimate goal is that you are trying to reach.

                     

                    One partitioning solution that is often a good first approach is to simply modify a partitioned table that has a MAXVALUE partition to use an explicit partition boundary instead. For example the table would have ONE partition with a boundary of 'July 1, 2014'. Then you can modify the existing table with a data dictionary entry.

                     

                    Then once you have eliminated the MAXVALUE partition you can begin creating new empty partitions for FUTURE data: Aug 2014, Sep 2014, etc.

                     

                    That approach lets you start using partitions for NEW data, which is generally more important and useful than the older data. It also lets you split off any older data that might really be important. For example you could create a new partition for Jan 2014 if you need to.

                     

                    Without knowing what the goal of partitioning is we can't really help you with any specifics.

                    • 7. Re: Best way to split big partition to few smaller
                      cardel

                      We have many big tables in our system with millions of records and sizes around few GB. Tables were prepared for partitioning, but no partitioning was made. So we would like to solve actual performance problems that are caused by reading data from one big table partition and working with nonpartitioned indexes.

                      I will try DBMS_REDEFINITION. Thanks

                      • 8. Re: Best way to split big partition to few smaller
                        rp0428
                        We have many big tables in our system with millions of records and sizes around few GB. Tables were prepared for partitioning, but no partitioning was made. So we would like to solve actual performance problems that are caused by reading data from one big table partition and working with nonpartitioned indexes.

                         

                         

                        But you haven't posted ANY information about any 'performance problems'.

                         

                        The method and type of partitioning will depend on WHAT data your queries typically use and HOW they use that data.

                         

                        If older data is not often used there is little to gain by partitioning ALL of the older data. That is why I briefly discussed first preparing the table properly for partitioning (which can be done quickly with little impact) and then  focusing on the NEWER data.

                         

                        You need to perform testing to determine that best partitioning strategy for your tables and especially the indexes.

                         

                        I stronglyl recommend NOT using DBMS_REDEFINITION until you have tested and know, for certain, that the partitioning strategy you use is the correct one.

                         

                        Using that method on a large table to completely redefine ALL data can have significant impact on the performance of your system during the process. That is certainly the appropriate method to use if the operation needs to be done online but you can't afford to make a mistake and use that method until you are sure you have designed the proper partitioning.