9 Replies Latest reply: Mar 20, 2013 4:08 AM by Manik RSS

    Improve DDL query performance

    Prince chandra
      Hi Team

      Below are our working environment details.
      Oracle version: 11.2.0.2.0
      OS version : AIX 6.1
      We have a table emp_details with 23772889 records. Our requirement is to increase few of the columns size in the table emp_details. We are following the below alter statement which is taking around 2 hours of time.
      ALTER TABLE emp_details
      MODIFY
      (
      address char(90)
      ,department char(30)
      )
      /
      Is there any way to improve the above query performance? Greatly appreciated if anyone has any ideas on how to speed this up. Thank you.
        • 1. Re: Improve DDL query performance
          sb92075
          user8895369 wrote:
          Hi Team

          Below are our working environment details.
          Oracle version: 11.2.0.2.0
          OS version : AIX 6.1
          We have a table emp_details with 23772889 records. Our requirement is to increase few of the columns size in the table emp_details. We are following the below alter statement which is taking around 2 hours of time.
          ALTER TABLE emp_details
          MODIFY
          (
          address char(90)
          ,department char(30)
          )
          /
          Is there any way to improve the above query performance? Greatly appreciated if anyone has any ideas on how to speed this up. Thank you.
          do NOT use CHAR; use VARCHAR2
          • 2. Re: Improve DDL query performance
            Martin Preiss
            it may be more efficient to create a new object with the changed column definitions using CTAS and a following rename operation - if you have a maintainance window to do the task.

            And varchar2 should be a more appropriate data type.

            Regards

            Martin
            • 3. Re: Improve DDL query performance
              John Spencer
              As the others have said, you should be using varchar2, not char. The reason your alter table is taking so long is that it is updating every row in the table to right pad the address and department columns to the new length with spaces.

              The char datatype is a fixed length type and is always right padded with spaces. The varchar2 datatype is a variable length type and only stores the characters you explicitly enter, there is no padding.

              John
              • 4. Re: Improve DDL query performance
                Prince chandra
                Hi,

                Thank you all for your valuable suggestions. I have a functional dependency So it is mandatory for me to use char datatype itself. I mean, there should be empty spaces to be added at the end as code is looking for the empty space as well. Can we have any other possibility even if we choose char datatype?
                • 5. Re: Improve DDL query performance
                  603257
                  Unfortunately then whatever method you use, you're going to have to update 23M records to right pad them.
                  • 6. Re: Improve DDL query performance
                    Paul  Horth
                    user8895369 wrote:
                    Hi,

                    Thank you all for your valuable suggestions. I have a functional dependency So it is mandatory for me to use char datatype itself. I mean, there should be empty spaces to be added at the end as code is looking for the empty space as well. Can we have any other possibility even if we choose char datatype?
                    It would be better to change the code.
                    • 7. Re: Improve DDL query performance
                      Prince chandra
                      Hi

                      I had followed the below steps and found good improvement. Elapsed time compared to "alter table modify" command is reduced very well with the below approach. Approximately 30% improvement I had with the below steps.

                      1. Add the temporary column with the final/Required size (data type)
                      2. Update the original column's data (for which you want to change the data type) into temporary column using BULK UPDATE logic.
                      3. Drop the original column.
                      4. Rename the temporary column name to original column name.

                      Please note that the above approach will not be useful if the table is having an index with the impacted column(s).
                      • 8. Re: Improve DDL query performance
                        Purvesh K
                        user8895369 wrote:
                        2. Update the original column's data (for which you want to change the data type) into temporary column using BULK UPDATE logic.
                        One suggestion:-
                        Replace the Above step with a single Update Statement, to improve your performance a little further at least.

                        Bulk Collect is un-necessarily using PGA to store the data and degrade your performance. Update or a Simple SQL shall scale better.
                        • 9. Re: Improve DDL query performance
                          Manik
                          I would have given try for PARALLEL.

                          alter table table_name parallel;

                          alter table table_name modify........... PARALLEL 4 --- this depends again.

                          alter table table_name noparallel.

                          Cheers,
                          Manik.

                          Edited by: Manik on Mar 20, 2013 2:38 PM