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