Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Rebuilding a table with smaller columns
"Jim Kennedy" <jim dot scuba dot kennedy at gee male dot com> wrote in
message news:c6qdnbOLj7Q3OHveRVn-og_at_comcast.com...
>
> "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
>
>
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)
*
SQL> select dump(a), dump(b)
2 from t1;
DUMP(A)
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)
*
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