9:21 a.m.
Pagination with Oracle ROWNUM is sub-optimal
--------------------------------------------
Key: HHH-3325
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325
Project: Hibernate3
Issue Type: Bug
Affects Versions: 3.2.5
Reporter: e. wernli
Pagination with Oracle ROWNUM is sub-optimal
The feature works but result in sub-optimal SQL. The generated SQL is the following:
select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_,
this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_,
this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ )
where rownum_ <= ? and rownum_ > ?
But this SQL is faster:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_
WHERE ROWNUM <= ?)
WHERE rownum_ > ?
The second solution allows Oracle to use an optimization that can dramatically reduce the
time of the query, especially one of the first page is retrieved.
See this link for an explanation of this optimization:
http://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-o...
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:24 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3325) Pagination with Oracle ROWNUM is sub-optimal
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325?page=c...
]
Sébastien Lesaint commented on HHH-3325:
----------------------------------------
I stepped upon this issue in one of my projects.
I was startled to see that display of a paged table took about 10 times more time on the
first page than on any other page.
Activating hibernate's SQL logs, turns out that Oracle takes much more time to query
something like "select * from ( blabla ) where rownum < 15" than query
"select * from (select row_.*, rownum rownum_ from ( blabla ) row_ where rownum <=
15) where rownum_ > 0".
Hibernate execute the first query whenever one sets the firstResult property of the Query
class to 0 or less. This seems to be an obvious way to optimise the generated SQL string
but it turns out to be quite a bad performance choice when executing against Oracle.
An obvious fix would be to NOT simplify the SQL when Oracle dialect is selected. I have no
idea how easy it is to actually implement.
In the meantime, as a workaround, we included the paging parameter in our native query and
it works like a charm.
Pagination with Oracle ROWNUM is sub-optimal
--------------------------------------------
Key: HHH-3325
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325
Project: Hibernate Core
Issue Type: Bug
Affects Versions: 3.2.5
Reporter: e. wernli
Pagination with Oracle ROWNUM is sub-optimal
The feature works but result in sub-optimal SQL. The generated SQL is the following:
select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_,
this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_,
this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ )
where rownum_ <= ? and rownum_ > ?
But this SQL is faster:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_
WHERE ROWNUM <= ?)
WHERE rownum_ > ?
The second solution allows Oracle to use an optimization that can dramatically reduce the
time of the query, especially one of the first page is retrieved.
See this link for an explanation of this optimization:
http://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-o...
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
1:39 a.m.
New subject: [Hibernate-JIRA] Updated: (HHH-3325) Pagination with Oracle ROWNUM is sub-optimal
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325?page=c...
]
Seth Wilcox updated HHH-3325:
-----------------------------
Attachment: OracleLimit.patch
Patch of file Oracle9iDialect.java against Hibernate 3.2.7
Changed Limit generation to always have a lower limit, even if it is 0 to allow oracle to
perform optimizations if link above.
Added in the first N rows hint as suggested here
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
Moved the manipulation of the SQL for isForUpdate until after the StringBuffer to
necessitate less GC.
Pagination with Oracle ROWNUM is sub-optimal
--------------------------------------------
Key: HHH-3325
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325
Project: Hibernate Core
Issue Type: Bug
Affects Versions: 3.2.5
Reporter: e. wernli
Attachments: OracleLimit.patch
Pagination with Oracle ROWNUM is sub-optimal
The feature works but result in sub-optimal SQL. The generated SQL is the following:
select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_,
this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_,
this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ )
where rownum_ <= ? and rownum_ > ?
But this SQL is faster:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_
WHERE ROWNUM <= ?)
WHERE rownum_ > ?
The second solution allows Oracle to use an optimization that can dramatically reduce the
time of the query, especially one of the first page is retrieved.
See this link for an explanation of this optimization:
http://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-o...
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:11 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3325) Pagination with Oracle ROWNUM is sub-optimal
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325?page=c...
]
Christian Daszenies commented on HHH-3325:
------------------------------------------
The proposed patch uses the query hint {{select /*+ FIRST_ROWS\(n\) */ ...}}. This would
not work as expected as _n_ is a variable ("where n is any positive integer")
as stated in oracle docs:
http://download.oracle.com/docs/cd/A97630_01/server.920/a96533/hintsref.htm
The challenge is to determine an appropriate value in the dialect implementation.
Pagination with Oracle ROWNUM is sub-optimal
--------------------------------------------
Key: HHH-3325
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325
Project: Hibernate Core
Issue Type: Bug
Affects Versions: 3.2.5
Reporter: e. wernli
Attachments: OracleLimit.patch
Pagination with Oracle ROWNUM is sub-optimal
The feature works but result in sub-optimal SQL. The generated SQL is the following:
select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_,
this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_,
this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ )
where rownum_ <= ? and rownum_ > ?
But this SQL is faster:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_
WHERE ROWNUM <= ?)
WHERE rownum_ > ?
The second solution allows Oracle to use an optimization that can dramatically reduce the
time of the query, especially one of the first page is retrieved.
See this link for an explanation of this optimization:
http://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-o...
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4:30 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3325) Pagination with Oracle ROWNUM is sub-optimal
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325?page=c...
]
Richard Birkby commented on HHH-3325:
-------------------------------------
Hibernate doesn't need the ROWNUM column returned. So why not get rid of it?
SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_ FROM customer this_ WHERE ROWID in (
SELECT rowid_
FROM (SELECT ROWNUM rownum_, ROWID rowid_ FROM customer
WHERE ROWNUM <= ?)
WHERE rownum_ > ?)
ORDER BY this_.ID ASC
Perhaps it's not possible to do that for all queries?
Pagination with Oracle ROWNUM is sub-optimal
--------------------------------------------
Key: HHH-3325
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325
Project: Hibernate Core
Issue Type: Bug
Affects Versions: 3.2.5
Reporter: e. wernli
Attachments: OracleLimit.patch
Pagination with Oracle ROWNUM is sub-optimal
The feature works but result in sub-optimal SQL. The generated SQL is the following:
select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_,
this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_,
this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ )
where rownum_ <= ? and rownum_ > ?
But this SQL is faster:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_
WHERE ROWNUM <= ?)
WHERE rownum_ > ?
The second solution allows Oracle to use an optimization that can dramatically reduce the
time of the query, especially one of the first page is retrieved.
See this link for an explanation of this optimization:
http://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-o...
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
12:26 a.m.
New subject: [Hibernate-JIRA] Updated: (HHH-3325) Pagination with Oracle ROWNUM is sub-optimal
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325?page=c...
]
Seth Wilcox updated HHH-3325:
-----------------------------
Attachment: OracleLimit.patch
Thanks Christian, I was able to overload getLimitString(String sql, int offset, int limit)
to get the limit to replace n with.
Richard, that solution would cause the query to have to be run twice, not sure how good
oracle would be at optimizing that away
Here is the patch for Oracle9iDialect.java correcting the FIRST_ROWS(n) problem
Pagination with Oracle ROWNUM is sub-optimal
--------------------------------------------
Key: HHH-3325
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325
Project: Hibernate Core
Issue Type: Bug
Affects Versions: 3.2.5
Reporter: e. wernli
Attachments: OracleLimit.patch, OracleLimit.patch
Pagination with Oracle ROWNUM is sub-optimal
The feature works but result in sub-optimal SQL. The generated SQL is the following:
select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_,
this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_,
this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ )
where rownum_ <= ? and rownum_ > ?
But this SQL is faster:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_
WHERE ROWNUM <= ?)
WHERE rownum_ > ?
The second solution allows Oracle to use an optimization that can dramatically reduce the
time of the query, especially one of the first page is retrieved.
See this link for an explanation of this optimization:
http://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-o...
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11:13 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3325) Pagination with Oracle ROWNUM is sub-optimal
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325?page=c...
]
Can Çermikli commented on HHH-3325:
-----------------------------------
I don't know why this problem is still open, but I think this problem is already
resolved in Hibernate Dialect classes which are Oracle9iDialect, Oracle9Dialect and
Oracle10gDialect. It generates SQL's like following:
select *
from ( select row_.*, rownum rownum_
from ( OUR_QUERY ) row_
where rownum <= MAX_ROWS )
where rownum_ > MIN_ROWS
But the way Hibernate's SQL generation for pagination in OracleDialect.java and
Oracle8iDialect.java is the problematic one. Actually, it is not problematic, I think it
is done intentionally in OracleDialect.java and Oracle8iDialect.java like this, because in
Oracle 8i and below order by clause in the inner SQL stamement is not allowed. So, after
8i and Oracle's order by support, Hibernate's Dialect classes seem to change to
support the new pagination.
So, if you use OracleDialect or Oracle8iDialect, Hibernate will generate sub-optimal
native SQL's for pagination (of course if you use Oracle 8i and below, you need to use
those ones), and
if you use Oracle9iDialect, Oracle9Dialect or Oracle10gDialect Hibernate will generate
optimal native SQL's.
I hope it will be useful for the people who are searching for an answer for the problem.
Pagination with Oracle ROWNUM is sub-optimal
--------------------------------------------
Key: HHH-3325
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325
Project: Hibernate Core
Issue Type: Bug
Affects Versions: 3.2.5
Reporter: e. wernli
Attachments: OracleLimit.patch, OracleLimit.patch
Pagination with Oracle ROWNUM is sub-optimal
The feature works but result in sub-optimal SQL. The generated SQL is the following:
select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_,
this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_,
this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ )
where rownum_ <= ? and rownum_ > ?
But this SQL is faster:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_
WHERE ROWNUM <= ?)
WHERE rownum_ > ?
The second solution allows Oracle to use an optimization that can dramatically reduce the
time of the query, especially one of the first page is retrieved.
See this link for an explanation of this optimization:
http://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-o...
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:39 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3325) Pagination with Oracle ROWNUM is sub-optimal
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325?page=c...
]
Seth Wilcox commented on HHH-3325:
----------------------------------
In oracle, to get maximum performance, you can explicitly tell the DB that you are doing a
pagination query(by using the select /*+ FIRST_ROWS(n) */ hint). When you do this, as
soon as oracle has the number of rows you said you needed, the DB immediately stops and
returns. Also, it can change the way the Cost based optimizer works. In my experience,
it makes a noticeable and measurable impact on performance. The hint is what is still
missing.
Pagination with Oracle ROWNUM is sub-optimal
--------------------------------------------
Key: HHH-3325
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325
Project: Hibernate Core
Issue Type: Bug
Affects Versions: 3.2.5
Reporter: e. wernli
Attachments: OracleLimit.patch, OracleLimit.patch
Pagination with Oracle ROWNUM is sub-optimal
The feature works but result in sub-optimal SQL. The generated SQL is the following:
select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_,
this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_,
this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ )
where rownum_ <= ? and rownum_ > ?
But this SQL is faster:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_
WHERE ROWNUM <= ?)
WHERE rownum_ > ?
The second solution allows Oracle to use an optimization that can dramatically reduce the
time of the query, especially one of the first page is retrieved.
See this link for an explanation of this optimization:
http://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-o...
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:58 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3325) Pagination with Oracle ROWNUM is sub-optimal
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325?page=c...
]
Nicolas Gavard commented on HHH-3325:
-------------------------------------
This article http://www.dba-oracle.com/t_sql_tuning_rownum_equals_one.htm talks about
using rank() instead of rownum for better performance.
Pagination with Oracle ROWNUM is sub-optimal
--------------------------------------------
Key: HHH-3325
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325
Project: Hibernate Core
Issue Type: Bug
Affects Versions: 3.2.5
Reporter: e. wernli
Attachments: OracleLimit.patch, OracleLimit.patch
Pagination with Oracle ROWNUM is sub-optimal
The feature works but result in sub-optimal SQL. The generated SQL is the following:
select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_,
this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_,
this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ )
where rownum_ <= ? and rownum_ > ?
But this SQL is faster:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_
WHERE ROWNUM <= ?)
WHERE rownum_ > ?
The second solution allows Oracle to use an optimization that can dramatically reduce the
time of the query, especially one of the first page is retrieved.
See this link for an explanation of this optimization:
http://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-o...
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4777
days inactive
5869
days old
hibernate-issues@lists.jboss.org
8 comments
7 participants
participants (7)
-
Can Çermikli (JIRA) -
Christian Daszenies (JIRA)
-
e. wernli (JIRA)
-
Nicolas Gavard (JIRA)
-
Richard Birkby (JIRA)
-
Seth Wilcox (JIRA)
-
Sébastien Lesaint (JIRA)