Best way to structure history table with large amount of data

Navigation
3

Functional requirement

I have devices . Each device, roughly, has its unique identifier, an IP address, and a type.

I have a pings routine for every device that has an ip address. This routine is nothing more than a C # executable, which runs every 3 minutes and attempts to ping the device's IP address.

The result of ping I need to save to the database, as well as the date of the check (regardless of the ping result).

Technical part:

Assuming my ping and bank structuring process is ready from 01/06/2016, I need to do two things:

  • Daily extraction (which is a fed table)
  • Extraction in real time (last 24 hours)

Both should return the same thing to me:

  • Devices that have been unavailable for more than 24 hours.
  • Devices that have been unavailable for more than 7 days.

The device that is pinged does not respond to ping .

It is understood that the device that pinged successfully responded ping .

What I have today and it works very badly:

A historico_disponibilidade table, with the following structure: 

create table historico_disponibilidade (id_dispositivo number, respondeu number, data date);

This table has a large amount of data (today it has 60 million, but the tendency is to grow exponentially)

The questions:

  • How to achieve such goals without running into problems of slowness in queries?
  • How to create a table structure (s) that is prepared to receive millions / billions of records within my corporate universe?
asked by anonymous 24.05.2016 / 16:26

1 answer

2

The problem does not seem to be exponential. There are some that are, there is not much to do. If there is an exponential implementation, you have to solve this, but the question does not make it clear how it is being done. It seems to me that neither is the case.

The volume of rows in a table should affect very little the performance of queries , as long as it has the necessary indexes and the queries themselves do not do very weird things.

Even if it does, the solution would be to erase the data you no longer need. If you need to keep the data for historical purposes only, and no longer need to keep queries on the old data (at least not often) the solution is to have an auxiliary table that will work as a archive. There will be a transfer from the normal table to that of the archive. Apparently you just need to keep the last 7 days. I do not even need to say that this transfer can be automated. You can use ranges of data to partition tables but I do not know how this works in Oracle.

All the good databases on the market, especially Oracle, are poised to work with billions of lines without problems. If you have a specific problem in a query and you are not finding a solution (probably with index) then it's worth posting on a specific question.

Note that the table is so simple that it has nothing more to simplify. Unless you have things hidden in the question.

I wonder if the whole routine is wrong. It seems weird to do what is being done, I think it is the right solution to the wrong problem. But I will not go into merit.

    
24.05.2016 / 16:43
How to format a String or put mascara with Totalcross Disable form submit button inside a modal bootstrap window