-
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
-
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 COMPUTE STATISTICS
SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,NUM_ROWS/BLOCKS FROM DBA_TABLES WHERE 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
-
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
