Issue Details (XML | Word | Printable)

Key: CORE-2721
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Joerg von Frantzius
Reporter: Joerg von Frantzius
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
JPOX Core (ARCHIVED)

Support effective range queries on Oracle

Created: 28/Feb/06 08:00 PM   Updated: 16/Jul/10 04:13 PM   Resolved: 03/Mar/06 12:04 PM
Component/s: Datastore Support
Affects Version/s: 1.1.0-rc-1
Fix Version/s: 1.1.0

Datastore: Oracle


 Description  « Hide
Currently range queries are very slow (and memory-intensive) on Oracle, because no matter what the range is, everything before its start is fetched, even though it is not returned to the user.

As described e.g. in http://www.oracle.com/technology/books/pdfs/powell_ch.pdf
Oracle needs a special syntax using a subquery: Oracle evaluates ROWNUM before any ordering is applied, so the whole original query including the ordering must be wrapped as a subquery within a SELECT applying ROWNUM filtering. As an example see chapter 6.3.3 in the said PDF:
SELECT * FROM (SELECT * FROM customer ORDER BY name) WHERE
ROWNUM < 25;



Sort Order: Ascending order - Click to sort in descending order
Joerg von Frantzius added a comment - 01/Mar/06 01:42 PM

Joerg von Frantzius added a comment - 03/Mar/06 12:03 PM
Fixed in org.jpox.store.query.QueryStatement rev. 1.48

Yang ZHONG added a comment - 14/Jul/10 11:25 PM

Yang ZHONG added a comment - 16/Jul/10 04:13 PM