查找时间戳和标识不按顺序排列的行的有效方法是什么？

Question

背景：

我有一个 MS SQL 应用程序，它每小时从我们的 Oracle 计费数据库中读取一次数据，寻找新的付款。它通过存储基于每次运行时找到的最新时间戳的 CRT_DTTM 的时间戳来实现此目的。

例如

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')

返回此结果后，MAX(CRT_DTTM) 将存储为下一个每小时运行的开始时间戳。

似乎正在发生的情况是，有时 Oracle 端正在运行一个事务，该事务在我运行查询的同时将数据插入表中。这些行似乎没有按照它们获得的时间戳顺序插入。这意味着我的 MAX(CRT_DTTM) 大于查询完成后插入的一些行。我错过了付款信息，并且我的系统失去了平衡。

我相信我可以通过简单地修改上面的 SQL 语句添加以下内容来解决这个问题：

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

问题：

我想知道是否有一种方法可以检查已插入表中的行以查找这些行身份与时间戳无序的口袋：

我想找到这种情况下的数据包，以了解 10 分钟是否足以推迟查看时间戳。

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

或者也许我忽略了一些基本的事务隔离级别设置？我通过链接服务器使用 OPENQUERY() 运行它

Answer 1

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 2

“这些行似乎没有按照它们获得的时间戳顺序插入。”
更有可能的是，它们没有按时间戳顺序提交，因此它们已被插入，但在您运行查询时尚未提交。

查看 Oracle DBA 是否会为您提供 V$TRANSACTION 的资助。然后，您可以查看任何未结交易的开始时间、未结交易的时长等。
此外，如果 Oracle 数据库是 RAC，则序列号（最接近 Oracle 标识列）也很可能不按顺序排列。 RAC 中的每个节点都有自己的缓存以减少争用。

在 11gR2 中，他们引入了 WAITING_ON_PENDING_DML< /a> 来适应这种情况。

Answer 3

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

编辑：

我写了这个答案，然后意识到这或多或少与您已有的查询相同。所以我一定不明白你的问题。

我的查询将“...检查已插入表中的行，以找到身份与时间戳不一致的那些口袋”，我认为这是您的问题。

顺便说一句，如果您只是寻找时间戳向后最大的跳跃是什么，您可以使用：

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

如果我误解了您的问题，请让我知道我错过了什么。