| modify columns [message #145647] |
Fri, 04 November 2005 09:47  |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
dear friends: we got a pretty big size table around 20GB. we need to modify some columns not null and some other columns as also not null and putting a default value 0 there.syntax is as follows:
alter table big_table modify (col_a not null, col_b default (0) not null, col_c not null, col_d default (0) not null);
it takes long time to complete as full tablescan is necessary. Is there any way to reduce the time. Any suggestion would be welcome.
thanks.
|
|
|
|
| Re: modify columns [message #145650 is a reply to message #145647] |
Fri, 04 November 2005 10:21  |
bmahireddy
Messages: 14
Registered: March 2005
|
Junior Member |
|
|
You can use Nologging either at table level or tablespace level and avoid any redo entries. Make sure that you take proper backup after completion of these changes.
alter table big_table modify (col_a not null, col_b default (0) not null, col_c not null, col_d default (0) not null) NOLOGGING;
|
|
|
|
|
|
| Re: modify columns [message #145652 is a reply to message #145650] |
Fri, 04 November 2005 10:47 |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
My understanding is that there is no dml transactions going on during the alter statements so no occurrence of logging. Oracle is just verifying that whether all the columns supposed to be not null as per alter table modify statements : do those columns in every rows have a value. otherwise the alter statements would fail. So full tablescan is occurring on this ~20gb table.
thanks.
|
|
|
|
| Re: modify columns [message #145672 is a reply to message #145647] |
Fri, 04 November 2005 13:21 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
I don't have the ability to test anything right now, but have you tried testing what happens if you do it one column at a time? Or the columns without default values by themselves? I'm more curious as to if this would help than offering a solution; you would want to test it. Other than that the sugestion of adding a new column and copying data over would be the other method to test.
|
|
|
|
| Re: modify columns [message #145677 is a reply to message #145672] |
Fri, 04 November 2005 14:05 |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
smartin: thank you so much for all the valuable suggestions. Infact we tried to do the single alter modifications but taking longer time than them combined. I will invest some time as the new column method you suggested.
thanks again to all in this great forum to jump into the discussion and sharing their insight.
|
|
|
|
| Re: modify columns [message #145678 is a reply to message #145647] |
Fri, 04 November 2005 14:13 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Ahh good to know on the multi column operation being slower. I didn't know if it would be slower overall, but fast enough one at a time to do them in batches during windows of opportunity.
And the other method mentioned above was the CTAS method, just transfer / rebuild the whole table in essence. The advantage to that would be it could be in nologging, and you could also use the opportunity to compress some of that 20gig if it would be appropriate, and or partition the table.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
