Re: Ways to eliminate undo on update?
- From: "LiShan Cheng" <exriscer@xxxxxxxxx>
- To: topshot.rhit@xxxxxxxxx
- Date: Tue, 21 Feb 2006 09:01:29 +0100
Hi
I think your best bet is CTAS with parallel query and direct load.
On 2/20/06, Michael Ray <topshot.rhit@xxxxxxxxx> wrote:
>
> 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@xxxxxx> 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...
> >
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
