Hibernate pagination failing in production with Oracle 11g due to dialect mismatch

Problem

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.

Key constraints

• Local development uses H2/MySQL with native LIMIT support
• Production runs Oracle 11g requiring rownum-based pagination
• Query may include JOIN FETCH operations on collections
• Default fetch size may cause memory issues with large datasets

Questions

1. How can the Hibernate dialect be configured to ensure consistent pagination SQL generation across different database versions?
2. What strategies exist to prevent in-memory pagination when using collection joins with paginated queries?
3. Which Oracle 11g-compatible pagination approach should be used to avoid the HHH000104 warning?