5 Replies Latest reply: Jul 16, 2013 4:49 PM by rp0428 RSS

    Quickest method to partition existing large table

    Neal

      What would be the best and quickest way to partition an existing table with 40 millions rows on 11g DB?

       

      Nilaksha.

        • 1. Re: Quickest method to partition existing large table
          Hemant K Chitale

          There are three ways :

           

          1.  DBMS_REDEFINITION  (e.g. see http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php  )

           

          2.  Create a new Partitioned table with 1 partition, Exchange this table with the Partition in the new table and then SPLIT the Partitions as required (I am assuming Range Partitioning)   (e.g. see http://www.oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition.php  ).  Then drop/rename the old table and rename the new table. Create Indexes as required.

           

          3.  Create a new Partitioned table with all the desired partitions and then copy data from the existing table to the new partitioned table -- use Parallel DML for the INSERT.   Then drop/rename the old table and rename the new table.  Create Indexes as required.

           

           

          Hemant  K Chitale


          • 2. Re: Quickest method to partition existing large table
            sb92075

            Neal wrote:

             

            What would be the best and quickest way to partition an existing table with 40 millions rows on 11g DB?

             

            Nilaksha.

             

            You can NOT partition an existing table.

             

            use DBMS_REDEFINITION  to populate a new partitioned table.

            • 3. Re: Quickest method to partition existing large table
              rp0428

               

              2.  Create a new Partitioned table with 1 partition, Exchange this table with the Partition in the new table and then SPLIT the Partitions as required (I am assuming Range Partitioning)   (e.g. see http://www.oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition.php  ).  Then drop/rename the old table and rename the new table. Create Indexes as required.

               

               

              PLEASE NO! Danger Will Robertson, danger!

               

              That method is one of the WORST ways to do the job. Oracle-base articles are usually pretty good but that one is TERRIBLE and I wish they would remove it.

               

              When a partition is SPLIT Oracle must create two NEW segments. So if you split PART_2007 into PART_2007 and PART_200701 three DIFFERENT segments are involved. The January data gets its own segment and then the data for the other 11 months gets moved from PART_2007 to a NEW PART_2007 (it doesn't and can't get left in the same partition since the Jan data is still there and is never deleted).

               

              Then that 'new' PART_2007 would get split into a DIFFERENT new PART_2007 and PART_200702 which means moving TEN months of data from one PART_2007 partition to the new one. The next split moves NINE months of data for the THIRD time and so on.

               

              By the time the split is complete the December data has been moved 12 times, November data has been moved 11 times and so on.

               

              Can you spell HORRENDOUS?

              • 4. Re: Quickest method to partition existing large table
                Hemant K Chitale

                You have a point !

                 

                Defining the partition boundaries and validating the existing data vis-a-vis the boundaries is important.  The example on oracle-base is for 3 "yearly" partitions so the data doesn't move too many times.  When a SPLIT partition is done, if there is no data above the split boundary, then there is no actual data movement, Oracle simply creates a new empty partition.  However, when the partition split boundary is in the middle of the partition --- yes, rows have to move to a new segment.

                 

                 

                Hemant K Chitale

                • 5. Re: Quickest method to partition existing large table
                  rp0428

                  Too bad OP isn't using 12.1.0.1.0 then they could split into multiple partitions in one operation.

                   

                  See the VLDB and Partitioning Guide

                  http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_admin002.htm#sthref609

                  >

                  Splitting into Multiple Partitions

                  You can redistribute the contents of one partition or subpartition into multiple partitions or subpartitions with the SPLIT PARTITION and SPLIT SUBPARTITION clauses of the ALTER TABLE statement. When splitting multiple partitions, the segment associated with the current partition is discarded. Each new partitions obtains a new segment and inherits all unspecified physical attributes from the current source partition.You can also use fast split when splitting into multiple partitions.

                  You can use the extended split syntax to specify a list of new partition descriptions similar to the create partitioned table SQL statements, rather than specifying the AT or VALUES clause. Additionally, the range or list values clause for the last new partition description is derived based on the high bound of the source partition and the bound values specified for the first (N-1) new partitions resulting from the split.

                  The following SQL statements are examples of splitting a partition into multiple partitions.

                  ALTER TABLE SPLIT PARTITION p0 INTO  (PARTITION p01 VALUES LESS THAN (25),  PARTITION p02 VALUES LESS THAN (50),  PARTITION p03 VALUES LESS THAN (75),  PARTITION p04);    

                  >