1. Junior Member
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. Junior Member
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.

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%.

SRIKANTH

3. Junior Member
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%.

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
•