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.