What's an efficient way to find rows where the timestamp and identity are not in sequence?

Question

Background:

I have a MS SQL application that reads data from our Oracle billing database once an hour, looking for new payments. It does this by storing a timestamp based on the CRT_DTTM of the most recent timestamp found each time it runs.

e.g.

SELECT *
FROM V_TRANS
WHERE TRANS_CLS = 'P'
AND CRT_DTTM > TO_DATE('2010-01-25 12:59:44', 'YYYY-MM-DD HH24-MI-SS')

After this result is returned, the MAX(CRT_DTTM) is stored as the starting timestamp for the next hourly run.

What appears to be happening is that sometimes there's a transaction running on the Oracle side that is inserting data into the table at the same time I'm running my query. The rows are seemingly not being inserted in order of the timestamp they got. This means that my MAX(CRT_DTTM) is greater than some of the rows that get inserted after my query completes. I've missed payment information, and my systems are out of balance.

I believe that I can work around this by simply modifying my SQL statement above to add:

...
AND CRT_DTTM < SYSDATE - INTERVAL '10' MINUTE

The Question:

What I want to know is if there's a way to examine the rows already inserted into the table to find those pockets where the identity is out of order with the timestamp:

I want to find the pockets of data with this situation to know if 10 minutes is long enough to hold off on looking at the timestamp.

SELECT *
FROM V_TRANS t1
JOIN V_TRANS t2
    ON t1.trans_id < t2.trans_id
        AND t2.crt_dttm < t1.crt_dttm
WHERE t1.TRANS_CLS = 'P'
    AND t1.CRT_DTTM > TO_DATE('2010-01-25 12:59:44', 'YYYY-MM-DD HH24-MI-SS')
    -- Only look at an interval of one day
    AND t1.CRT_DTTM < TO_DATE('2010-01-25 12:59:44', 'YYYY-MM-DD HH24-MI-SS') + 1

Or perhaps I'm overlooking some basic transaction isolation level setting? I'm running this via a linked server with OPENQUERY()

Answer 1

select t1.id, t2.id, t1.timestamp - t2.timestamp
from table t1
join table t2 on t1.id = t2.id - 1
where t1.timestamp < t2.timestamp

EDIT:

I wrote this answer, then realized it was more or less the same query you already have. So I must not understand your question.

My query will "...examine the rows already inserted into the table to find those pockets where the identity is out of order with the timestamp" which I thought was your question.

BTW, if you're just looking for what's the biggest jump backwards of a timestamp, you can use:

select max(t2.timestamp - t1.timestamp)
from table t1
join table t2 on t1.id = t2.id - 1;

If I'm misunderstanding your question, please let me know what I'm missing.

Answer 2

SELECT
  *
FROM (
        SELECT
          t1.*,
          RANK() OVER (ORDER BY trans_id) AS trans_id_rank,
          -- Ordering by trans_id after CRT_DTTM to cater for transactions within the same second
          RANK() OVER (ORDER BY CRT_DTTM, trans_id) AS CRT_DTTM_rank
        FROM V_TRANS t1) t
WHERE trans_id_rank <> CRT_DTTM_rank

Answer 3

"The rows are seemingly not being inserted in order of the timestamp they got. " More likely they are not being COMMITED in timestamp order, so they have been inserted but not committed when you run your query.

See if the Oracle DBA will give you grants on V$TRANSACTION. You can then see the start time of any open transactions, how long they are open, etc. Also if the Oracle database is RAC, it is very likely that the sequence numbers (the closest Oracle has to identity columns) are not in order either. Each node in the RAC has its own cache to reduce contention.

In 11gR2, they've introduced a WAITING_ON_PENDING_DML to cater for this situation.