Data type size:

[badrinathn]

Hi,

I am trying to calculate the exact number of bytes, each of my records in the table . Could any one give me the bytesize for these data types.

Elementary question,never mind

Char = ? bytes
varchar2 = ?bytes
number(9) = ?bytes
number(9,2) = ?bytes
number = ?bytes
date = ?bytes
boolean = ?bytes
long = ?bytes

Thanks In advance
Badrinath

[sambavan]

Char(size) where size in bytes max=2000 min=1
varchar2(size) size in bytes max=4000 min=1
number(precision,scale) precision= 1 to 38 scale = -84 to 127
date range from January 1, 4712 BC to December 31, 9999 AD
boolean ?
long upto 2GB

Hope this would help you.
Sam

[jmodic]

Well, the actual size of your rows depends on a data that is in them. By merely looking at datatype of your columns you can't conclude how much space the row will occupy. One row in a table could occupy only 1 byte, while the other row in the *same* table could occupy hundreds or thousands or milions of bytes!

- For character datatypes, the space occupied by data depends on the database characterset, but usualy the number of bytes will be equal to the length of the contained string (for CHAR it will be equal to the length of the column).
- For dates it will always be 7 bytes.
- Boolean is not a database type, so you won't have any column of that type.
- For numbers it is a little tricky to calculate the space they consume. For example, 1,000,000,000 will ocupy (much) less space than 1.923968. You can find an actual formula to calculate the required number of bytes for each number in Oracle Concepts manual.
- Any null value (of any data taype) requires 1 byte of space, unless there are only null values in all following columns to the end of the records, in which case it does not ocuppy any space at all.

In any case, you can use a VSIZE() function to find out the number of bytes occupied by any calumn value.

[sambavan]

Thanx for clearing this concept Modic.
Sam

[pando]

what happens if I set the value of datatype much larger than it really needs?

For example varchar2(10) would be enough for a column of a table but I set it to varchar2(2000), would this have a negative effect?

[jmodic]

No, it will not have any affect, neither to space usage nor to performance.

So why don't we use VARCHAR2(4000) all the time? Because the declared size of a varchar2 column is a kind of constraint, we set the maximum length of the string that can ever be inserted in it. It is the same as specifying/not specifying NOT NULL constraint or any other constraint for that mater. We simply set a rule and let the database enforce it.