how to estimate table size?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: how to estimate table size?

  1. #1
    Join Date
    Dec 2001
    Posts
    120

    how to estimate table size?

    Hi everybody,

    i want to estimate the table sizes for each of my tables before inserting 1 million records in each of them. I also want to estimate the corresponding size of the indices. could any one of you pls guide me on how to go about it? Pls take the example of Employee table and mention the steps. At present I am taking the help of TOAD to estimate the table sizes. I want to know how can we do it without taking help from any of the tools.

    The motive behind doing all this is to foretell the clients about the expected size of database after they have fed in i million records in each of the tables.

    thanks a lot

    Parijat Paul

  2. #2
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97
    select table_name table, (sum (data_length) / 1048576) * 1000000 "Length MB"
    from dba_tab_columns
    where owner = 'owner'
    group by table_name
    ;
    Márcio de Souza Almeida
    DBA Oracle / SQLServer / PostgreSQL
    Rua Cupa, 139 Apto 85 A
    Penha - São Paulo - SP
    03640-000 - Brasil
    http://www.directory.com.br

  3. #3
    Join Date
    Dec 2001
    Posts
    120
    hi,

    thanks a lot for your mail. i ran this query of yours and found that the size of only one record in the following table "TB_PROPERTIES_TEXT" will be 160 bytes and it would be 152MB for 1 million records. Pls mention about how oracle is reaching upto this figure of 160 Bytes. I am providing the table structure so that you can do the exact calculations based on the column datatype.

    TB_PROPERTIES_TEXT
    -----------------------------
    COMPANYID number
    OBJECTTYPEID number
    OBJECTID number
    PROPERTYID number
    SEQUENCE number
    VALUEPROPERTY varchar2(50)

    Pls also mention about how to calculate the index sizes for a given table.

    thanks once again

    Parijat Paul

  4. #4
    Join Date
    Dec 2001
    Posts
    120
    well i got the answer to my question.. its (22*5 + 50) = 160 bytes.
    But this is the total maximum size for a row of data and most of the time this wont be fully occupied by a given row. So, to plan the table sizing in a reasonable way, my question is how can we estimate the size of a new row based on the average size of existing data in the table?

    thanks

    Parijat Paul

  5. #5
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97
    I made the test and it did not give to 160KB and yes 160 MB. I have the habit esteem the size of the short while bigger table of the one than its initial size, this prevents spalling and for the NEXT I normally place I eat 10% you the total size of the table.

    SQL> Create table teste
    2 (COMPANYID number,
    3 OBJECTTYPEID number,
    4 OBJECTID number,
    5 PROPERTYID number,
    6 SEQUENCE number,
    7 VALUEPROPERTY varchar2(50))
    8 ;

    Table created.

    SQL> select table_name tabela, (sum (data_length) / 1048576) * 1000000 "Length MB"
    2 from dba_tab_columns
    3 where owner = 'MARCIO'
    4 group by table_name
    5 ;

    TABELA Length MB
    ------------------------------ ----------
    PLAN_TABLE 1566.8869
    TESTE 152.587891
    Márcio de Souza Almeida
    DBA Oracle / SQLServer / PostgreSQL
    Rua Cupa, 139 Apto 85 A
    Penha - São Paulo - SP
    03640-000 - Brasil
    http://www.directory.com.br

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