| 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: Number vs. float vs. char vs. varchar2
A copy of this was sent to "Roger Loeb" <rloeb_at_martech.com>
(if that email address didn't require changing)
On Tue, 26 May 1998 22:06:51 -0600, you wrote:
>Space is an issue in a very large (400 million row) table I am working with.
>I need to better understand the actual storage requirements of data stored
>as number, float, char, and varchar2, since many of the columns in this
>table could be expressed in any of these forms.
>
You need to read the Server concepts manual, chapter 6 for V7 or chapter 9 for V8 to learn about how the data is actually stored (how many bytes each datatype will actually take)...
for example:
<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 -- 3 bytes. Storage wise -- setting the scale and precision is not meaningful. Application wise -- setting the scale and precision is very meaningful. Consider the scale and precision to be constraints, they can edit your data.
>Number seems to take 22 bytes. Does it really take all of that when scale
>and precision are something like 3?
>
>Float seems to take the same 22 bytes, on a machine where a float takes only
>8 bytes. Does it take 22, 8, or something else?
>
>Many of these data elements have extensive leading zeros, i.e., there is an
>implied decimal to the left of the number. They also often have signs,
>either plus or minus. I could easily store them in a char or varchar2,
>which would "apparently" take less space than number or float. (None of
>these values need to be manipulated. They simply need to be returned on a
>query and placed in a fixed length string. If I trim the leading zeros, the
>application can easily put them back on.)
>
>So, how do I use the least amount of disk space with this kind of data?
>
>Rog
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
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 Wed May 27 1998 - 05:54:47 CDT
 |
 |