1. Junior Member
Join Date
Jul 2000
Location
Atlanta
Posts
15
Hi,

I am trying to figure out how big of a number can you store in Number datatype. I know that the precision can be from 1 to 38. Also I would like to know/understand what the size in Bytes would it be in the following cases.

1) number
2) number(2)
3) number(4)
4) number(6) ... and so on

Thanks for all the help.

Join Date
Oct 2000
Location
Posts
3,925

3. Senior Member
Join Date
Mar 2001
Posts
314
To find the actual number of bytes occupied by a field value use the VSIZE function.

-amar

Join Date
Jun 2001
Location
Helsinki. Finland
Posts
3,938
Examples:

7 needs 2 bytes: 1 for the digit and 1 for exponent
-7 needs 3 bytes: 1 for the digit, 1 for exponent, 1 for the sign
777 needs 3 bytes: 2 for digits and 1 for the exponent
7700 needs 2 bytes (why :-))

5. Junior Member
Join Date
Jul 2000
Location
Atlanta
Posts
15
Thank you all very much for the help. The below is what I was looking for which I found from this link: http://oradoc.photo.net/ora816/serve...0datyp.htm#743

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

6. Senior Member
Join Date
Mar 2001
Posts
314
I would stick to VSIZE while trying to find out the number of bytes internally occupied by oracle to store a certain value. I don't know how the formula works ( length(p) = what? ) - tried to figure out though. Take a look at the following:

value----------bytes needed using vsize
------- -----------------------------

1 -------------> 2
10 ------------> 2
100 -----------> 2
1000 ----------> 2
10000 ---------> 2
100000 --------> 2
100001 --------> 4
876943 ---------> 4
-1 ---------------> 3

The reason is :

Oracle stores a number in base 100 format
Each byte can store 2 digits
One byte is always reserved for exponent
For -ve numbers an additional byte is reqd (for sign)

Thus, for Julian's number:

7700 needs 2 bytes because:
the digits 77 need one byte
the exponent 00 needs one byte
thus 7700 needs 2 bytes

This holds true for all numbers - as long as there are trailing zeroes, the space requirement does not increase.

I would be interested in an explanation of the formula if anyone has any please If oracle has it in the doc's there would be a logic behind it I think!

-amar

7. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Originally posted by amar
I would be interested in an explanation of the formula if anyone has any please If oracle has it in the doc's there would be a logic behind it I think!
The formula is explained in the Concepts manual (just follow the URL spatemp has provided, just clik on it ). p represents precision, s represents scale.

8. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Originally posted by amar
I would stick to VSIZE while trying to find out the number of bytes internally occupied by oracle to store a certain value.
This reminded me of another one from the world of Oracle puzzles.

It is well known fact that dates occupy 7 bytes of storage in Oracle RDBMS - one byte for each of the following date elements: century, year, month, day, hour, minute, and second.

So how come VSIZE() returns the following?

SQL> select vsize(sysdate) from dual;

VSIZE(SYSDATE)
--------------
8

So, is it 7 or 8 bytes for dates? Are the manuals wrong (no, they are not!)? Does VSIZE() sometimes return incorect value (no, it does not!)? Any takers to explain this?

Join Date
Jun 2001
Location
Helsinki. Finland
Posts
3,938
So how come VSIZE() returns the following?

SQL> select vsize(sysdate) from dual;

VSIZE(SYSDATE)
--------------
8

SYSDATE is "other" type, not date, not number. The value of SYSDATE is derived by interrogating the system clock.

10. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Well, I still think SYSDATE is a perfect example of DATE datatype. Scalar datatypes are defined in very exact way in Oracle, there is no such type as "other" type . Anyway, of what datatype is the following expression?

to_date('01.01.2001','dd.mm.yyyy')

Another perfect example of DATE datatype, I would say. And it has nothing to do with system clock this time. So how come VSIZE() again returns 8 for it?

SQL> select vsize(to_date('01.01.2001','dd.mm.yyyy')) from dual;

VSIZE(TO_DATE('01.01.2001','DD.MM.YYYY'))
-----------------------------------------
8

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•