If I have a daily batch job that need to pick up the records that have been modified during the past 24 hours and do some processing, there is the issue of timing.
If I execute:
insert into TARGET_TABLE select * From SOURCE_TABLE where MODIFIED_DATE >= to_date(to_char(sysdate - 1, 'YYYYMMDD') || '070000', 'YYYYMMDDHHMISS')
and MODIFIED_DATE < to_date(to_char(sysdate , 'YYYYMMDD') || '070000', 'YYYYMMDDHHMISS')
If I have a MODIFIED_DATE column that gets updated by a trigger whenever there is a change, how can I make sure I pick up all the records? Because of read consistency and a transaction might take a few minutes to commit, a record can get the MODIFIED_DATE updated in the range above after the statement above gets executed, so it won't be picked up. How do I solve the issue of read consistency?