Question
Hibernate pagination failing in production with Oracle 11g due to dialect mismatch
Neel Dey
0 reputation · 21 Jun 2022, 09:11 UTC
109.5K views
Loading…
0 reputation · 21 Jun 2022, 09:11 UTC
Development environments commonly use H2 or MySQL for local testing, while production may run on Oracle 11g which lacks native LIMIT/OFFSET support. This dialect mismatch causes Hibernate to generate incompatible SQL syntax when applying pagination through setFirstResult() and setMaxResults().
The issue surfaces when queries that work locally suddenly fail in production with syntax errors or unexpected result sets. Oracle 11g requires complex nested rownum subqueries that differ significantly from the simpler LIMIT clauses used in MySQL or the OFFSET/FETCH syntax introduced in Oracle 12c+.
Additionally, when pagination is combined with collection joins, Hibernate may fall back to in-memory pagination, triggering the HHH000104 warning and potentially loading entire tables into JVM memory.
35775 reputation · 21 Jun 2022, 13:53 UTC
To ensure consistent pagination on Oracle 11g, you must explicitly configure the org.hibernate.dialect.Oracle10gDialect (or the equivalent for your Hibernate version) in your production profile. Oracle 11g does not support LIMIT or OFFSET; this dialect forces Hibernate to generate the necessary nested ROWNUM subqueries required for setFirstResult() and setMaxResults() to function correctly.
The HHH000104 warning occurs because Hibernate cannot safely apply database-level pagination when a JOIN FETCH is used on a collection (@OneToMany or @ManyToMany). Doing so would result in duplicate parent rows, causing the pagination count to be incorrect. Consequently, Hibernate fetches all records into the JVM memory and paginates them there, which often leads to OutOfMemoryError in production.
To avoid this, use one of the following strategies:
hibernate
reach
oracleJOIN FETCH). Then, execute a second query to fetch the parents and their collections using those specific IDs.JOIN FETCH from the paginated query and set hibernate.default_batch_fetch_size (e.g., to 50). This allows Hibernate to load collections for the retrieved page in a few optimized batches rather than one-by-one (N+1) or all-at-once (in-memory).WHERE id > :lastSeenId clause. This is compatible with JOIN FETCH and is significantly more performant for deep paging.hibernate.dialect=org.hibernate.dialect.H2Dialect or MySQL8Dialecthibernate.dialect=org.hibernate.dialect.Oracle10gDialectJOIN FETCH combined with setFirstResult or Pageable.hibernate.show_sql=true
hibernate.format_sql=trueSELECT ... ROWNUM queries rather than LIMIT clauses.This guidance assumes the use of Hibernate 5.x or 6.x. In Hibernate 6.2+, some dialect classes have been deprecated or consolidated; if Oracle10gDialect is not found, check the org.hibernate.dialect package for the current Oracle 11g compatible class. We assume the production environment is strictly Oracle 11g, as Oracle 12c+ introduces OFFSET/FETCH which would allow for a different dialect (Oracle12cDialect).
Missing Diagnostic: Please confirm your exact Hibernate version, as the specific dialect class name may vary slightly between major releases.
Use comments to ask for clarification. Post a solution as an answer.
35,775 reputation · 21 Jun 2022, 09:53 UTC
One clarification worth adding: Oracle12cDialect is not a drop-in fix for Oracle 11g. It emits ANSI OFFSET/FETCH syntax, which 11g does not support, so the failure simply shifts from a missing LIMIT to an unsupported pagination clause. For 11g, the version-aware choice is Oracle10gDialect or a custom subclass, which Hibernate maps to nested ROWNUM subqueries.
To confirm the dialect is active, enable SQL logging and inspect a paginated query. You should see the original select wrapped in an inner query with a ROWNUM filter and an outer query applying the offset boundary. If LIMIT or OFFSET still appears, the dialect property is not reaching that SessionFactory.
Separately, HHH000104 is a fetch-graph problem, not a dialect problem. It appears when a JOIN FETCH on a collection forces Hibernate to page in memory. Changing dialects will not silence it; the query shape must change.