Re: How many bytes does the datatype NUMBER use?

> Greetings All, I was hoping that someone might be able to tell
> me how many bytes the datatype "number" uses. If it is
dynamic
> what is the method of determinig how many bytes will be used?
>
> Thank You, Louis
> frolio_at_videoshare.com

a NUMBER column (within a row within a table datablock) will occupy
(at most) 22 bytes of storage.

I've not found the answer to this in the Oracle manuals or the Oracle
Press books. If anyone knows which manual and chapter this is in,
please let me know.

We do know this: a column of type NUMBER is stored internally in a proprietary floating point format, and the maximum number of digits
in a NUMBER is 38. float = (sign, mantissa, exponent)

If we assume that Oracle packs two digits into each byte of storage
(in their proprietary floating point format), then that'd take 19 bytes to
store all 38 significant digits (ie. the mantissa). Oracle also needs to
store the sign, and the position of the decimal point (ie. the exponent).
So 19 bytes for the mantissa and another 3 bytes for sign, exponent
and any required housekeeping... that jives with the 22 bytes reported
in the DBA_TAB_COLUMNS and DBA_IND_COLUMNS views...

But I'm thinking that part of the "required housekeeping" may be to
keep track of the number of significant digits, to save space when all
38 digits are not needed)... similar to the way that the 1-byte or 3-byte
length field is used to save space for a VARCHAR2 column...

i've done some limited tests, creating tables with variations of columns
of type NUMBER(p,s)

create table test_n15 (test_col NUMBER(15,2)) ...
create table test_n92 (test_col NUMBER(9)) ...
create table test_n38 (test_col NUMBER(38)) ...

and loading the tables with identical data, and then running "analyze table
... compute statistics", and quering DBA_TABLES to check the average
row length...

i found that if i loaded the tables with all zeros, the average row length of
all of the tables is identical. when the tables are loaded with larger values
(with more significant digits) the average row length for all of the tables
increased identically. with really, really big values, the average row length
got even larger.

this leads me to a conclusion that all columns of type NUMBER(p,s) are
stored simply as NUMBER. The (p,s) define restrictions that are enforced
when a value is assigned to the column, but do not have any effect on the
internal representation within the datablock.

When I created an INDEX on test_col, the index used significantly more
space than the original table. Also, the total amount of space allocated
to the index did not seem to vary with the values loaded into the table,
like the average row length of the table did.

This leads me to believe that the representation of a NUMBER in an index
datablock may not be identical to representation in the table datablock.
Possibly, in an INDEX, a NUMBER may be represented as a constant 22 bytes. If so, this probably is a performance issue...

(note: the tests were performed with Oracle 8.0.5 on Windows NT.)

Again, I have _not_ found this information documented in the Oracle manuals
or the Oracle Press books...

but, bottomline, at most, a NUMBER column will occupy 22 bytes of
storage within a datablock. Received on Tue Feb 08 2000 - 23:28:18 CST