Re: Number vs. float vs. char vs. varchar2

In article <6kg3gs$h2u$1_at_news1.rmi.net>,   "Roger Loeb" <rloeb_at_martech.com> 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.
>
> 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
>
> --

Hi Rog,

I would like to share some of the space consuming issues which i think may help you in deciding the datatypes.

Eventhough the datatype NUMBER seems to take much, it depends on the value being stored. For example,

Column value (a NUMBER col) Space used

9999                                   3 Bytes
10000                                  2 Bytes
1000000                                2 Bytes
1111                                   3 Bytes

Since Oracle stores all NUMBERs in exponential format it differs from value to value. Please note that if a NUMBER has leading zeros then space taken is less.

Also, it is better to use a VARCHAR type data rather a CHAR type as it uses variable format.

Regards,
Saravanan
(saro_at_psoux141.sgp.hp.com)

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu May 28 1998 - 04:38:10 CDT