skip to content

What does it mean to fill every owner's collection by re-running the original query's predicate as a subquery?

level: middleimportance: must knowfreq 46%

answer

  1. reuse the query, not the key list
  2. one extra statement per collection
  3. the owner predicate is evaluated twice
  4. no varying parameter list
  5. row limits make the re-run over-select

basics

~20 s

The layer issues one extra statement per deferred collection, reading children whose owner key comes from a subquery repeating the driving query's predicate. Statement count stops depending on how many owners returned, but that predicate is evaluated twice.

solid answer

~50 s

Instead of listing the owner keys it happens to hold, the layer re-uses the query that produced those owners. Filling a collection becomes `select ... from child where owner_id in (select id from owner where <the original predicate>)`, issued once for that collection no matter whether the driving query returned ten owners or ten thousand. The trade against identifier batches is clear: statement count is fixed rather than proportional to owners over batch size, and there is no varying parameter list, but the owner predicate runs a second time, so an expensive one is paid twice. It also only applies to collections reached through that query — owners obtained some other way have no predicate to re-run — and layers differ in whether the re-run carries the driving query's row limit, which is what makes it risky on a paged screen.

code

sql · 14 lines
sql
-- driving query
select * from owner
where region = ? and active = true;

-- fill a collection with an identifier batch
select * from child
where owner_id in (?, ?, ?, ?);

-- fill the same collection by re-running the predicate
select * from child
where owner_id in (
  select id from owner
  where region = ? and active = true
);

go deeper

for a junior

Recall the shape: the children are fetched with a predicate that repeats the original owner query, so one extra statement covers every owner in the result instead of one per owner or per key batch.

for a middle

Explain both sides — statement count fixed and shape stable, against a predicate evaluated twice and unavailable for owners obtained any other way. Be able to write the statement out.

for a senior

Lead with the paging trap and the cold-cache cost of the second evaluation, and say how you would confirm either from statement logs rather than by reasoning alone.

for a principal

Argue about where the technique is a default and where it is banned — large unpaged reads against selective predicates versus paged screens — and about who owns that decision as the query shapes change.

## The idea Identifier batching answers the question "which owners do I have?" by listing keys. **Subquery fetching answers it by remembering how those owners were found.** The driving query had a predicate. If the layer keeps that predicate, then filling a deferred collection no longer needs a key list at all: it can ask for every child whose owner satisfies the same predicate. ```sql -- the driving query select * from owner where region = ? and active = true; -- filling one deferred collection by re-running the predicate select * from child where owner_id in (select id from owner where region = ? and active = true); ``` One extra statement fills that collection for **every** owner the driving query returned. Ten owners or ten thousand, the count of statements is the same. ## What it buys - **A fixed number of statements.** One per deferred collection being filled, rather than owners divided by batch size. On a result of a thousand owners, that is one statement instead of dozens. - **One statement shape.** The text does not change with the number of owners, because there is no varying parameter list — which is the opposite of the key-list case, where the length of the list is part of the statement. - **No dependence on what is in memory.** The set of owners is defined by the predicate, not by which stand-ins happen to be pending, so the fill is complete for the whole result rather than for whichever ones were registered. ## What it costs - **The predicate runs twice.** Once for the driving query and once inside the subquery. If it is an expensive predicate — a wide scan, several joins, an unindexed condition — the second evaluation is paid in full. On a cold cache, where the owner rows and index pages are not resident, that second pass can cost as much as the first. - **It is only available where the predicate is.** A collection on owners you looked up individually, or received from somewhere else, has no query to re-run; those fall back to key lists. - **The two evaluations can disagree.** Between the driving query and the fill, another transaction may have inserted or deleted owners. Depending on the isolation in force, the subquery can see a slightly different owner set than the one you are holding — extra children arriving for owners you do not have, which the layer simply has nowhere to file. - **Row limits are the trap.** If the driving query fetched only the first page of owners, the predicate on its own describes far more owners than that page. Layers differ in whether the re-run reproduces the limit; where it does not, a page of twenty owners can drag back children for the entire matching population. ## Set against identifier batches | | Identifier batches | Subquery fetch | |---|---|---| | Statements to fill one collection | Owners divided by batch size | One | | Statement shape | Varies with the key-list length | Stable | | Owner predicate evaluated | Once | Twice | | Works for owners obtained outside the query | Yes | No | | Behaviour under a row-limited driving query | Safe — only the keys you hold | Risky — the predicate may re-select far more | | Fills links for owners not in memory | No | Possibly, and those rows are wasted | Neither is a general winner. A large unpaged result whose predicate is cheap and selective is where subquery fetching is at its best. A paged screen, or an expensive predicate, or owners assembled by hand, is where a key list is the safer instrument. ## Nesting The reasoning composes downwards. Filling a collection of children by the re-run predicate, and then a collection hanging off those children, is two extra statements, each with the owner predicate nested one level deeper. The count stays proportional to the number of collection levels, not to the number of rows — which is the property that makes the technique attractive on large results and the one to check has not been quietly given away by a limit somewhere in the chain. ## How to answer it Describe the mechanism in one sentence — the collection is filled by a statement whose owner-key predicate re-runs the original query — then give the trade honestly: statements fixed instead of proportional, one stable shape instead of many, paid for by evaluating the owner predicate twice and by only being applicable where that predicate still describes the owners you actually hold.

  • Why is a row-limited driving query the classic failure case for this technique?
    The limit belongs to the driving query, not to the predicate. Re-running the predicate alone describes every matching owner, so a page of twenty can fetch children for the whole population. Where the layer does not reproduce the limit inside the subquery, the fix is to fill by key list on paged paths.
  • How many statements does it take to fill two sibling collections on the same owners?
    Two — one per collection, each with the owner predicate re-run. That independence is the point: the collections are fetched separately, so they never multiply against each other the way two collections pulled through one joined statement would.
  • What happens if new owners match the predicate between the driving query and the fill?
    The subquery may select those owners too and return children the layer holds no owner for; those rows have nowhere to go and are discarded. The magnitude depends on the isolation in force — a snapshot that covers both statements avoids it, a read-committed pair of statements does not.

saying these in an interview costs you the question

  • Thinks the number of statements grows with the number of owners
  • Believes the owner rows are fetched again into memory by the subquery
  • Assumes the driving query's row limit is carried into the re-run predicate
  • Says it works for owners loaded individually outside that query
  • Claims it avoids all extra statements by folding into the first one
  • Ignores that an expensive owner predicate is now evaluated twice