Home » RDBMS Server » Server Administration » Estimate for altering a table.
Estimate for altering a table. [message #146352] Wed, 09 November 2005 23:59 Go to next message
deepa_balu
Messages: 74
Registered: March 2005
Member
Hi ,

I need to provide an estimate of Migrating application to the next version.

This migration involves lot of changes in the Tables. So i need to find out how much time it takes.

So can u please help me with ur experience.

1. How much time will it take to add a column or one or more columns to a big Table.

( please tell me with ur experience on the no. of rows and the time taken to add a column or more)


2. How long to delete a column

3. Have u experience any case where creating a new table is faster instead of altering the Table if we are going to add more than 10 rows

Please do help me out.
Thanks

Report message to a moderator

icon6.gif  Re: Estimate for altering a table. [message #146356 is a reply to message #146352] Thu, 10 November 2005 00:34 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
1) adding a big table doesn't take too long, depending on the number of rows. It sometimes creates 'chained-rows' that you should resolve later. I would say about 200 seconds per million rows per column.

2) dropping a column is even faster. On a Solaris 9, 64 bit, 8 processors, 32 gigs RAM, 64 bit Oracle 10g it takes about 20 seconds per million rows.

Hope this helps!

[Updated on: Thu, 10 November 2005 00:35]

Report message to a moderator

Re: Estimate for altering a table. [message #146402 is a reply to message #146356] Thu, 10 November 2005 04:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Without an understanding your environment,hardware etc it is impossible to guess anything.
Migration is a ONE time process. So whatever time it takes, go through the 'proper channel'. Else be prepared for the lengthy days in dark rooms.
>>Have u experience any case where creating a new table is faster instead of altering the Table if we are going to add more than 10 rows
You really mean 10 rows?

Dropping the columns are 'technically' not the fastest.
Setting the columns unused are the fastest.
And you can drop the columns later during offpeak hours.

IMHO, there are bigger fish to fry here.
I would worry about the behaviour/performance of applications after the migration.

Report message to a moderator

Re: Estimate for altering a table. [message #146540 is a reply to message #146402] Thu, 10 November 2005 23:22 Go to previous messageGo to next message
kamragulshan
Messages: 66
Registered: May 2005
Location: Delhi
Member

Thanks to u Sir. But i have recently join a co. as a fresher. There database is not perfect. Our database is running on Oracle 8 and all the table are in System tablespace and now we are planning to move onto Oracle9i and for that we have purchased License.

Now our planning is to create a well formed database and for that we want to judge, what is our exact requirement and what is the daily workload so that we will keep track of the size of the datafile.

Thanks

Gulshan

Report message to a moderator

Re: Estimate for altering a table. [message #146564 is a reply to message #146540] Fri, 11 November 2005 01:59 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>all the table are in System tablespace
That would be one of the worst oracle practises.
First move them away from system tablespace.

Report message to a moderator

Previous Topic: Corrupt block relative dba: 0x02400608 (file 9, block 1544)
Next Topic: Upgrade Oracle 8.1.6 to 9i
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jan 10 12:49:34 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.