how to calculate the storage volume of a database

Navigation
0

This is the following, I'm having concerns about a database being used in a project. The project consists of the development of a software for joining respective data to two solar spectrum sensors, and then storing them in a database along with other information from a "master" table. The concept is that for every row in the master table there will be thousands of rows in the spectrum table.

SUMMARY

We have the following diagram:

The problem I'm facing is that the rows in the master table will be added 24/7 a minute, and the rows in the esp table will be inserted more than a thousand at a time, but only 5 times a day, with a range of one hour.

PROBLEM

It may have been a bit confusing, but the biggest concern is not understanding this diagram.

What I want to do is calculate what the average volume of data that will go into the bank will be by year / month / week. is there a formula or a way to calculate how much space I will need to store this data?

Thank you!

    
asked by anonymous 02.06.2017 / 18:29

1 answer

1

First you need to identify the size in bytes of each line. With this you will be able to identify, given the number of rows in the table, the size of the table.

In Oracle I use query q below to get the total size of the table and dividing by the number of rows I get the value per row and then it is to follow with the estimate: 

SELECT
   owner, 
   table_name, 
   TRUNC(sum(bytes)/1024/1024) Meg,
    ROUND(sum(bytes)/1024/1024/1024, 2) GB,
   ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE UPPER( owner) in UPPER('MY_OWNER')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc
    
02.06.2017 / 21:15
Closing app that are in the background Merge upload and input ajax