Home » RDBMS Server » Performance Tuning » Improve DDL operation performance (2 Merged) (oracle, 11.2.0.2, AIX6.1)
icon14.gif  Improve DDL operation performance (2 Merged) [message #577368] Thu, 14 February 2013 09:32 Go to next message
soft_doctor
Messages: 33
Registered: May 2010
Member
Hi Team

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.
Re: Improve DDL operation performance [message #577370 is a reply to message #577368] Thu, 14 February 2013 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 22477
Registered: January 2009
Senior Member
>Is there any way to improve the above query performance?
NO

using CHAR is a FLAWED implementation; VARCHAR2 is Best Practice.
Re: Improve DDL operation performance [message #577371 is a reply to message #577370] Thu, 14 February 2013 09:46 Go to previous message
cookiemonster
Messages: 10841
Registered: September 2008
Location: Rainy Manchester
Senior Member
Recreate the table with the correct sizes using create table as select.
It'll be a lot faster.
But if I was you I'd take this opportunity to change the datatype of those columns to varchar2.
You can increase the size of varchar2 columns instaneously.
char takes ages as it has to actually update the data in the column to add the extra spaces.
Previous Topic: Collection count high in Memory
Next Topic: Joins and in-line views
Goto Forum:
  


Current Time: Tue Jul 22 17:17:40 CDT 2014

Total time taken to generate the page: 0.11707 seconds