Re: modifying and adding columns to a big table

On Mon, 10 Nov 2003 15:12:51 +0000 (UTC), Rob Cowell <rjc4687_at_hotmail.com> wrote:

>Cris Carampa wrote:
>>
>> I have to enlarge the precision of a numeric column and add two new
>> columns on a partitioned table that counts 1,300,000 records. This on
>> Oracle 9.0.1.3 on SuSE SLES 7. Is this a resource-consuming operation or
>> a "light" one? Does this operation will invalidate the packages based on
>> this table?
>
>Adding and modifying columns is "light".

 Although it can get heavier if you add a column with a DEFAULT clause as Oracle has to populate the column in all existing rows.

>It's dropping them that hurts.

 But remember you can defer this cost by using 'set unused', at the cost of having the space still allocated. At a later time you can either 'alter table x drop unused columns' (which does the 'heavy' work of actually removing the data).

 Or if it's a table where rows get deleted out after a certain amount of time, you can choose to leave it; new rows appear to have a 1-byte overhead for the inaccessible column from looking at block dumps, and the old ones with the larger overhead (the value of the column in that row) will eventually disappear. Depends what your priorities are and when/whether you can afford to rewrite the table with 'drop unused columns'.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)