[2010-02 note: see footnote]

Regarding storage size for a number column, Oracle gives the formula at
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1833

"the column size in bytes for a particular numeric data value NUMBER(p), where
p is the precision of a given value, can be calculated using the following
formula:

ROUND((length(p)+s)/2))+1

where s equals zero if the number is positive, and s equals 1 if the number is
negative."

I quoted 11gR1 documentation but it's the same since at least 8i on. [20090904
update: 11gR2 documentation no longer has this subsection "Internal Numeric
Format" or this formula.]

There're a few mistakes in this formula and its interpretation.

(1) Parentheses don't match. We can drop the rightmost closing parenthesis:
ROUND((length(p)+s)/2)+1
Alternatively, we can drop the parenthesis to the right of scale, with a 
totally different calculation though:
ROUND((length(p)+s/2))+1
But I don't think this is what they intended. The double opening and closing 
parentheses become unnecessary.

(2) Since p is already the precision, defined as "total number of digits", 
length(p) makes no sense. E.g., for 123.1, p is 4; length(p)=length(4) would be 
1. We should replace length(p) with just p. (Length(the_number) is not right 
either since it's 1 more than precision if the number contains a decimal 
point.) Now the formula looks like:
ROUND((p+s)/2)+1

(3) How can it be that "s equals zero if the number is positive"? For 123.1, s, 
defined as "number of digits to the right of the decimal point" earlier in 
documentation, is clearly 1.

(4) How can it be that "s equals 1 if the number is negative"?

Regarding (3) and (4), there's a sentence just above the formula "Negative 
numbers include the sign in their length". I think the documentation writer 
accidentally dropped the last part in the formula, "+sign". So it really should 
look like:
ROUND((p+s)/2)+1+sign
where sign equals zero if the number is positive, and sign equals 1 if the 
number is negative.

However, the modified formula still doesn't take into account of exponent. 
vsize(110) is 3 while vsize(1100) is 2, because they're 1.1 x 10^2 and 1.1 x 
10^3, respectively. But applying the (modified) formula would yield the same 
value, ROUND((3+0)/2)+1+0=3 and ROUND((4+0)/2)+1+0=3, respectively.


[2010-02 note: I didn't notice the email sent from Oracle Documentation Project 
Manager till not too long ago. I think the content can be publicized, except for 
perhaps the email addresses and even the names so I blank them out.]

Subject:   	[Fwd: Re: User comment forwarded by moderator - Formula ROUND((length(p)+s)/2))+1 - Concepts (b28318)]
From:   	"xxx" <...@oracle.com>
Date:   	Sat, December 20, 2008 12:47 am
To:   	yong321@freeshell.org

Hello yong321. I don't know if I'm sending to the correct email address, 
because you didn't mention it in the feedback. But we appreciate the 
time you've taken on this topic, so I'd like to reply.

The material you mention has been removed from Concepts. The SQL 
Language Reference has the following description of the ROUND function:

    ROUND is implemented using the following rules:

       1. If n is 0, then ROUND always returns 0 regardless of integer.
       2. If n is negative, then ROUND(n, integer) returns -ROUND(-n,
          integer).
       3. If n is positive, then

        ROUND(n, integer) = FLOOR(n * POWER(10, integer) + 0.5) *
        POWER(10, -integer)

This is not the same as the corrections provided in your notes -- in 
fact it seems to be a different way of presenting this entirely. But I 
hope it appears correct. If not, please feel free to email my directly 
and I'll follow up with Oracle Development.

Thanks for your feedback,
Diana



-------- Original Message --------
Subject:         Re: User comment forwarded by moderator - Formula 
ROUND((length(p)+s)/2))+1 - Concepts (b28318)
Date:         Fri, 19 Dec 2008 14:31:45 -0800
From:         yyy <...@oracle.com>
To:         xxx <...@oracle.com>



...@oracle.com wrote:
> Subject: Formula ROUND((length(p)+s)/2))+1
> Submitter: yong321
> Book title: Oracle Database Concepts
> Part number: b28318
> Release: 11g Release 1 (11.1)
> Topic title: Oracle Data Types
> URL: http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm
> Status: Forwarded
> Submitted on: 13-DEC-08
> Comment ID: 16161
>
> It's been wrong since 8i (or earlier) documentation. See
> http://yong321.freeshell.org/oranotes/NumberDataStorageLength.txt
>
> Yong Huang
> yong321@yahoo.com
>
>   


-- 
=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/
xxx, Documentation Project Manager
Information Development, Server Technologies Division, Oracle
Working off site
e-mail: ...@oracle.com