CODEWITHSUNDEEP
oracle-databasedatabase-performance
Marllon Nasser
Marllon Nasser

Reputation: 390

Best way to create tables for huge data using oracle

Functional requirement

We kinda work for devices. Each device, roughly speaking, has its unique identifier, an IP address, and a type.

I have a routine that pings all devices that has an IP address. This routine is nothing more than a C# console application, which runs every 3 minutes trying to ping the IP address of each device. The result of the ping I need to store in the database, as well as the date of verification (regardless of the result of the ping).

Then we got into the technical side.

Technical part:

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

Both should return the same thing:

Understood to be unavailable the device to be pinged AND did not responded. It is understood by available device to be pinged AND answered successfully.

What I have today and works very badly:

A table with the following structure:

create table history (id_device number, response number, date date);

This table has a large amount of data (now has 60 million, but the trend is always grow exponentially)

** Here are the questions: **

Upvotes: 0

Views: 1353

Answers (1)

Art Trifonov
Art Trifonov

Reputation: 220

Partition the table based on date. For partitioning strategy consider performance vs maintanence. For easy mainanence use automatic INTERVAL partitions by month or week. You can even do it by day or manually pre-define 2 day intervals. You query only needs 2 calendar days. 

select id_device, 
       min(case when response is null then 'N' else 'Y' end), 
       max(case when response is not null then date end)
from history
where date > sysdate - 1
group by id_device
having min(case when response is null then 'N' else 'Y' end) = 'N'
   and sysdate - max(case when response is not null then date end) > ?;

If for missing responses you write a default value instead of NULL, you may try building it as an index-organized table.

You need to read about Oracle partitioning.

This statement will create your HISTORY table partitioned by calendar day.

create table history (id_device number, response number, date date) 
  PARTITION BY RANGE (date) 
  INTERVAL(NUMTOYMINTERVAL(1, 'DAY'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('5-24-2016', 'DD-MM-YYYY')),
      PARTITION p1 VALUES LESS THAN (TO_DATE('5-25-2016', 'DD-MM-YYYY'));

All your old data will be in P0 partition. Starting 5/24/2016 a new partition will be automatically created each day. HISTORY now is a single logical object but physically it is a collection of identical tables stacked on top of each other. Because each partitions data is stored separately, when a query asks for one day worth of data, only a single partition needs to be scanned.

Upvotes: 1

Related Questions