3 Replies Latest reply on May 8, 2018 1:23 PM by Mike Kutz

    Converting to PARTITION BY REFERENCE?

    Mike Kutz
      1. Is it possible to convert a RANGE PARTITION table into a PARTITION BY REFERENCE table?
      2. Or do I just use a modified "CTAS+RENAME" technique to do the conversion?

       

      A handful of tables need to have the same partitioning scheme so that old data can be dropped per Business Requirement.

       

      I can't find the answer in the Documentation. (and I'm going cross-eyed trying to read it)

      Attempts on LiveSQL have also failed.

       

      I'm guessing "Option 2" will need to be used.  (unless you have a better idea)

       

      Thanks,

       

      MK

        • 1. Re: Converting to PARTITION BY REFERENCE?
          AndrewSayer

          Mike Kutz wrote:

           

          1. Is it possible to convert a RANGE PARTITION table into a PARTITION BY REFERENCE table?
          2. Or do I just use a modified "CTAS+RENAME" technique to do the conversion?

           

          A handful of tables need to have the same partitioning scheme so that old data can be dropped per Business Requirement.

           

          I can't find the answer in the Documentation. (and I'm going cross-eyed trying to read it)

          Attempts on LiveSQL have also failed.

           

          I'm guessing "Option 2" will need to be used. (unless you have a better idea)

           

          Thanks,

           

          MK

          If you're referring to the one statement miracle we were given in 12.2, no I'm afraid it won't work. You'll get ORA-14427: table does not support modification to a partitioned state DDL. Once a table is partitioned, you can't use the syntax to modify it's partitioning (even to convert it back to a non-partitioned table).

           

          You can still use dbms_redefinition to do it online (like CTAS+rename, but online)

          • 2. Re: Converting to PARTITION BY REFERENCE?
            1. Is it possible to convert a RANGE PARTITION table into a PARTITION BY REFERENCE table?

            No - I don't understand how that would even make any sense.

             

            A range partitioned table is a standalone entity partitioned on one or more columns in the table itself.

             

            A referenced partitioned table is partitioned based on columns in a DIFFERENT table - the usual use case doesn't have the partitioning column in the table - that is why you would use reference partitioning instead.

             

            Please explain how it would make sense to do that conversion given that you would be using totally different partitioning keys.

             

            What am I missing about your use case.

             

            1. Or do I just use a modified "CTAS+RENAME" technique to do the conversion?

            No - that is NOT what would be done.

             

            Except with rare exceptions DDL should ALWAYS be kept in a version control system and objects should be created using that DDL. Using CTAS doesn't create ANY of the indexes or constraints that the new table will need.

             

            1. create the DDL that is needed

            2. execute the DDL to create the object

            3. validate that the object was created properly

            4. populate the table with test data

            5. create test code/processes to validate that the new table and data architecture actually meets the SLA (service level agreement) that was expected when the decision was made to use a different data model

            6. apply the above steps to the production environment

             

            Given ALL of the work and testing that needs to be done it is rather pointless to use CTAS to create the table.

             

            The ONLY exception might be during prototyping to see if the reference partitioning is even going to work. But even then it would only be done with a minimal amount of data.

             

            The real table, even for development, should then be created based on DDL from the version control system.

            • 3. Re: Converting to PARTITION BY REFERENCE?
              Mike Kutz

              AndrewSayer

              Thanks for the verification.

               

              (I treat DBMS_REDEFINITION as a modified "CTAS+RENAME" )

               

              rp0428

              I'm currently investigating the idea mentioned in the 3rd bullet point for a specific Business Requirement, how to convert current existing Production tables to such a design (this question),  and what changes (within the application) would need to occur (if any) to support it.

              https://docs.oracle.com/database/121/VLDBG/GUID-5E5BAB95-DE89-41CB-A61F-16A23A7A3B84.htm#VLDBG1280

               

              So far, no show stoppers (except the wait for the set of databases [dev/qa/test/prod] to be upgraded to 12c+)

               

              MK