A solution for oracle table data post processing without bad trigger
In some cases, we receive the data in table from the front end and do some tasks after that.
The problem is we do not know when the data changed.
Some people will add the tasks in table trigger.
This method is simple,
but the session of the data souce will not finish before these tasks done.
When there is any exception in the trigger, all actions might be rollback.
Some people will add a job at a proximate time, and another job is executed later to monitor the first job.
This is not a good method to handle any possible exception.
I implemented a method 4 years ago when I was hired by my current company.
Creating a job to monitor the table which checks the data changes and does the tasks when the data changed.
This method required the programming logic which has awareness of data changes and a short interval job to be fast response to data change.
This is a complex implement,
so there are many way to do performance tuning on this monitoring, checking and data processing.
These tuning are necessary to avoid db overhead.
Since the mechanism was enhanced,
I don't have to spend too many time on this routine job,
then I can do some really important things, for ex. enhance legacy systems or develope new requests.
Today The DBA ask me to reduce this kind of frequent job.
It's not easy.
The goals :
1. keep the fast response of data changes (in 2 minutes)
2. stay clean with the front end data sources (don't do task in trigger)
3. wait for all front end data sources finished (2 for now)
4. low job frequency (new request)
Fortunately, I implemented a good data changes checking mechanism already.
Each frontend data source will insert it's name and current time to a signing table.
The current mechanism is to check this signing table every minute,
and do the following tasks when all frontend is signed in a time period.
In few minutes, an idea occurred to me.
I could add a trigger on the signing table which does nothing but
'dbms_job.next_date(1, SYSDATE + 1/1440);'.
'1' is the current job no.
Since the job could check the multi frontend in 0.5 sec,
the post process will not execute before all data ready
and the possibility of the next date be changed by the trigger when it's executing is low enough.
留言列表
