Data type size:

Join Date
Jan 2001
Posts
642
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

Join Date
Oct 2000
Location
Posts
3,925
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

Sam

3. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
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.

Join Date
Oct 2000
Location
Posts
3,925
Thanx for clearing this concept Modic.
Sam

5. Pando & Company
Join Date
Jun 2000
Location
Posts
7,447
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?

6. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•