How much storage numeric vs. number(2)

[Kevin Barnes]

How much storage difference is there between defining a column numeric
vs. numeric (X).

I find that restricting the length of the field in most cases causes
more development pains than allowing unrestricted lengths.

TIA,

Kevin

[Thomas Kyte]

A copy of this was sent to Kevin Barnes <kba...@airmail.net>
(if that email address didn't require changing)

Server concepts manual, chapter 6, section on "Number Datatype" includes this
(and much more) about the number datatype:

<quote>
Oracle stores numeric data in variable–length format. Each value is
stored in scientific notation, with one byte used to store the exponent
and up to 20 bytes to store the mantissa. (However, there are only 38
digits of precision.) Oracle does not store leading and trailing zeros. For
example, the number 412 is stored in a format similar to 4.12 x 10^2,
with one byte used to store the exponent (2) and two bytes used to
store the three significant digits of the mantissa (4, 1, 2).
<quote>

So, the number 412 in a number(3) or a number(38) will consume the same exact
amount of storage. Storage wise -- setting the scale and precision is not
meaningful. Application wise -- setting the scale and precision is very
meaningful. Fix the numbers not for the storage but for the fact that a
number(3) is just that -- a number with 3 digits of precision. Consider the
scale and precision to be constraints, they can edit your data.

Thomas Kyte
tk...@us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you. Any bounced
email will be treated the same way i treat SPAM-- I delete it.

[Pavel Polcar]

Hi,
I don't think there are any storage differences, because only valid digits
are stored in each case.
The restriction on length (precision) or scale gives some range check on
the number. Moreover,
using unrestricted lengths (especially for character strings) can cause
difficulties in products
like SQL*Plus, because memory allocation for fetching records is done based
on the declared
length, not based on the actual length of the item.
Regards,
--
Pavel Polcar

Kevin Barnes <kba...@airmail.net> wrote in article
<3559E317...@airmail.net>...