Re: Rebuilding a table with smaller columns

"Michael Ray" <Ihatespam_at_indy.net> wrote in message news:Xns9761ADB3BBBB3topshotindynet_at_199.45.49.11...
> One thing we'd like to do during an upgrade is reduce the scale and
> precision of a couple columns. There are 2 tables that make up 90% of the
> data, both of which have a column of number (12,7), which is much greater
> than required. If I precreate these tables with a column of number(7,3),
> the data goes in fine, but the import takes MUCH longer than if we use the
> original definition.
>
> Would it be quicker to do a normal import then create a new table with the
> smaller column, copy the data over, drop the old and rename the new? I'm
> guessing that whatver processing Oracle is doing to round this data off
> will take the same amount of time regardless of which method I use. Plus
> then I have to deal with making sure all the indexes, constraints and
> rights are in order for the new table.
>
> Essentially, I want to know what is the fastest method to reduce the scale
> and precision of a column since we don't know how much downtime we'll
have.
> Since we were already doing an upgrade (8i to 10g) we thought we'd save
> some space at the same time.

The numbers take the same size. The extra precision is a constraint not a storage difference. You can alter the table in place. Try it on a sample of the table with the original table definition. Or update the column in place and then alter the table definition. Jim Received on Sun Feb 05 2006 - 19:28:05 CST