# Number Data Type

Printable View

Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last
• 10-03-2001, 05:14 PM
spatemp
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.

• 10-03-2001, 05:42 PM
sambavan
• 10-03-2001, 09:22 PM
amar
To find the actual number of bytes occupied by a field value use the VSIZE function.

-amar
• 10-04-2001, 01:55 AM
julian
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 :-))

• 10-04-2001, 10:06 AM
spatemp
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

• 10-04-2001, 01:18 PM
amar
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
• 10-04-2001, 03:11 PM
jmodic
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!

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.
• 10-04-2001, 03:24 PM
jmodic
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.
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? :D
• 10-05-2001, 06:42 AM
julian
Quote:

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-05-2001, 11:15 AM
jmodic
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
Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last