Sometimes, immediately after designing a database schema we have to define the amount of disk space required to run the application. Even before testing it.
At first glance this can be quite difficult because it seems to be so many variables.
However if we are able to figure out how many records each table is going to store, 99% of our problem is going to be solved.
1) So, first of all..
We should list the tables and the number of records we estimate for each one, for the first year, the second and so on.
TABLE_NAME || REGS_1Y || REGS_2Y || REGS_3Y
table_A 500 1000 1500
table_B 10000 20000 30000
2) Now we need access..
To a Database with all these tables. Each table should have at least one record and every column should be filled up the way it is going to happen in production.
This means that if we have a varchar2(100) column but in general only 50 characters are going to be used, we should insert a record with 50 characters length for this column.
If we have records that are not representative in a table, we should delete them.
3) Then run statistics..
Run statistics for each table:
analyze table TABLE_NAME compute statistics;
this process will calculate the average row length of each table among other things.
4) Now access a system view..
…in order to get the average row length.
select num_rows, avg_row_len from user_tables where table_name=’TABLE_NAME’;
5) now some simple math..
avg_row_len is the size in bytes of the record, so
((( avg_row_len * REGS_1Y) / 1024 ) / 1024 )) is the size in MB each table is going have after the first year according to our estimation.
We can repeat this excercise to fill up the chart for the rest of the years and we will have the size estimation for the tables.
TABLE_NAME || REGS 1Y || TABLES 1Y || REGS 2Y || TABLES 2Y || REGS 3Y || TABLES 3Y
However we can also find indexes in a database schema, and you should keep this in mind because in general, indexes consume more space than tables.
6) So to finish..
..the size estimation we could calculate the size of each index but there are many indexes that will have not probably been created yet, so a good alternative is to estimate for indexes 3 times the space we estimated for the table.
TABLE_NAME || REGS 1Y || TABLES 1Y || INDEXES 1Y || REGS 2Y || TABLES 2Y || INDEXES 2Y || REGS 3Y || TABLES 3Y || INDEXES 3Y
So now we can give a first estimation for the space required by the database schema, considering the size of each table and indexes.
