Thank you very much for your response.
There are about 15 tables in our database that we need to track. Also I need some transformation of the data, maybe there are several tables participating for one row of changes. So I went to trigger option.
I made a table
create table CCARE.CHANGES_EVENT_LOG
changes_event_log_id NUMBER not null,
reg_date_time DATE default sysdate
partition by range (REG_DATE_TIME)
partition D200901 values less than (TO_DATE(' 2020-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
alter table CCARE.CHANGES_EVENT_LOG
add constraint PK_CHANGES_EVENT_LOG primary key (CHANGES_EVENT_LOG_ID)
with partition on reg_date_time column for 5 days , sadly it is not Timestamp (it's not easy to make changes for this table, because we need to stop a lot of applications).
In triggers of these tables I just insert table name and identifiers of changed rows . changes_event_log_id is inserted with sequence. I thought that it would be very easy then to track changes, sawing last processed changes_event_log_id and on next iteration make select like this:
select * from CCARE.CHANGES_EVENT_LOG where changes_event_log_id>:last_changes_event_log_id
But I faced two major problems:
First of all we are using Oracle RAC with 3 nodes and I had huge gaps between changes_event_log_id, because in RAC oracle caches Sequence by nodes and because ordered sequences is another overhead... The second was Phantom rows that occurred between transactions , first transaction maybe last more time than the second, so tracking by changes_event_log_id had big issues...
I decided not to change this method and it changes status of the row (I used identifier_3_number column, because couldn't alter table on heavy load), because it's very fast and made another thread, which detects unprocessed rows
select * from CCARE.CHANGES_EVENT_LOG t where t.identifier_3_number is null
But it was very slow (about 300 msc, which is issue), so I decided make query like this;
from CCARE.CHANGES_EVENT_LOG t
where t.reg_date_time > v_last_action
and t.identifier_3_number is null
on each iteration
v_last_action = sysdate-1/24/10
and on 50th iteration
v_last_action = sysdate -1
It worked fine, but I see that on some heavy database load I have several second delays , rarely 30 or even more seconds ... in normal situation it's not more than 15-20 millisecond.
As I see, there is not more than 3000 records per second (average 30), and about 2M records per day.
Can you suggest to use CQN for tracking this table with query like this? :
select t.changes_event_log_id from CCARE.CHANGES_EVENT_LOG t where t.identifier_3_number is null
Will it have big overhead and performance degradation for database system ?
September 08, 2020 - 1:34 am UTC
Some options you could look at:
1) a custom index. So you capture rows and log them as "processed=N" and when they are processed you mark that column as null. An index on "processed" is then only the size of the *unprocessed* rows. As they get set to null, they are removed from the index. Makes it very efficient to find the rows you need to process.
2) Use AQ. Ultimately you are looking for a list of "unprocessed" changes, which aligns with a queue messaging system. But AQ can be a little resource heavy for your needs because it supports a lot of functionality
3) Even if you don't partition by it, adding a timestamp for ordering purposes should be easy because you can do:
alter table add TS timestamp default localtimestamp
and even make that column invisible if you need, and hence no application code would need to change (or even be aware of it) except your code.