You need a correctly sized page of parent entities with their child collections already initialised, without Hibernate paginating in memory. Describe the two-query approach and what each query looks like.

## The idea Split the two jobs that conflict. *Choosing which parents belong on the page* is a row-limited operation the database does well. *Loading each chosen parent's children* is a bounded lookup that needs no limit at all. Do them in separate statements and neither one has to compromise. ## Query 1 — select ids only ``` select o.id from Order o where o.status = :st order by o.createdAt desc, o.id desc ``` with `setFirstResult`/`setMaxResults`. Because nothing is fetch-joined, Hibernate emits a real `limit ? offset ?`. It returns scalars, not entities, so nothing enters the persistence context and the memory cost is a list of longs. If the ordering column is indexed, the database can often satisfy this from the index alone. ## Query 2 — fetch the graph for those ids ``` select o from Order o left join fetch o.items where o.id in :ids order by o.createdAt desc, o.id desc ``` No `setMaxResults` here. The `IN` clause is the bound: at most *page size* parents, each with its children. The join still multiplies rows, but the multiplication is over 20 parents, not the whole table, so it is fine. Three things people get wrong: 1. **Ordering.** `where id in (...)` returns rows in whatever order the plan produces. If you skip the `ORDER BY` in query 2, your page comes back scrambled. Repeat the exact same ordering. (If the sort key is expensive or not available on the entity, sort the results in Java by the id list's index instead.) 2. **Consistency between the two queries.** They run as two statements. Under concurrent writes the set of ids can no longer match the predicate by the time query 2 runs — a deleted row simply disappears from the page. Running both inside one transaction (and, if the guarantee matters, at an isolation level that gives a stable snapshot) bounds the anomaly; usually a slightly short page is acceptable for a list screen. 3. **Id-list size.** Every database has a ceiling on bind parameters or `IN` elements (Oracle's 1000-element `IN` limit is the famous one; PostgreSQL's practical limit is the 65535 parameter cap). For normal page sizes this never bites, but if you reuse the pattern for a bulk load, chunk the ids. Hibernate 6's `hibernate.query.in_clause_parameter_padding` pads the list to powers of two so the statement cache sees far fewer distinct SQL strings — worth enabling when you use this pattern a lot. ## Variants of the same trick - **Entities instead of ids.** Query 1 can select the entities themselves (paged, no fetch join); query 2 then runs `select o from Order o left join fetch o.items where o in :orders`. Because the parents are already managed, query 2's job is purely to initialise the collections, and Hibernate wires them onto the instances already in the persistence context — you can even ignore query 2's return value. This costs more than selecting ids but keeps the code simple. - **`@BatchSize(size = 25)` on the collection.** Leave it lazy. Page the parents in one SQL-limited query; when the first `items` collection is touched, Hibernate initialises up to 25 collections in one statement with an `IN` list of owner ids. One extra query per batch, no query rewriting, and it works for every code path automatically. - **`@Fetch(FetchMode.SUBSELECT)`.** Hibernate re-runs the original query as a subselect to load all collections at once. Careful: with a paged original query, the subselect does **not** carry the limit, so it can load collections for far more parents than the page. Prefer `@BatchSize` when paging. - **DTO projection.** If the screen needs three columns and a child count, `select new ...(o.id, o.total, count(i))` with a `group by` avoids the whole problem. ## Two collections at once Query 2 can fetch-join only one `List`-typed collection; two throw `MultipleBagFetchException` because the double join makes the row multiplication ambiguous for bag semantics. Options: change one to a `Set`, split into a third query on the same id list, or leave the second collection to `@BatchSize`. ## What it costs Two round trips instead of one, and the ordering expressed twice. In exchange, both statements are bounded by the page size, the plan for query 1 is index-friendly, and heap usage no longer depends on table size. For any list screen over a table that grows, that trade is worth making.