Printable View
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.
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
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.Quote:
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!
This reminded me of another one from the world of Oracle puzzles.Quote:
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.
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? :D
SYSDATE is "other" type, not date, not number. The value of SYSDATE is derived by interrogating the system clock.Quote:
So how come VSIZE() returns the following?
SQL> select vsize(sysdate) from dual;
VSIZE(SYSDATE)
--------------
8
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
After thinking about this puzzle ( on and off for the last 3 days!), here is my best shot at a solution to the puzzle :)
I think the extra byte that VSIZE reports for SYSDATE is because of an extra byte reserved for the sign. Let me explain,
If you have ever played with dates from the BC era ( which I did not till Jurij raised this question!) you will remember that oracle displays dates from the BC era with a "-" sign before the date, e.g.
select vsize(to_date(to_char(sysdate,'mm/dd/syyyy'),'mm/dd/syyyy')) Siz,
to_char(sysdate,'mm/dd/syyyy') Dt
from dual
will give you the result
Siz --------- Dt
------------ -------------
8 ------------- 10/08/ 2001
Notice the blank space before 2001. Now doing the same to a date in the BC era, e.g. Jan 1, 4000 BC
select vsize(to_date(to_char(to_date('01/01/4000 bc','mm/dd/yyyy bc'),'mm/dd/syyyy'),'mm/dd/syyyy')) Siz,
to_char(to_date('01/01/4000 bc','mm/dd/yyyy bc'),'mm/dd/syyyy') Dt
from dual
will give you the result
Siz --------- Dt
------------ -------------
8 ---------- 01/01/-4000
Notice the "-" ve sign before 4000.
In view of the above, it is my humble opinion that the "extra" byte that VSIZE reports for SYSDATE, for DISPLAY purposes only (if you store SYSDATE into a col and do a VSIZE on that col, you will get 7 bytes - it is only while displaying that VSIZE reports 8 bytes), is because of the sign, which takes up one byte similar to storage for -ve numbers .
How about that for an explanation ????????? :D
-amar
P.S. Jurij, I did follow your suggestion and clicked on the link :) And guess what I found? "s" does not stand for scale, but assumes a value of either 0 or 1 depending on whether the number is +ve or -ve :D NO OFFENCE PLEASE! - amar
Gee, why should I be offended? I sincerily hope I didn't left the impression that I am being offended whenever someone doesn't agree with me :o! On the contrary, I have (and there have been quite a few such occasions on this forums) allways addmited my mistake when anyone provided *facts* that proved me wrong. And here again is such example - you are correct regarding the meaning of "s" in the formula, I was wrong! Obviously I didn't think twice when I wrote that "s" stands for "scale". It is more than evident that scale realy doesn't play any role in determining how much space is required to store a number in Oracle. I was mislead by the fact that in various formulas describing numbers in Oracle documentation usualy "p" stands for precission and "s" for scale. Well, not in this formula, here "s" stands for sign. Once again, I was wrong!Quote:
Originally posted by amar
P.S. Jurij, I did follow your suggestion and clicked on the link :) And guess what I found? "s" does not stand for scale, but assumes a value of either 0 or 1 depending on whether the number is +ve or -ve :D NO OFFENCE PLEASE! - amar
Now let's go back to the subject of VSIZE() and dates....
I would say: excelent observations, however your conclusions about the sign as a reason for the extra byte are not correct. Anyway, you spoted the exact reason for the apparent discrepancy between DATE storage format (7 bytes) and VSIZE() on SYDATE or any other function that returns DATE value (8 bytes). Let's make it more understandable by the following test:Quote:
.... In view of the above, it is my humble opinion that the "extra" byte that VSIZE reports for SYSDATE, for DISPLAY purposes only (if you store SYSDATE into a col and do a VSIZE on that col, you will get 7 bytes - it is only while displaying that VSIZE reports 8 bytes), is because of the sign, which takes up one byte similar to storage for -ve numbers .
SQL> create table test_date (c1 DATE);
Table created.
SQL> insert into test_date values(
2 to_date('01-DEC-2001 12:59:00','DD-MON-YYYY HH24:MI:SS'));
1 row created.
SQL> select vsize(to_date('01-DEC-2001 12:59:00','DD-MON-YYYY HH24:MI:SS'))
2 from dual;
VSIZE(TO_DATE('01-DEC-200112:59:00','DD-MON-YYYYHH24:MI:SS'))
-------------------------------------------------------------
8
SQL> select vsize(c1) from test_date;
VSIZE(C1)
----------
7
So it is obvious that VSIZE() returns 7 for stored dates, while it returns 8 for the dates that are returned from the functions. In other words, DATE datatype has different internal format *inside the database* (where DATE datatype requires 7 bytes) than the internal format of DATE datatype for host variables in programs is (8 bytes)! The difference in this two formats becomes more visible if we use DUMP() function. Let's see what DUMP() returns for the above two examples:
SQL> select dump(to_date('01-DEC-2001 12:59:00','DD-MON-YYYY HH24:MI:SS'))
2 from dual;
DUMP(TO_DATE('01-DEC-200112:59:00'
----------------------------------
Typ=13 Len=8: 209,7,12,1,12,59,0,0
SQL> select dump(c1) from test_date;
DUMP(C1)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,101,12,1,13,60,1
We immediately see that the types are different (type 12 represents internal DATE datatype, while I haven't found any mentioning of type 13 in the documentation). It is also evident, that first case requires 8 bytes, while the second only 7. It is also obvious that even in the first case the date itself is stored in the first 7 bytes and that in both cases first two bytes represent the year, while other 5 bytes represent month, day, hour, minutes and seconds respectively.
The internal representation of each component is different in both cases, though. In first case year is obtained by multiplying the second byte with 256 and adding the first byte (7*256+209=2001), while other date elements are stored in normal decimal notation (for example, 59 minutes is stored as 59 in 6th byte). In the second case, where we deal with how dates are realy stored in the database, the first byte represent centuries and the second one years, both in "exces-100" notation. So the year in our example is (120-100)*100 + (101-100)= 2001. Month and date are stored in normal notation as in the first case, while the hours, minutes and seconds are stored in "excess-1" notation - for example we obtain 59 minutes in our case by subtracting 1 from the value stored in the 6th byte (60-1=59).
And then there is an extra, 8th byte in the first case. Why is it used for? I don't know! So why did I say above that your conclusions about an extra byte as a "sign indicator" are wrong? Because I have played around with various dates, both BC and AD, and that extra 8th byte in the first case allways contains value 0! The "negative sign" (or BC/AD indicatior) is implicitely stored in the first byte. If the first byte contains values between 0 and 18 (if most significant bit is '0') then the date is in AD era, if it is between 237 and 255 (if MSB is '1') the date is in BC era. But in all cases that extra eight byte remains 0. So does anyone have any other idea what that byte is used for?
Bhoo woo!! There goes my theory :D
We don't have anybody from the Oracle Corp as members ??
-amar