Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Rebuilding a table with smaller columns

Re: Rebuilding a table with smaller columns

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sun, 5 Feb 2006 22:46:24 -0500
Message-ID: <>

"Jim Kennedy" <jim dot scuba dot kennedy at gee male dot com> wrote in message
> "Michael Ray" <> 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

the precision constrains, but technically is not a constraint. and the max value of a 12,7 will take more storage than a 7,3 (examples using OracleXE -- 10.2):

SQL> create table t1( a number(12,7), b number (7,3))

Table created.

SQL> insert into t1 values(99999.9999999, 9999.999);

1 row created.

SQL> insert into t1 values(999999.9999999, 99999.999); insert into t1 values(999999.9999999, 99999.999)


ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> select dump(a), dump(b)
  2 from t1;



Typ=2 Len=8: 195,10,100,100,100,100,100,91 Typ=2 Len=5: 194,100,100,100,91

also, you can't reduce precision of a column that contains data:

SQL> create table t2(n number(12,7));

Table created.

SQL> insert into t2 values(0);

1 row created.

SQL> alter table t2 modify n number(7,3); alter table t2 modify n number(7,3)


ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale

that being said, no extra space is used by not changing the datatype if the existing data already conform to the 'corrected' datatype -- just that the DBMS won't enforce the correct scale and precision

an actual check constraint, or a trigger, could be used to enforce 7,3 vs 12,7

++ mcs Received on Sun Feb 05 2006 - 21:46:24 CST

Original text of this message