Actually, I just answered my own question. The
following is from MetaLink. I have seen this article
before but could not find it this morning for the life
of me!! :-)
---
Doc ID: Note:1031902.6
Internal Numeric Data Storage
-----------------------------
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 zeroes.
For example, the number 412 is stored in a format
similiar to 4.12 X 10^2, with one byte used to store
the exponent(2) and two bytes used to store the three
digit significant digits of the mantissa (4,1,2) for
a total length of 3.
In the example below, the data_value column on the
extreme left shows the number value which is being
dumped in the dump(number_value) column on the
opposite side to find the length of the field.
SVRMGR> select data_value, dump(number_value) from
number_data;
DATA_VALUE DUMP(NUMBER_VALUE)
--------------- ---------------------------
412 Typ=2 Len=3: 194,5,13
1 Typ=2 Len=2: 193,2
10 Typ=2 Len=2: 193,11
10000 Typ=2 Len=2: 195,2
10001 Typ=2 Len=4: 195,2,1,2
-1 Typ=2 Len=3: 62,100,102
-10 Typ=2 Len=3: 62,91,102
-10000 Typ=2 Len=3: 60,100,102
-10001 Typ=2 Len=5: 60,100,101,100,102
10.25 Typ=2 Len=3: 193,11,26
100.25 Typ=2 Len=4: 194,2,1,26
1025 Typ=2 Len=3: 194,11,26
10225 Typ=2 Len=4: 195,2,1,26
11 Typ=2 Len=2: 193,12
111 Typ=2 Len=3: 194,2,12
Simple Formula to calculate the length of Number(p)
where p is the precision of a given value (scale has
no effect), can be calculated using:
length = floor [(p+1)/2] + 1
add +1 byte (only for negative numbers
where the number of significant digits
is less than 38).
> --- Walter K <alden14004_at_yahoo.com> wrote:
> > Thanks, but the table doesn't exist yet. Also, I
> > need
> > to estimate the space needs for a column of a
> > numeric
> > datatype, not the row itself.
> >
> > --- John Lewis <jlewis_at_punchnetworks.com> wrote:
> > > Oracle stores the avg_row_length in all_tables
> > view.
> > > Use analyze_schema to get the most recent
> numbers.
> > >
> > > -----Original Message-----
> > > Sent: Monday, May 21, 2001 12:12 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Thanks for the info. I know about the VSIZE
> > > function,
> > > but "how" do I estimate the amount of space
> > > manually?
> > > What about negatives, fractional values, etc.?
> > These
> > > all require differing amounts of space.
> > >
> > > Thanks again.
> > > Walt
> > >
> > >
> > >
> > > --- "Toepke, Kevin M" <ktoepke_at_cms.cendant.com>
> > > wrote:
> > > > Walter:
> > > >
> > > > You use the "VSIZE()" function. And you are
> > right
> > > > about 10000 not taking the
> > > > same amount of size as 99999. 10000 uses 2
> bytes
> > > and
> > > > 99999 uses 4.
> > > >
> > > > SQL> select vsize(10000), vsize(99999) from
> > dual;
> > > >
> > > > VSIZE(10000) VSIZE(99999)
> > > > ------------ ------------
> > > > 2 4
> > > >
> > > > Kevin
> > > >
> > > > -----Original Message-----
> > > > Sent: Monday, May 21, 2001 1:56 PM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > Hi,
> > > >
> > > > How do I calculate the amount of physical
> space
> > > > required for storing a number? (i.e. NUMBER,
> > > > NUMBER(5), NUMBER(10,5), etc.) I believe
> Oracle
> > > uses
> > > > "2's compliment" for storing numbers. A value
> of
> > > > 10000
> > > > does not seem to take up the same amount of
> > space
> > > as
> > > > 99999.
> > > >
> > > > TIA!
> > > > -wk
> > > >
> > > >
> > __________________________________________________
> > > > Do You Yahoo!?
> > > > Yahoo! Auctions - buy the things you want at
> > great
> > > > prices
> > > > http://auctions.yahoo.com/
> > > > --
> > > > Please see the official ORACLE-L FAQ:
> > > > http://www.orafaq.com
> > > > --
> > > > Author: Walter K
> > > > INET: alden14004_at_yahoo.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).
> > > > --
> > > > Please see the official ORACLE-L FAQ:
> > > > http://www.orafaq.com
> > > > --
> > > > Author: Toepke, Kevin M
> > > > INET: ktoepke_at_cms.cendant.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).
> > >
> > >
> > >
> __________________________________________________
> > > Do You Yahoo!?
> > > Yahoo! Auctions - buy the things you want at
> great
> > > prices
> > > http://auctions.yahoo.com/
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Walter K
> > > INET: alden14004_at_yahoo.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).
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: John Lewis
> > > INET: jlewis_at_punchnetworks.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).
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Yahoo! Auctions - buy the things you want at great
> > prices
> > http://auctions.yahoo.com/
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> >
> === message truncated ===
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Auctions - buy the things you want at great
> prices
> http://auctions.yahoo.com/
>
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: alden14004_at_yahoo.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).
Received on Mon May 21 2001 - 14:32:47 CDT
