Simple question

[samdba]

Hi all,

What is the default column size for data type NUMBER.

Eg. When we say DATE, column length is 7 bytes. Similarly, in case of NUMBER, how much size is reserved.

I did not find much documentation on this.

Thanks in Adv.

[sambavan]

http://technet.oracle.com/docs/produ...typ.htm#427846

On a 32bit OS its 8byts I presume.

Good discussion:
http://dbasupport.com/forums/showthr...threadid=17375

Sam

[Edited by sambavan on 02-25-2002 at 06:06 PM]

[jmodic]

There is no default column size for column of type NUMBER, i.e. no space is reserved in database for nonexisting values (NULLs) of type NUMBER.

When you store some values in such column, the physical space used by those values depends on the actual values stored. It can vary anywhere from 1 byte to 21 bytes.

You can check the size used by values stored in your table by isuing:

SELECT col1, VSIZE(col1) bytes_used FROM my_table;

You can find out how much space your number will use if stored in the table by isuing the following (example for number -123.4567):

SELECT VSIZE(-123.4567) FROM dual;

You can calculate the space used by numeric data by using formula in the following URL:
http://technet.oracle.com/docs/produ...0datyp.htm#743

[samdba]

While doing table sizing, if the table designer does not give any length and just gives Col1 NUMBER, how much bytes should I take while doing new table sizing.

Thanks,

[samdba]

Hi guys,

Any update plz...

Thanks,

[shadow21]

Look at Jurij's answer, I thing everything is there...

[tamilselvan]

By default 22 bytes are reserved for the column whose type is NUMBER.

[jmodic]

Originally posted by tamilselvan
By default 22 bytes are reserved for the column whose type is NUMBER.

By default,0 bytes are reserved for the column, no matter of what type it is!

[SANJAY_G]

From the docs:
Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with one byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with one byte used to store the exponent (2) and two bytes used to store the three significant digits of the mantissa (4, 1, 2). Negative numbers include the sign in their length.

Taking this into account, 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


where s equals zero if the number is positive and s equals 1 if the number is negative.

Zero and positive and negative infinity (only generated on import from Version 5 Oracle databases) are stored using unique representations. Zero and negative infinity each require one byte; positive infinity requires two bytes

I am still not clear if anything is reserved for NULLS??

Sanjay

[jmodic]

[QUOTE]Originally posted by SANJAY_G

I am still not clear if anything is reserved for NULLS??

Nothing is reserved for NULL value if this column is the last one in a table or if all other trailing columns contain only NULL values. If any column following this NULL value contains any non-NULL value then 1 byte is reserved for this NULL value column.