formula to calculate row size accurately
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: formula to calculate row size accurately

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Posts
    120

    formula to calculate row size accurately

    hi all,

    i want to know the row size of a given table. i am querying the table called dba_tab_columns and summing up all the data length after grouping by table name. This gives me the total size of the row for that table. The question that i want to ask is is this size the exact row size or does oracle allocate some extra space for each rows? If it does then pls mention how much or what percentage of row size it stores as free space.

    The other question i want to ask is how can we calculate the average row size based on the present lot of data for a given table? Its not always that the data for a row would completely fill it up. So a percentage of row space will always be free. If we can calculate the average row space based on the present lot of data then we can be sure that the row size for a table would be say 75% of the total row size and by doing this we can accurately calculate how much the tables will grow in future.

    pls give me ur guidance regarding how to do this.

    thanks


    Parijat Paul

  2. #2
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97
    To calculate free size on table.
    The average of the sizes of column is the placed size divided for the total lines.


    Select SUBSTR (owner, 1, 20) "Owner",
    SUBSTR (tabela, 1, 20) "Table",
    TO_CHAR (SUM (blocos) , '999,999,999') "Blocks Used",
    TO_CHAR (SUM (vazios) , '999,999,999') "Blocks Empty",
    TO_CHAR (SUM (alocado) / 1048576, '99,999.9999') "Alocad MB",
    TO_CHAR (SUM (blocos + vazios) , '999,999,999') "Total Blocks",
    TO_CHAR (SUM (alocado) / SUM (blocos + vazios) / 1024, '999,999.9999') "Size Block KB",
    TO_CHAR((SUM (alocado) / SUM (blocos + vazios)) * SUM (vazios) / 1048576, '999,999.9999') "Empty MB"
    From (
    Select owner, table_name tabela, blocks blocos, empty_blocks vazios, 0 alocado
    from dba_tables
    Where owner not in ('SYS', 'SYSTEM')
    union
    Select owner, segment_name tabela, 0 blocos, 0 vazios, bytes alocado
    from dba_segments
    Where owner not in ('SYS', 'SYSTEM') and segment_type = 'TABLE'
    )
    group by Owner, Tabela
    having SUM (blocos) > 0
    ;
    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 for ur post.

    i want to know what is the average row size of a given table. pls give me a formula which will read the present data of a given table and calculate the average(filled) row size for that table.

    thanks

    Paul

  4. #4
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97
    if your tables have analized, your get column num_rows from dba_tables and divide.
    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