Home » Eclipse Projects » EclipseLink » Bug on using pagination on Oracle?(Bug on using pagination in conjunction with big resultlists on Oracle ordering after a columns with low variation?)
| Bug on using pagination on Oracle? [message #638506] |
Thu, 11 November 2010 14:26  |
 Marcel Pokrandt
Messages: 5
Registered: November 2010 |
Junior Member |
|
|
I´m not sure if you would see this as a bug of EclipseLink or of Oracle oi if I´m totally wrong - so before I filed it to bugzilla I post it here.
I´m using Eclipselink from the latest Glassfish 3.1 bundled package (2.1.1.v20100817-r8050) connected to an Oracle 11g (11.2.0.1.0) database and made a strange observation. I´m quering a table with round about 30.000 entries using the JPA2 criteria API (that doesn´t seem to matter).
That table (ZBM) is joined (inner by annotation JoinFetch, ManyToOne) to another table (MEL).
However this referenced table (MEL) has a column ("messageStateDescription" - mapped to "MELSTSBEZ") which can contain only ~3 different values and is not null. So you can say that at least more than 10.000 entries share the same value. And here it comes: If you query the table and order it by (only) this column (don´t think about the sense) and you use pagination (firstResult, maxResults) you get a strange behaviour.
Pagination works on the first ~~100 entries and on the last ~~100 entries. Between this area you get the *same* result on every query. I know, this sounds weird but I broke it down to the SQL-query.
Let´s say, I query with firstResult=24520 and maxResults=20 and later with firstResults=24540 - EclipseLink generates the following SQL-queries (removed the first_rows-hint and added a line break, but this doesn´t matter)
SELECT * FROM (SELECT a.*, ROWNUM rnum FROM (SELECT t1.ID AS a1, t1.EGSJAH AS a2, t1.BESNUM AS a3, t1.EGSNUM AS a4, t1.MELJAH AS a5, t1.BESMEL AS a6, t1.MELNUM AS a7, t1.EVENTDATA_ID AS a8, t1.EVENT_ID AS a9, t1.MESSAGE_ID AS a10, t1.LAST_MODIFIED_BY_GROUP AS a11, t1.CREATED_BY_GROUP AS a12, t1.CREATED_AT_DATE AS a13, t1.AENDAT AS a14, t1.CREATED_BY_USER AS a15, t1.AENUSR AS a16 FROM MEL t0, ZBM t1 WHERE (t0.ID = t1.MESSAGE_ID) ORDER BY t0.MELSTSBEZ ASC) a
WHERE ROWNUM <= 24520) WHERE rnum > 24500
;
SELECT * FROM (SELECT a.*, ROWNUM rnum FROM (SELECT t1.ID AS a1, t1.EGSJAH AS a2, t1.BESNUM AS a3, t1.EGSNUM AS a4, t1.MELJAH AS a5, t1.BESMEL AS a6, t1.MELNUM AS a7, t1.EVENTDATA_ID AS a8, t1.EVENT_ID AS a9, t1.MESSAGE_ID AS a10, t1.LAST_MODIFIED_BY_GROUP AS a11, t1.CREATED_BY_GROUP AS a12, t1.CREATED_AT_DATE AS a13, t1.AENDAT AS a14, t1.CREATED_BY_USER AS a15, t1.AENUSR AS a16 FROM MEL t0, ZBM t1 WHERE (t0.ID = t1.MESSAGE_ID) ORDER BY t0.MELSTSBEZ ASC) a
WHERE ROWNUM <= 24540) WHERE rnum > 24520
;
The queries look good, but when i manually execute them (Quest TOAD) I really get the same result on both queries, only the column RNUM is different. It is irrelevant what number I place in "WHERE rnum > 24520" as long as it´s bigger than ~~100.
When i manually modify the query to (just changed the outer WHERE-clause and deleted the inner)
SELECT * FROM (SELECT a.*, ROWNUM rnum FROM (SELECT t1.ID AS a1, t1.EGSJAH AS a2, t1.BESNUM AS a3, t1.EGSNUM AS a4, t1.MELJAH AS a5, t1.BESMEL AS a6, t1.MELNUM AS a7, t1.EVENTDATA_ID AS a8, t1.EVENT_ID AS a9, t1.MESSAGE_ID AS a10, t1.LAST_MODIFIED_BY_GROUP AS a11, t1.CREATED_BY_GROUP AS a12, t1.CREATED_AT_DATE AS a13, t1.AENDAT AS a14, t1.CREATED_BY_USER AS a15, t1.AENUSR AS a16 FROM MEL t0, ZBM t1 WHERE (t0.ID = t1.MESSAGE_ID) ORDER BY t0.MELSTSBEZ ASC) a
) WHERE rnum > 24520 and rnum <= 24540
;
The result is as expected. This happens *only* if you order your table by a column with a very low variation. I think that Oracle internally "optimizes" this query in a wrong way because the ordering is not deterministic (because of the low variation).
For a workaround I now always add as last ordering-criteria the order by "ID" which works for me.
But perhaps it would be possible to change EclipseLinks way of generating the pagination query to the statement i proposed...
|
|
|
| Re: Bug on using pagination on Oracle? [message #638580 is a reply to message #638506] |
Thu, 11 November 2010 16:51  |
|
When using pagination in general, you should have a consistent order-by field, ideally the Id of the object.
If your order is not consistent, then you can get back different results each re-execution of the query.
Your change to the SQL that you indicate works looks promising though. Please log a bug for this, as it at least needs further investigation.
James : Wiki : Book : Blog : Twitter
|
|
| |
Goto Forum:
Current Time: Tue Apr 16 19:15:09 GMT 2024
Powered by FUDForum. Page generated in 0.23678 seconds |
] 
Search
Help
Register
Login
Home
