How to use sliding window on Oracle table?

2992 views sql
7

I have a table (records) getting real-time in Oracle. I need to extract data from this table and count number of entries added to the database in every minute. This statistics is then dumped to the new table 'statistics'. How can I effectively extract data in sliding window fashion?

Structure of Table:

RecordId       NUMBER(10)    ---- Unique random value for every row        
RecordTime     VARCHAR2(64)  ---- Timestamp in YYYY-MM-DD HH24:MI:SS

The query should be something similar to:

select substr(RecordTime, 1, 16), count(*) from Records group by substr(RecordTime, 1, 16);

Every minute around 500000 new rows are expected in the table, so I need to sliding window operation on this database table. If a queue-like operation is better, then please let me know about this as well.

answered question

1 Answer

12

First, it would be better to have the RecordTime as a date field so that you could create an index on it and query it. Using substr() you'd be missing out on that.

Second, a time series database like Influx is more suitable for this use case. You might consider using it.

posted this

Have an answer?

JD

Please login first before posting an answer.