Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Ways to eliminate undo on update?
My goal is reducing the precision and scale of a populated column on a
table with almost 500 million rows.
I haven't tried the online redef yet (this is 10gR2), but the other option is taking a huge amount of undo. I have set ALTER SYSTEM SET UNDO_RETENTION=5; which helped quite a bit on the original full import.
Is there a way to disable undo for the update operation? I think my best bet would be doing CTAS nologging, drop old table and rename new one.
On 2/9/06, Richard Quintin <rquintin_at_vt.edu> wrote:
> If you are 9i+ you have two more options.
>
> Online redefinition would allow you to do it without any additional down time.
>
> or
>
> as an alternative to creating a new table you can do something like
> alter table foo add column(x number(7,3));
> update foo set x = old_column;
> alter table foo drop column old_column;
> alter table foo rename column x to old_column;
>
> I rather doubt this method would be more performant than just importing into a
> pre-created table, but it's an option...
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 20 2006 - 15:42:06 CST
![]() |
![]() |