On 03/04/2013 04:11, Bryan Pendleton wrote:
it's worth reporting the problem, together with whatever information you have.
OK, I'll prepare something and submit it.
2) I agree with Knut Anders's hunch that this seems like a "sort avoidance" bug.
Yes, that sounds right to me too.
3) I see that your query plan output includes: Scroll Insensitive ResultSet: Number of opens = 1 Rows seen = 30 Number of reads from hash table = 30 Number of writes to hash table = 30 This is interesting, and makes me think that maybe you have some special connection settings which are causing the query plan to take all of your results (which might have been correctly ordered), and throw them into an in-memory hash table, which then causes the rows to become randomly ordered.
The rows aren't *randomly* ordered; they are ordered by the order in which they were originally inserted into the ITEMS table, which is linked to by ITEM_USAGE. If you do "SELECT * FROM items" without sorting, the item codes come out in this order. So it looks like it's just not bothering to sort, and the rows are then coming out in the natural order.
Do you by any chance specify ResultSet.TYPE_SCROLL_INSENSITIVE, when you create the statement object in your JDBC code?
Yes.
If so, does the behavior change if you change this setting?
No -- tried FORWARD_ONLY and SCROLL_SENSITIVE and it makes no difference.
Hope this helps,
It certainly does, even if we haven't got to the bottom of it yet -- many thanks! -- John English
