Databases
StackOverflow2013
Postsdbo
POWhat's an efficient way to find rows where the timestamp and identity are not in sequence?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat's an efficient way to find rows where the timestamp and identity are not in sequence?
    text
    copied!<p><strong>Background:</strong></p> <p>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 <code>CRT_DTTM</code> of the most recent timestamp found each time it runs. </p> <p><em>e.g.</em></p> <pre><code>SELECT * FROM V_TRANS WHERE TRANS_CLS = 'P' AND CRT_DTTM &gt; TO_DATE('2010-01-25 12:59:44', 'YYYY-MM-DD HH24-MI-SS') </code></pre> <p>After this result is returned, the <code>MAX(CRT_DTTM)</code> is stored as the starting timestamp for the next hourly run.</p> <p>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 <code>MAX(CRT_DTTM)</code> 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.</p> <p>I believe that I can work around this by simply modifying my SQL statement above to add:</p> <pre><code>... AND CRT_DTTM &lt; SYSDATE - INTERVAL '10' MINUTE </code></pre> <p><strong>The Question:</strong></p> <p>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:</p> <p><a href="http://i46.tinypic.com/r70sk0.png" rel="nofollow noreferrer">database sequence vs timestamp http://i46.tinypic.com/r70sk0.png</a></p> <p>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.</p> <pre><code>SELECT * FROM V_TRANS t1 JOIN V_TRANS t2 ON t1.trans_id &lt; t2.trans_id AND t2.crt_dttm &lt; t1.crt_dttm WHERE t1.TRANS_CLS = 'P' AND t1.CRT_DTTM &gt; 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 &lt; TO_DATE('2010-01-25 12:59:44', 'YYYY-MM-DD HH24-MI-SS') + 1 </code></pre> <p>Or perhaps I'm overlooking some basic transaction isolation level setting? I'm running this via a linked server with <code>OPENQUERY()</code></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload