OraFAQ Forum: Performance Tuning » Performance tuning of DDL?

Home » RDBMS Server » Performance Tuning » Performance tuning of DDL?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance tuning of DDL? [message #292022]

Mon, 07 January 2008 08:20

lwright
Messages: 1
Registered: January 2008

Junior Member

I need to add 2 columns to a table with 180M rows. I have tested this on a test server and the alter table took 9 hours to complete. The database I tested on is not in archivelog mode. Any ideas how to speed this up? My statement is

alter table tablex add
(column1 number(9,2) default 0,
column2 number(7) default 0);

Report message to a moderator

Re: Performance tuning of DDL? [message #292023 is a reply to message #292022]

Mon, 07 January 2008 08:30

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do it when your are alone.
This is the only way to speed up the stuff.
You can also increase your redo log files during the process (check your alert.log if you have any "checkpoint not complete" or the like message).

Regards
Michel

Report message to a moderator

Re: Performance tuning of DDL? [message #292085 is a reply to message #292022]

Mon, 07 January 2008 14:00

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi,

I have done a similar change on PROD Database for a 100+ mill rows table,

The approach was

Quote:

1.Modify Table Just add the column.( No default )
2.Update the table for this 2 newly added columns ( in a chuck of 500 K or depending on your Undo TS and Redolog Segement and commit the rows )
3.Modify the tables now to be with Default 0 and you can add Not Null Constraint if you want.

It was on 9i Release.

Thanks

[Updated on: Mon, 07 January 2008 14:02] by Moderator

Report message to a moderator

Re: Performance tuning of DDL? [message #292097 is a reply to message #292022]

Mon, 07 January 2008 16:40

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

my inclination might be to recreate the table from scratch, adding the new columns with data etc. as you go.

create table newtab
nologging
parallel degree (?)
as
select a.*,newcol1,newcol2
from oldtab a
/

rebuild indexes nologging in parallel

rename old table, rename new table

test it!

drop old table

turn on logging for tables/indexes

backup!

I have never been able to add columns with data to a large table as this any other way.

On a decent system you should be able to do 1 million rows/minute easy, so you are looking at 3 hours or less to rebuild this table (yes this is a bit of a swag but is also based on my similar experiences). Indexes will take on average 30 minutes each (yes another swag) but you can do more than one at a time, or us parallel option on index builds.

So, you be the judge of how the math works for you.

Good luck, Kevin

Report message to a moderator

Re: Performance tuning of DDL? [message #292127 is a reply to message #292022]

Mon, 07 January 2008 23:36

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you need to do it online, you can also use dbms_redefinition.

Regards
Michel

Report message to a moderator

Re: Performance tuning of DDL? [message #292140 is a reply to message #292127]

Tue, 08 January 2008 00:31

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

I agree with Kevin. Adding a column with a non-null in every block will load every single block into the buffer cache, and will rewrite every block back to disk. It may result in chaining if you are unlucky.

A rebuild will write every block back to disk, but will bypass the buffer cache so logically is MUST be quicker in every case because it does less work. What's more, it guarantees no chaining.

The only caveat is indexing and referential integrity. If your table is riddled with indexes, foreign keys, and dependent tables, there is a possibility (a remote one) that rebuild will be slower.

Ross Leishman

Report message to a moderator

Previous Topic:	Index followup decision in case of composite b-tree index
Next Topic:	increasing cache hit ratio

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 23 20:37:52 CST 2025