DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Data type size:

  1. #1
    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

    Thanks In advance
    Badrinath

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    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

    Hope this would help you.
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Thanx for clearing this concept Modic.
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    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. #6
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width