Re: Storing large numbers, NUMBER datatype questions

> Looks like we increase the datatype to Number(18,6) from Number(14,6)
> in one field of one table.
>
> The administrator said she ran out of space after doing this but I
> think it must have been a coincidence.

Simply modifying the column datatype from NUMBER(18,6) to NUMBER(14,6) will not cause you to run out of space. Similarly, modifying the column datatype from VARCHAR2(40) to VARCHAR2(50). The act of making this DDL change does not cause any more space to be used by the table.

Modifying the columns value within the table may require more space to be used. And if there is not enough room in the block for the new data, the entire row may be migrated to a new row.

> I did some research and came to
> the conclusion that increasing the size should have little impact on
> the space utilization. If you store a larger nunber then it increases
> the storage requirements for that particular value.

A larger value may or may not require more space. It depends on the original value and the new value. Whereas it is obvious that increasing a VARCHAR2 data in length (for instance changing "abc" to "abcd") will require more bytes, the same is not necessarily true of number values. Changing the NUMBER value from '1' to '10' does not require any more space. Oracle needs a minimum of two bytes two store a NUMBER, one bye for the mantissa and one byte for the exponent. The number '1' still requires two bytes, as does the number '10'. Some number changes may increase your space requirements though.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown