This discussion is archived
9 Replies Latest reply: Mar 20, 2013 2:08 AM by Manik RSS

Improve DDL query performance

Prince chandra Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points