Oracle的“排序依据"子句以错误的顺序返回结果或缺少结果 [英] Oracle "order by" clause returning results in wrong order or with results missing

问题描述

在我的应用程序日志中，我可以看到查询结果正在被迭代(使用JDBC ResultSet，没什么花哨的)，并且某些实际存在于表中的结果要么丢失，要么在结果集中出现的时间太晚(我不确定是哪一种，因为如果发生这种情况，我经常会在碰到最后时遇到主键冲突).

In my application logs, I can see that the query results are being iterated over (using a JDBC ResultSet, nothing fancy), and certain results which are actually in the table, are either missing, or appear too late in the resultset (I'm not sure which, because I often get a primary key violation before I hit the end if this happens).

我的查询是:

select t.* from myschema.vm t order by id;

但是当我在SQL Developer中运行完全相同的查询时(在数据库未更改的情况下)，我看不到此问题发生，并且它只在我的应用程序中发生大约一半的时间(我怀疑有时由于所有插入和更新而导致表更改，这种情况有时不会发生.

However I can't see this issue occurring when I run the exact same query in SQL Developer (while the database is not changing), and it only occurs about half the time in my application (I suspect it sometimes doesn't happen due to the table changing due to all the inserts and updates).

要提及的其他要点:

1. 在遍历ResultSet时，我正在使用另一个数据库连接进行更新，插入和可能的删除.但是，这没关系，对吧?
2. 我正在使用BoneCP进行连接池.
3. 数据库编码为AL32UTF8.

为什么会这样?这让我发疯了！

Why is this happening? It's driving me crazy!

推荐答案

此问题是由于用户错误引起的.我有另一个在另一台机器上运行的应用程序的副本，我已经忘记了它，并且同时更改了数据库.

This problem was due to user error. I had another copy of the application running on a different machine, which I had forgotten about, and that was changing the database at the same time.

这篇关于Oracle的“排序依据"子句以错误的顺序返回结果或缺少结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！