Re: Adding column is slow

Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:ak2pv30168k_at_drn.newsguy.com...
> In article <3d64c91c$0$43947$edfadb0f_at_dspool01.news.tele.dk>, "Peter
says...
> >
> >Hi,
> >
> >System(s) 8.0.5 and 8.1.7 SE
> >
> >Adding a new column to a large table is taking hours. Its part of an
update
> >that will be run at many customers. The table will contain approx 100M
rows.
> >The table has 5 number fields and a date field. A new 'number(1) default
0'
> >is beeing added
> >Assuming the update will run as the only user connected what could be
done
> >to speed up things?
> >Will any of the following help: Disabling triggers? Disabling
constraints?
> >Dropping indexes and rebuilding?
> >
> >Also how do I calculate the how much rollback space is needed?
> >
> >TIA
> >Peter Laursen
> >
> >
>
> You are in effect updating every single row in the table.
>
> I see lots of row migration taking place in your future....
>
>
> Perhaps instead of alter table add column .... you could:
>
>
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table new_emp as select * from
> scott.emp;

I assume you mean create the new table with the same structure as the old. Not sure why you're inserting the data at this point as you're going to do the direct load later ?

>
> Table created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter table new_emp add x number(1)
default
> 0;

Which could have been done at the same time as above ?

>
> Table altered.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert /*+ append */ into new_emp
select
> emp.*, 0 from scott.emp;
>
> 14 rows created.
>

Now you do the direct insert. See the advantage ;)

>
> and then drop the old table and rename the new table to the old. Then,
add
> indexes/constraints/grants/etc to the new table
>
> advantages
>
> o no redo if you use nologging
> o no rollback (append hint)
> o can be done in parallel using parallel query

The add column could also be performed in parallel ?

> o no row migration

In this case, I'm not sure row migration is going to be a problem. The new column is a number(1) field so providing PCTFREE is a reasonable value, then he should be OK. Also, future updates won't be much of a problem with a 1 number field.

Disadvantages

All indexes have to be recreated.

All constraints need to be recreated.

All grants reissued.

All triggers have to be recreated.

It's definitely a possible way to go not doubt about it. But if row migration in particular is not going to be a problem (which it mightn't be in this case), then this method in my tiny weeny opinion loses one of it's great advantages.

So it could be a toss of the coin.

Cheers

Richard
>
>
> >
> >
>
> --
> Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Thu Aug 22 2002 - 09:57:32 CDT