RE: NUMERIC data type always takes up 22 bytes. Alternatives?

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Sat, 21 Oct 2000 15:13:10 +0400
Message-Id: <10656.119903@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C03B4F.E5382110
Content-Type: text/plain;

charset="iso-8859-1"

Hi,
I believe 22 bytes is the maximum to store 38 precision digits. Oracle will not be storing 22 bytes even when you allocate a small figure.

Thanks,
Amar
00-971-50-7883254
ts2017_at_emirates.com
amar_padhi_at_hotmail.com
amar_padhi_at_musclemail.com

-----Original Message-----
From: Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com [mailto:Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com] Sent: Saturday, October 21, 2000 12:41 AM To: Multiple recipients of list ORACLE-L Subject: NUMERIC data type always takes up 22 bytes. Alternatives?

Gurus,

In all the years that I have dealt with Oracle (both as a developer and a DBA -- altogether 3 years) I never stopped to pay much attention to the fact that when you define a field in a table as NUMERIC, no matter what precision and scale you define for it, you are always going to get 22 bytes allocated for this field in the table. While I see the benefit of being able to store really large numbers in the database, most of the time it's a waste.

Let's say I wanted to store a record for each person living on the planet -- ~ 7bln records.

The record would only have one field -- a unique ID of this person, starting with 1,2,3, ...

Thus the max number I would want to store would be 7,000,000,000, plus planning for potential population growth - ~35 bln in 10 years.

If I allocate 22 bytes for each of these numbers I would end up needing approx. 140Gb right away.

If there was a way for me to only allocate 6 bytes (plenty of space for billions), I would only need 40Gb.

My question is: is there a way to do it in Oracle?

I just went through documentation for 7.3.4 and this is the only data type available (there others but they are just synonyms of NUMERIC).

If somebody can shed the light on this I would be very appreciative.

Thanks,
Val Gamerman.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01C03B4F.E5382110
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2652.35">
<TITLE>RE: NUMERIC data type always takes up 22 bytes. =
Alternatives?</TITLE>
</HEAD>
<BODY>
<BR>

<P><FONT SIZE=3D2>Hi,</FONT>
<BR><FONT SIZE=3D2>I believe 22 bytes is the maximum to store 38 =
precision digits. Oracle will not be storing 22 bytes even when you =
allocate a small figure. </FONT></P>

<P><FONT SIZE=3D2>Thanks,</FONT>
<BR><FONT SIZE=3D2>Amar</FONT>
<BR><FONT SIZE=3D2>00-971-50-7883254</FONT>
<BR><FONT SIZE=3D2>ts2017_at_emirates.com</FONT>
<BR><FONT SIZE=3D2>amar_padhi_at_hotmail.com</FONT>
<BR><FONT SIZE=3D2>amar_padhi_at_musclemail.com</FONT>
</P>
<BR>
<BR>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: =
Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com</FONT>
<BR><FONT SIZE=3D2>[<A =
HREF=3D"mailto:Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.co=
m">mailto:Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com</A>=
]</FONT>
<BR><FONT SIZE=3D2>Sent: Saturday, October 21, 2000 12:41 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: NUMERIC data type always takes up 22 bytes. =
Alternatives?</FONT>
</P>
<BR>
<BR>
<BR>
<BR>

<P><FONT SIZE=3D2>Gurus,</FONT>
</P>

<P><FONT SIZE=3D2>In all the years that I have dealt with Oracle (both =
as a developer and a</FONT>
<BR><FONT SIZE=3D2>DBA -- altogether 3 years) I never stopped to pay =
much attention to the</FONT>
<BR><FONT SIZE=3D2>fact that when you define a field in a table as =
NUMERIC, no matter what</FONT>
<BR><FONT SIZE=3D2>precision and scale you define for it, you are =
always going to get 22 bytes</FONT>
<BR><FONT SIZE=3D2>allocated for this field in the table. While I see =
the benefit of being</FONT>
<BR><FONT SIZE=3D2>able to store really large numbers in the =
database,&nbsp; most of the time it's</FONT>
<BR><FONT SIZE=3D2>a waste.</FONT>
</P>

<P><FONT SIZE=3D2>Let's say I wanted to store a record for each person =
living on the planet</FONT>
<BR><FONT SIZE=3D2>-- ~ 7bln records.</FONT>
</P>

<P><FONT SIZE=3D2>The record would only have one field -- a unique ID =
of this person,</FONT>
<BR><FONT SIZE=3D2>starting with 1,2,3, ...</FONT>
</P>

<P><FONT SIZE=3D2>Thus the max number I would want to store would be =
7,000,000,000, plus</FONT>
<BR><FONT SIZE=3D2>planning for potential population growth - ~35 bln =
in 10 years.</FONT>
</P>

<P><FONT SIZE=3D2>If I allocate 22 bytes for each of these numbers I =
would end up needing</FONT>
<BR><FONT SIZE=3D2>approx. 140Gb right away.</FONT>
</P>

<P><FONT SIZE=3D2>If there was a way for me to only allocate 6 bytes =


(plenty of space for</FONT>

<BR><FONT SIZE=3D2>billions), I would only need 40Gb.</FONT>
</P>

<P><FONT SIZE=3D2>My question is: is there a way to do it in =
Oracle?</FONT>
</P>

<P><FONT SIZE=3D2>I just went through documentation for 7.3.4 and this =
is the only data type</FONT>
<BR><FONT SIZE=3D2>available (there others but they are just synonyms =
of NUMERIC).</FONT>
</P>

<P><FONT SIZE=3D2>If somebody can shed the light on this I would be =
very appreciative.</FONT>
</P>

<P><FONT SIZE=3D2>Thanks,</FONT>
<BR><FONT SIZE=3D2>Val Gamerman.</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: </FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: =
Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =

Received on Sat Oct 21 2000 - 06:13:10 CDT