10 Replies Latest reply: Dec 7, 2012 11:56 AM by rp0428 RSS

    Which is the Best method to Split Partition

    949291
      Hello,

      Could you please suggest, which is the best and less impacted and fast method to split partition ?
      As of i know Following are there but i am not sure which is the best one.
           
           1.Normal Split partition
           2.Normal Split partition with alter index     
           3.Exchange table + split
           4.dbms_redefination
           5. fast split.

      I have oracle 11.2.0.2.0 version db on linux machine.

      Thanks in advance,
      Sachin
        • 1. Re: Which is the Best method to Split Partition
          IamHariKrishna
          Sachine -

          Normal partition method with 'update indexes' clause is the best method for critical prod dbs. We are following this method from past 2 years and don's any issues with data & index too.

          Thanks
          • 2. Re: Which is the Best method to Split Partition
            IamHariKrishna
            Sachine -

            Normal partition method with 'update indexes' clause is the best method for critical prod dbs. We are following this method from past 2 years and dont's see any issues with data & index too.

            Thanks
            • 3. Re: Which is the Best method to Split Partition
              949291
              Hi Harry ,

              Goal:- I have a partition of more than 200 gb and i wanted to split into 6 partition.

              Here is my understanding

              Oracle Database implements a SPLIT PARTITION operation by creating two new partitions and redistributing the rows from the partition being split into the two new partitions. This is an expensive operation because it is necessary to scan all the rows of the partition being split and then insert them one-by-one into the new partitions. Further if you do not use he UPDATE INDEXES clause, both local and global indexes also require rebuilding.

              We have following different way by which we can split partition.
              1.     Split Operation (which Bank DBA suggested)
              a.     We can use this option however while splitting data into different partition it makes local and global index unusable that might be affect on live production system (i.e if u are not taking any downtime)
              2.     Split Operation with update index (UPDATE INDEXES PARALLEL n):-
              a.     We can use this option to split partition on live production system because it is not making any index unusable however it takes time to complete split operation. I would suggest if you are not taking any down time for this operation then go with this option.
              3.     Split Operation with update index and exchange partition method:-
              a.     We can use this method only when we have privileges to take downtime and partition size is considerable(like <100gb).
              4.     Table Redefinition :-
              a.     We can use this for online redefinition however it will apply for whole table so we shouldn’t go for this option.
              5.     Fast Split Operation.
              a.     It split partition into two and among of these one of the partition become completely empty. As this is not our goal so we should not go for this option
              • 4. Re: Which is the Best method to Split Partition
                rp0428
                >
                Goal:- I have a partition of more than 200 gb and i wanted to split into 6 partition.

                Here is my understanding

                Oracle Database implements a SPLIT PARTITION operation by creating two new partitions and redistributing the rows from the partition being split into the two new partitions. This is an expensive operation because it is necessary to scan all the rows of the partition being split and then insert them one-by-one into the new partitions. Further if you do not use he UPDATE INDEXES clause, both local and global indexes also require rebuilding.
                >
                Now you are describing a slightly different problem.

                You still haven't provide one key piece of information: how many partitions are there now?

                If you have 100 partitions and just want to split one of them into 6 that could be different than if you just have 4 and want to split one of them into 6.
                >
                4. Table Redefinition :-
                a. We can use this for online redefinition however it will apply for whole table so we shouldn’t go for this option.
                >
                Yes and (maybe) no. Yes it applied to the whole table but your use case raises an interesting question that I don't know the answer to.

                'Can Oracle simply use existing partitions that will be used 'as is' in the newly redefined table?' - I would hope so but I haven't test that.

                If a table has partitions A, B, C, D and you redefine the table to have partitions A, B, C, D, E, F (i.e. you are splitting D into D, E, F) and the partition definitions for partitions A, B and C are unchanged does Oracle still copy all of the data for those partitions?

                That might be worth testing. If the those partitions are being redefined to use the same tablespace they use now Oracle may be smart enough to recognize that and just copy the metadata. They would have to be copied if a new tablespace was being used for them.

                Can you provide actual information for:
                1. How many partitions currently exist?
                2. How many of those partitions will be unchanged?
                3. Will the unchanged partitions have the same tablespace after the split operation?
                • 5. Re: Which is the Best method to Split Partition
                  user5066799
                  Hello Sachine,

                  If you need to split one partition to N partitions then, if using alter table .. split partition, N - 1 iterations will be needed with scanning / reinserting the part of original partition's data, starting with whole data, decreasing each time, but still the whole volume to be about ~ (N - 1) * original partition size / 2. As well as for merge several partitions at once, seems no syntax for splitting one partition into several ones at one time.
                  We use merging several partitions at once in our DWH, and were forced to switch from N - 1 merge iterations approach to approach when we insert at once all the data from all partitions to be merged into additional table, then truncate original table partitions, then merge them (which takes fast as they are empty), then exchange merged partition with additional table. Of course, there is some critical moment of data unavailability after truncate but before finishing exchange partition, but this is tolerable in our case.
                  For your case, probably inserting to several separate additional tables of similar structure dependently on partitioning key value (insert all clause ?), then truncating original partition, then splitting it into needed number of partitions, then going through resulting partitions and exchanging them with corresponding additional tables would work. But need to estimate the risk of the critical moment between truncate and finalizing last exchange partition.
                  Such a complicated approach is really needed if number of partitions to split to is high enough to be forced to avoid N - 1 scanning of the whole data to implement all these things...

                  Probably, by "Exchange table + split" you meant something similar in the way you first exchange your big partition with some additional empty table, then fast-split empty partition (fast even with N-1 iterations as empty), ... but then again there's a need to return data back to table, which is probably only possible again by insert select from the additional table, probably with APPEND and PARALLEL hints (with some limited degree of parallelism if you have "deeply-online" system).

                  Edited by: user5066799 on Dec 6, 2012 8:55 AM

                  Edited by: user5066799 on Dec 6, 2012 9:02 AM
                  • 6. Re: Which is the Best method to Split Partition
                    rp0428
                    >
                    Hello Sachine,
                    >
                    I'm not Sachine. In the future try to remember to click the 'reply' button next to the reply you are actually responding to.
                    • 7. Re: Which is the Best method to Split Partition
                      user5066799
                      Yes, sorry, wanted to reply to topic starter, but seems can't edit in my post the post I reply to or remove + repost...
                      • 8. Re: Which is the Best method to Split Partition
                        user5066799
                        Continuing my post mistakenly addressed as a reply not directly to you... More exactly, to the formula of how much data would be scanned | reinserted in iterations: if S is original size of partition, N is number of partitions you want to split to (let us assume equivalent size), the total amount scanned would be S * (N + 2) * (N - 1) / (2 * N). For example, to split 200G partition into four 50G partitions, you would first split to 150G + 50G scanning / inserting 200G, then split 150G to 100G and 50G, scanning / inserting 150G, then split 100G to 50G and 50G, scanning / inserting 100G, totally scanning / inserting 200 + 150 + 100 = 450G. The same result gives the formula 200 * (4 + 2) * (4 - 1) / (2 * 4) = 200*6*3/8 = 200*9/4 = 450G.

                        Btw, in the inverse problem of merging several partitions, there are cases when repeating regular scheduled process using only two partitions merge at a time would help (to avoid merging all at once), but don't know about splitting case (and you have just one single task, don't you?).

                        Also, if we assume dbms_redefinition gets a full copy of the whole table, but the partition you want to split is the biggest part of the table, from the point of view of scanning / reinserting that could be probably faster then N - 1 iterations also. But still if "the remainder" of the table is rather big, the approach proposed by me would scan less data.
                        • 9. Re: Which is the Best method to Split Partition
                          949291
                          Hello,

                          Here is the answer of your Questions.


                          1. How many partitions currently exist?
                          Answer :- Currently 12 partition are there.
                          2. How many of those partitions will be unchanged?
                          Answer :- I have one partition of more than 200gb and i wanted to split in six different partition.
                          3. Will the unchanged partitions have the same tablespace after the split operation?
                          Answer :- Yes
                          • 10. Re: Which is the Best method to Split Partition
                            rp0428
                            >
                            Here is the answer of your Questions.

                            1. How many partitions currently exist?
                            Answer :- Currently 12 partition are there.
                            2. How many of those partitions will be unchanged?
                            Answer :- I have one partition of more than 200gb and i wanted to split in six different partition.
                            3. Will the unchanged partitions have the same tablespace after the split operation?
                            Answer :- Yes
                            >
                            Then using DBMS_REDEFINITION will provide the least 'downtime' but will be somewhat slower than other methods. You should obviously have a backup of your table before you begin any split operations.

                            A normal 'split' will be the poorest performing since, as user5066799 stated, the data that is to remain in the current partition will get moved multple times. There will also be the related multiple concurrent access issues instead of just one.

                            The fastest method would be:

                            1. create a new standard table with the same table structure and constraints as your existing one.

                            2. create a new partitioned table with partitions that match the six new ones you want to create. Create the indexes but make them unusable.

                            3. insert the data from the partition you want to split into this new table using parallel if you have it. You could also speed things up if the new table was set for NOLOGGING since you will have a backup anyway.

                            4. truncate the existing table partition that you are splitting.

                            5. perform multiple split operations on the existing table partition to create your six partitions. Very fast since no data.

                            6. for each partition do the following:
                            a. exchange one partition from the new partitioned table with the new empty non-partitioned table.
                            b. exchange the new table (that now has one partitions data) with the empty new partition from your real table.

                            This dual 'exchange' is necessary since a partition exchange cannot be done directly between two partitioned tables.

                            Step 3 above is the only step that takes any time at all so the length of it will determine the outage window time you will need to completed the operation. The other steps are essentially only updates of the data dictionary.

                            The nice thing about this approach is that it is easy to test without interfering at all with your current prod app.

                            I still think it is worth testing a normal DBMS_REDEFINITION to see if Oracle simply leaves your 11 partitions untouched as I would expect. If it does then that is the method to use. The above method would be my second choice if that were true; but would be my first choice if I had a big enough outage window to do it.