
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.

Thanx
Sam
Life is a journey, not a destination!

To find the actual number of bytes occupied by a field value use the VSIZE function.
amar

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 :))

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

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:
valuebytes 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

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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

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?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

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.

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
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
