Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36

    Unanswered: how to calculate table size?

    Hi

    I have some table XXX.

    I need to write some script (input <table name> and <n of rows>)
    that let me know how much MB table XXX will allocate
    filled out with N rows.

    Thanks in advance.

    John.
    John Smith

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    that depends on your extent size

    If your initial extent size is 64k, then a table will no rows will take up 64k
    in space until you fill up that 64k. Then it will allocate another extent.

    My suggestion is to load PRACTICAL (ie: ideal to what the real data will bel) data into the table.
    Duplicate to taste and go from there.
    Keep in mind varchar2 fields could hold a variable byte amount for each row.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    First you need to work out the expected row size. The simplest way is to populate the table with some representative data, then analyse the tables (for all indexes) and run the following....

    Code:
       select table_name, 
              avg_row_len
       from   user_tables
    
       select inds.table_name,
              inds.index_name,
              sum( inds.sizes ) as index_bytes_per_row
       from   (              
               select i.index_name, 
                      i.table_name, 
                      i.column_name,
                      decode(data_type, 'DATE'    , 7,
                                        'CHAR'    , data_length,  
                                        'VARCHAR2', decode( sign(data_length)-250, -1, .7*data_length+3, .7*data_length+1),
                                        'NUMBER'  , floor(nvl(data_precision,38)/2)+2 ) as sizes
               from   user_ind_columns i,
                      user_tab_columns t
               where  t.TABLE_NAME = i.table_name AND
                      t.COLUMN_NAME = i.COLUMN_NAME
               order by i.table_name, i.column_name
              ) inds
       group by inds.table_name, inds.index_name
    This will give you a reasonable estimate of the table size and the index size for each row. This is a good estimate, not a science.

    Then you start considering this in the context of the tablespace in which the table is stored and the tablespace in which the indexes are stored.

    AIUI if you only specify tablespace names in the storage parameters when creating the table, it will assume initial, next etc from the tablespace storage parameters. It is possible though to specify mismatched storage parameters for the table/tablespace and become quite wasteful on space.

    There also other storage parameters such as pctfree etc which have an affect on allocated size and physical disk storage space - I would recommend you look at them separately.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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