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

Thread: Table Size calculation

  1. #1
    Join Date
    Aug 2000
    Posts
    33
    Friends,

    Can anyone tell me how to calculate the size of table ? Is there any particular formula , ofcours in DB Admin manual, it has been given. For calculating average size of a table what is the formula ?

    Example : table has five columns and each is

    (col1 char(5), col2 varchar(10),col3 datetime, col4 char(5), col5 varchar(20).

    According to the above said structure the size of the table will be (5+10+7+5+20)/5 to get the average size.

    Is the approach is right or wrong ? Is there any other way to find/calculate the table size.

    TIA
    Ravs

  2. #2
    Join Date
    Nov 2000
    Posts
    57
    FIRST OF ALL IT ALL TABLE STORAGE DEPENDS UPON THE SITUATION.

    METHOD 1:
    CALCULATE THE BYTES OF A TABLE FOR EACH ROW,

    SUPPOSE THE TABLE WILL HAVE ABOUT 1 LAC RECORDS THEN,
    TOTAL BYTES * 100000/1024/1024 WILL GIVE THE ANSWER IN MB

    SO WE CAN GIVE THE INITAIL EXTENT OF THE TABLE THROUGH THE ARRIVED ANSWER.

    NEXT EXTENT =INITAIL EXTENT*1.25 WILL BE THE SIZE OF THE NEXT EXTENT OF THE TABLE.


    METHOD 2:

    CREATE TABLE.

    INSERT ABOUT 1000 ROWS

    ANALYZE TABLE <TABLE NAME> COMPUTE STATISTICS

    SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,NUM_ROWS/BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='<TABLE NAME>';

    FROM THIS THIS YOU WILL GET THE NUMBER OF ROWS PER BLOCK. EXAMPLE 100 ROWS

    SO THAT MEANS HERE FOR 2048 BYTES THERE ARE ABOUT 100 ROWS.

    FOR 1 LAC ROWS MEANS (100000*2048)/100 BYTES = 2048000 BYTES

    => 2048000/1024/1024 = 2 MB (APPROX)


    THIS WILL BE YOUR INITIAL EXTENT VALUE FOR THE TABLE.

    FOR THE NEXT EXTENT SIZE INCREASCE THIS VALUE BY 25%.

    HOPE THIS WILL HELP YOU.
    SRIKANTH

  3. #3
    Join Date
    Nov 2000
    Posts
    57

    Table Size calculation

    METHOD 1:

    CALCULATE THE BYTES OF A TABLE FOR EACH ROW.

    SUPPOSE THE TABLE WILL HAVE ABOUT 1 LAC RECODS THEN,
    TOTAL BYTES OF A ROW*100000 = THE ANSWER WILL BE IN BYTES
    DIVIDE THE ANSWER/1024/1024 WHICH WILL BE IN MB

    SO WE CAN GIVE THE INITIAL EXTENT OF THE TABLE FROM THE MB ARRIVED.

    NEXT EXTENT =INITAIL EXTENT*1.25 WILL BE THE SIZE OF THE NEXT EXTENT OF THE TABLE.


    METHOD 2:

    CREATE A TABLE

    ANALYZE TABLE EMP COMPUTE STATISTICS

    SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,NUM_ROWS/BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='EMP';

    FROM THE QUERY YOU WILL GET THE NUMBER OF ROWS PER BLOCK SAY AROUND 25 ROWS PER BLOCK.

    SO THAT MEANS HERE FOR 2048 BYTES THERE ARE ABOUT 25 ROWS.

    FOR 1 LAC ROWS MEANS (100000*2048)/25 BYTES = 8192000

    => 8192000/1024/1024 = 8 MB(APPROX)


    THIS WILL BE YOUR INITIAL EXTENT VALUE FOR THE TABLE.

    FOR THE NEXT EXTENT SIZE INCREASCE THIS VALUE BY 25%.

    HOPE THIS WILL HELP YOU.

    THANKS,
    SRIKANTH


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