Re: Adding column is slow

In article <zJ699.12224$g9.39975_at_newsfeeds.bigpond.com>, "Richard says...
>
>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 ?
>
>

doh -- forgot the "where 1=0" somehow. Yes, that was just a structural copy (or should have been!)

>>
>> 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 ?
>

not with the number(1) -- it would've be number, not number(1) (even with a CAST).
>>
>> 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 ?

how?

>
>
>> 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.

*should be*, not "will most certainly be". The blocks could be full to the gills right now, we don't know

>
>
>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.

Not if the problem was speed, in most cases -- I believe this nologging/do it in parallel approach has advantages.

>
>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
>>
>
>

--
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