| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Size of NUMBER field
A copy of this was sent to Doug Cowles <dcowles_at_bigfoot.com>
(if that email address didn't require changing)
On Tue, 09 Feb 1999 11:04:36 -0500, you wrote:
>I read somewhere that a NUMBER with unspecified precision
>takes up 22 bytes, but upon testing it with a table, I find this isn't
>so.
>If I enter a 1 digit number, the row becomes 6 bytes long.
>If I enter a 5 digit number, the row becomes 8 bytes long.
>If I enter an 8 digit number, the row becomes 9 bytes long.
>If I enter a 12 digit number, the row becomes 11 bytes long -
>Etc.,
>I got these by analyzing the table and select avg_row_len on a table
>with only
>1 row, and one column, a NUMBER with no precision.
>
>A physical model was just reviewed and the suggestion was made to change
>
>any unspecified NUMBER fields to have length and/or precision.
>I guess I wondering though that if NUMBER doesn't hog 22 bytes
>automatically,
>and there are no conflicts with the application, it seems it's not
>important
>as I thought it was. Any thoughts?
>
>- Dc.
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. If it makes sense to have a scale and precision -- do it. If it doesn't, don't.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
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. Received on Tue Feb 09 1999 - 11:03:56 CST
 |
 |