Database Table Sizing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Database Table Sizing

Hybrid View

  1. #1
    Join Date
    Feb 2006
    Posts
    1

    Database Table Sizing

    Hi all,

    I am not very knowledgable in this area so would appreciate any advice you guys can give.

    I need to provide a projected estimate of Oracle db tables. Essentially my problem is in trying to work out the size of one single row. I have a table which uses the varchar2, integer and date datatypes but I cannot seem to find anywhere how many storage bytes each of these use (with the exception of varchar).

    As far as I understand for integers and numbers it depends on the actual number entered - is this correct? If so - is there anyway I can estimate this based on the data I know will be entered.

    Also, I read somewhere that date takes 7 bytes of storage - is this correct?

    All help much appreicated,
    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    date = 8 bytes (I think, might be 7) as for varchar and number, it depends on the actual string (not the size of the column) so take a typical row and count the number of charcters, add in the value for aa date column and you are there.

    Then you need to work out indexes which is tricky

  3. #3
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    I need to provide a projected estimate of Oracle db tables. Essentially my problem is in trying to work out the size of one single row. I have a table which uses the varchar2, integer and date datatypes but I cannot seem to find anywhere how many storage bytes each of these use (with the exception of varchar).
    Since you want projected estimate, you have to calculate the size of the data types for the table columns, like
    C1 varchar2(21), --size 21 (variable)
    C2 number, --size 38 (variable)
    C3 date --size 7 (fixed)
    A row will occupy c1+c2+c3 size (projected size) but actuall size is what the c1 and c2 is going to occupy depend on the data + the c3.
    "What is past is PROLOGUE"

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Date is 7 bytes length.
    Code:
    SQL> create table t1 (rdate date);
    
    Table created.
    
    SQL> insert into t1 values (sysdate);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select dump(rdate) from t1 ;
    
    DUMP(RDATE)
    --------------------------------------------------------------------------------
    Typ=12 Len=7: 120,106,2,23,15,60,57
    Tamil

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    1) create your table
    2) insert 10000 rows in your table with real data (e.g. a name has in most cases less then 50 characters even if the name field is defined as varchar2(50))
    3) calculate the needed space per row

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