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