5 Replies Latest reply: Jan 18, 2012 5:17 PM by rp0428 RSS

    How to move rows from one partition to another?

    911820
      We have a data retention requirement for 6 months and after 6 months the data can be removed except for few records with certain statuses (which cannot be removed even if they are over 6 months). We have Oracle 11g.

      I wanted to see if the following strategy works:

      I will have monthly partitions and 8 sub partitions (hash) in each of the main partitions. The hash sub-partitions are to spread the load of application data and balance the data insertion to avoid any contention.

      At the end of 6 months, is it possible for me to move the row that needs to be kept to a different partition and drop the last partition. I wanted to avoid data deletion because we only have very little database downtime and data deletion require us to coelize the database to avoid fragmentation.
      If I can move the data to another partition, how will I do it?

      Thanks
        • 1. Re: How to move rows from one partition to another?
          sb92075
          908817 wrote:
          We have a data retention requirement for 6 months and after 6 months the data can be removed except for few records with certain statuses (which cannot be removed even if they are over 6 months). We have Oracle 11g.

          I wanted to see if the following strategy works:

          I will have monthly partitions and 8 sub partitions (hash) in each of the main partitions. The hash sub-partitions are to spread the load of application data and balance the data insertion to avoid any contention.
          I doubt above is true.
          post complete DDL for this table.
          At the end of 6 months, is it possible for me to move the row that needs to be kept to a different partition and drop the last partition. I wanted to avoid data deletion because we only have very little database downtime and data deletion require us to coelize the database to avoid fragmentation.
          post SQL & results that show that "fragmentation" actually exists.
          • 2. Re: How to move rows from one partition to another?
            rp0428
            OP wrote:
            I wanted to avoid data deletion
            Your goal is unachievable. You can't avoid data deletion when a record is removed from a partition. When a record is moved from one partition to another it is deleted from the original partition as part of the transaction.

            So your strategy of moving records to a new partition and then dropping the new partition actually increases the amount of work being done.

            The only way the user can cause oracle to move data to another partition is to change the value of the partition key columns that determine what partition Oracle puts the data in. The table also has to have row movement enabled.

            Since you have monthly partitions your process would need to create a 'deleteMe' partition, move the records there and then drop the partition. You would have to do this every time you wanted to remove records.

            I have not heard of anyone even considering this approach.

            A related approach I have seen and used is to create a MAX VALUE partition to hold records that always need to be made available. Then at the end of 6 months you would move the records you want to kEEP (not the ones you want to remove) to the MAX VALUE partition by changing the partition key date value. The you can drop the oldest partition that is nover 6 months. old

            That process makes sense.

            Trying to control fragmentation? FUGETABOUTIT.
            • 3. Re: How to move rows from one partition to another?
              911820
              I think you didn't get intentions correctly.

              My intention is to move the required data to a partition and then drop the original partition. that because the amount of data required to keep for future use is less than 1%.

              I understood what you meant to convey. Thanks a lot.

              I laid out eh plan something like the following.


              1. Add a new date column to facilitate the partitioning (say its called PARTITION_DT. PARTITION_DT will have the same data as ROW_CREAT_DTTM without the timestamp portion of the date.)

              2. We will start with monthly partition using PARTITION_DT, Create a 'Default' partition as well.

              3. Add hash sub-partitions on MSG_ID to spread the application data load and avoid contention (so the application load is balanced just like how its balanced through the hash partitions today)

              4. At the end of the data retention period, identify the records that needs to be retained (because of some trade statuses, etc ) and update the PARTITION_DT with a distant past date so the row will automatically move to the Default partition. The identification of the records to be retained and updating the PARTITION_DT can be done through a batch job. since this step involve very few records, it can be done in minutes or seconds.

              5. Now, we can drop the oldest partition and rebuild the index if required.

              6 The process should add sufficient number of monthly and its sub-partitions ahead of time to make sure we always have partitions available for new data.

              The entire process can be automated and executed through a scheduled job.
              • 4. Re: How to move rows from one partition to another?
                911820
                My intention is to move the required data to a partition and then drop the original partition. that because the amount of data required to keep for future use is less than 1%.

                I understood what you meant to convey. Thanks a lot.

                I laid out the plan something like the following.


                1. Add a new date column to facilitate the partitioning (say its called PARTITION_DT. PARTITION_DT will have the same data as ROW_CREAT_DTTM without the timestamp portion of the date.)

                2. We will start with monthly partition using PARTITION_DT, Create a 'Default' partition as well.

                3. Add hash sub-partitions on MSG_ID to spread the application data load and avoid contention (so the application load is balanced just like how its balanced through the hash partitions today)

                4. At the end of the data retention period, identify the records that needs to be retained (because of some trade statuses, etc ) and update the PARTITION_DT with a distant past date so the row will automatically move to the Default partition. The identification of the records to be retained and updating the PARTITION_DT can be done through a batch job. since this step involve very few records, it can be done in minutes or seconds.

                5. Now, we can drop the oldest partition and rebuild the index if required.

                6 The process should add sufficient number of monthly and its sub-partitions ahead of time to make sure we always have partitions available for new data.

                The entire process can be automated and executed through a scheduled job.

                Edited by: 908817 on Jan 18, 2012 12:58 PM
                • 5. Re: How to move rows from one partition to another?
                  rp0428
                  You got it! That is exactly what I was trying to convey. That process will work the way you want.