Home » RDBMS Server » Performance Tuning » Performance tuning of DDL?
Performance tuning of DDL? [message #292022] Mon, 07 January 2008 08:20 Go to next message
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);

Re: Performance tuning of DDL? [message #292023 is a reply to message #292022] Mon, 07 January 2008 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Performance tuning of DDL? [message #292085 is a reply to message #292022] Mon, 07 January 2008 14:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: Performance tuning of DDL? [message #292127 is a reply to message #292022] Mon, 07 January 2008 23:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you need to do it online, you can also use dbms_redefinition.

Regards
Michel
Re: Performance tuning of DDL? [message #292140 is a reply to message #292127] Tue, 08 January 2008 00:31 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Index followup decision in case of composite b-tree index
Next Topic: increasing cache hit ratio
Goto Forum:
  


Current Time: Fri Dec 09 17:42:50 CST 2016

Total time taken to generate the page: 0.33015 seconds