Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
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 Go to next message
Marcel Pokrandt is currently offline 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 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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
Re: Bug on using pagination on Oracle? [message #638599 is a reply to message #638506] Thu, 11 November 2010 17:56 Go to previous message
Marcel Pokrandt is currently offline Marcel Pokrandt
Messages: 5
Registered: November 2010
Junior Member
so I did
https://bugs.eclipse.org/bugs/show_bug.cgi?id=330027

Let´s see what they´ll say
Previous Topic:Handling files using lob in entity
Next Topic:[JPA] Initializing a persistence unit with information retrieved at runtime
Goto Forum:
  


Current Time: Tue Oct 21 19:58:20 GMT 2014

Powered by FUDForum. Page generated in 0.02802 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software