1 2 Previous Next 20 Replies Latest reply: Jan 25, 2013 1:24 PM by rp0428 RSS

    dbms_redefinition - change table from non partitioned to partitioned

    user650888
      CREATE TABLE test_t1
          (x                              NUMBER ,
          y                              NUMBER NOT NULL,
          pv                             NUMBER);
      I want to partition test_t1 based on the column pv, I wrote a stored procedure that will populate pv column with correct values accordingly. Once I run the procedure, pv column will be populated,

      Here is what I think should be approach

      1. Rename test_t1 to test_t1_bkp

      2. Write the logic (procedure that updates pv value in test_t1_bkp)

      3. Create new table test_t1 with partitions (so this table is empty)

      CREATE TABLE test_t1
      ( x number, y number, pv number )
      PARTITION BY RANGE (pv)
      ( PARTITION p0 VALUES LESS THAN (1) ,
      PARTITION p1 VALUES LESS THAN (2),
      PARTITION p3 VALUES LESS THAN (MAXVALUE)
      )
      PARALLEL ENABLE ROW MOVEMENT ;

      3. Now instead of inserting the data from test_t1_bkp to test_t1 and also taking care of
      indexes, constraints, can I use dbms_redefinition and apply it on test_t1_bkp itself
      so I do not have to

      1. Drop the constraints, indexes on test_t1_bkp
      2. Insert into test_t1 select * from test_t1_bkp
      3.Create indexes, constraints on test_t1

      I logged into my development environment and tried to execute dbms_redefinition procedures, but I do not
      have rights, What privileges are actually needed ? Looks like dbms_redefinition does not belong to sys

      SQL> desc dbms_redefinition;
      ERROR:
      ORA-04043: object "SYS"."DBMS_REDEFINITION" does not exist


      what grants do I need ?
        • 1. Re: dbms_redefinition - change table from non partitioned to partitioned
          user650888
          http://psoug.org/reference/dbms_redefinition.html

          above link says {ORACLE_HOME}/rdbms/admin/dbmshord.sql has to be compiled, Should I ask the DBAs to compile it for me ?
          • 2. Re: dbms_redefinition - change table from non partitioned to partitioned
            SomeoneElse
            Defined here:

            http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables007.htm#sthref2297
            • 3. Re: dbms_redefinition - change table from non partitioned to partitioned
              JustinCave
              Why do you want to use DBMS_REDEFINITION?

              There are cases where this would be the right approach. If, for example, you need to run this script while the application is running and modifying the data in test_t1, the extra overhead of doing an online table redefinition is probably worth it. In most cases, however, you would be doing this sort of modification during a downtime window in which case the most efficient approach would be to create a new partitioned table, copy the data over, create the indexes, drop the old table, and rename the new one. Particularly if you run the loads in parallel.

              Justin
              • 4. Re: dbms_redefinition - change table from non partitioned to partitioned
                user650888
                I agree with you, I was just exploring more options

                But here is what I already did

                1. Rename test_t1 to test_t1_bkp (so indexes, constraints, triggers are copied to test_t1_bkp)

                2. Create empty table test_t1 only with partitions (No indexes, constraints or triggers)

                3. Execute my procedure ( on test_t1_bkp) that populates the pv partition column accordingly

                4. Now insert into test_t1 select * from test_t1_bkp (using append hint to speed up)

                5. Drop indexes, constraints and trigger on test_t1_bkp

                6. Create indexes, constraints , triggers on test_t1,

                I think above approach is good
                • 5. Re: dbms_redefinition - change table from non partitioned to partitioned
                  JustinCave
                  That will work. Is it fast enough? If so, you're done.

                  It's a bit concerning to me that it sounds like you're adding the PY column and that you're then partitioning on that column. If you are trying to use partitioning to improve query performance, that would imply that virtually every query in the system will include a predicate on the PY column. But that seems most unlikely if you just added the column.

                  Justin
                  • 6. Re: dbms_redefinition - change table from non partitioned to partitioned
                    user650888
                    the pv column already exists, we are just putting some logic in a procedure to populate it and update...

                    the approach ran pretty fast, 3 hours for a 50gb table for the update and 40 minutes for insert, however, DBAs are not happy when I discussed my approach, hence the need for dbms_redefenition...............
                    • 7. Re: dbms_redefinition - change table from non partitioned to partitioned
                      JustinCave
                      What, specifically, were the DBAs unhappy about? How do they expect DBMS_REDEFINITION to mitigate whatever issues they see? It's fine to talk about options but unless we know what problem we're trying to solve, it's impossible to talk reasonably about what the appropriate options for addressing that problem are.
                      the pv column already exists, we are just putting some logic in a procedure to populate it and update...
                      But if you are hoping to improve query performance and PV is the column you're partitioning on (virtually) every query in the system would need to include a predicate on PV. It seems very odd that you would need to run a procedure to modify the data in that column, presumably causing every query in the system to potentially return different results, as part of the process of preparing to partition the table.

                      Justin
                      • 8. Re: dbms_redefinition - change table from non partitioned to partitioned
                        user650888
                        the update will be a one time update, we do not worry about it once pv is populated correctly

                        I am trying the following after getting all the grants from dba, I have the grants needed



                        begin
                        dbms_redefinition.can_redef_table( 'username', 'TEST_T1' );
                        end;


                        ORA-06550: line 2, column 1:
                        PLS-00201: identifier 'SYS.DBMS_REDEFINITION' must be declared
                        ORA-06550: line 2, column 1:
                        PL/SQL: Statement ignored


                        what am i missing
                        • 9. Re: dbms_redefinition - change table from non partitioned to partitioned
                          user650888
                          Do you recommend local indexes on the pv ?
                          • 10. Re: dbms_redefinition - change table from non partitioned to partitioned
                            JustinCave
                            user650888 wrote:
                            the update will be a one time update, we do not worry about it once pv is populated correctly
                            Assuming that your goal in partitioning the table is to improve query performance, (virtually) every query in the system would already need to have a predicate on PV. But if every query is already filtering on PV, it would seem extremely unlikely that you could tolerate the data ever having been incorrect. The fact that you are doing a one-time update of the key you are partitioning on strongly implies that you are partitioning on the wrong column.
                            I am trying the following after getting all the grants from dba, I have the grants needed
                            Based on the error, it seems unlikely that you have the grants you need.
                            begin
                            dbms_redefinition.can_redef_table( 'username', 'TEST_T1' );
                            end;


                            ORA-06550: line 2, column 1:
                            PLS-00201: identifier 'SYS.DBMS_REDEFINITION' must be declared
                            ORA-06550: line 2, column 1:
                            PL/SQL: Statement ignored


                            what am i missing
                            What did the DBA grant you?

                            As for whether to create local indexes, it depends. Is your goal to improve query performance (I'm still not clear if this is the goal)? Do all your queries have predicates on PV? How are you partitioning the table? How many distinct PV values are there per partition? Is data evenly distributed across PV values?

                            Justin
                            • 11. Re: dbms_redefinition - change table from non partitioned to partitioned
                              rp0428
                              >
                              the update will be a one time update, we do not worry about it once pv is populated correctly
                              >
                              You didnt' answer Justin's question: what is it the DBAs are unhappy about?

                              To partition a non-partitioned table ALL of the data has to be moved. If you have an outage window there isn't much point to using DBMS_REDEFINITION to do an online operation.

                              Why are you populating 'pv' in a table that you don't want anymore? If I were your DBA that is the first thing I would look at. Using a procedure to populate a column in a table that you want to get rid of.

                              Why not just create the partition key values at the same time you insert into the target table?

                              Tell us what that procedure is doing and what the DBAs are unhappy about.
                              • 12. Re: dbms_redefinition - change table from non partitioned to partitioned
                                SomeoneElse
                                and what the DBAs are unhappy about.
                                It might be easier to ask what they're happy about.

                                ;-)
                                • 13. Re: dbms_redefinition - change table from non partitioned to partitioned
                                  user650888
                                  Ok,

                                  pv is a column in test_t1 table,

                                  Initially, test_t1 is non partitioned, pv has some constant value in it....

                                  I wrote a function that has logic about how the value of pv has to be arrived at, That function is also used in loading the value in pv column in test_t1 table

                                  So I wrote a stored procedure which will use the function to arrive at the correct value for pv, and update test_t1 with correct values

                                  This update is obviously a one time run, and so is the subsequent insert in my steps mentioned above, the DBA says because the table is huge, lets skip the insert by using any other approach

                                  After all the hard work I did :(, I am not happy
                                  • 14. Re: dbms_redefinition - change table from non partitioned to partitioned
                                    user650888
                                    As per dbms_redefinition, the error occurs I think is because it does not exist or we need a public synonym / grants to my schema,
                                    1 2 Previous Next