Why does a query against the base type of a mapped hierarchy emit one statement in some mappings and several in others?
answer
- the statement follows the layout
- one table, joins, or union arms
- adding a subtype touches existing queries
- paging happens after the arms combine
- narrow to a concrete type to prune
basics
~20 sBecause the statement follows where the subtypes' columns live: one shared table means one SELECT, per-subtype tables mean an outer join per subtype, and per-class tables mean a union arm each, so cost grows per subtype.
solid answer
~50 sA base-type query asks one question, but the layer has to reach every subtype's columns, and those may live in one shared table, in child tables keyed to a base table, or in a standalone table per concrete class. Those layouts produce three statement shapes: a single SELECT (optionally narrowed by a marker predicate), a single SELECT carrying one outer join per subtype, or a union with one arm per concrete table. The first grows in width, the second in joins, the third in arms — so with the second and third, adding a subtype silently makes every existing base-type query more expensive, and paging over a union only becomes meaningful after the arms are combined. The practical lever is to query a **concrete** type when the caller only needs one: that prunes the markers, the joins or the tables the layer has to touch.
go deeper
Remember that a query for a base type may read several tables, and that the number of tables follows how the hierarchy was mapped rather than how the query was written.
Be able to name the three statement shapes, say which layout produces each, and describe how each one grows as subtypes are added.
Show that you check the emitted statement for the busiest base-type read and treat 'one more subtype' as a change to every existing query on that hierarchy.
Weigh the growth curve against the expected subtype count and the read patterns before the layout is chosen, since re-laying a live hierarchy is a migration under load.
## Why one query has several possible shapes A query written against the base type asks a single question — "give me all payments" — but the statement the layer emits depends entirely on **where the subtypes' columns live**. The class hierarchy is one thing; the tables under it are another, and the mapper has to bridge them for every read. Three statement shapes cover what layers do: 1. **One statement over one table.** Every subtype's rows and columns share a table, and a type marker says which class each row is. The base-type query is a plain SELECT, optionally narrowed by a marker predicate when only some subtypes were asked for. 2. **One statement with an outer join per subtype.** Shared columns live in a base table, each subtype's own columns in its own table keyed by the same value. A base-type query outer-joins out to all of them, so every returned row can be hydrated as its concrete class in one pass. 3. **A union across concrete tables.** Each concrete class owns a standalone table carrying all of its columns, base ones included. A base-type query has to read every one of them: either a single UNION statement, or one query per table stitched together in memory. ## How each shape grows - Shape 1 grows in **rows and width**. Adding a subtype adds columns to a table everyone reads; statement count stays at one. - Shape 2 grows in **joins**. With n subtypes a base-type read carries n outer joins, and adding a subtype silently widens every existing base-type query in the system. - Shape 3 grows in **arms**. With n concrete classes the read scans n tables and reconciles columns only some arms have. Sorting and paging happen after the arms are combined, so the layer usually reads far more rows than it returns. | | one shared table | base plus per-subtype tables | per-concrete-class tables | |---|---|---|---| | Base-type read | one SELECT | one SELECT with n outer joins | UNION of n SELECTs, or n reads | | Effect of a new subtype | wider table | one more join everywhere | one more arm everywhere | | Read of one concrete type | marker predicate | base joined to one table | one table | | Paging a mixed list | ordinary paging | ordinary paging | applied after combining arms | ## Narrowing is the lever you actually have The layer cannot change the layout at query time, but it can prune: - Ask for a **concrete type** rather than the base type: that becomes a marker predicate, a single join, or a single table — and it removes the per-subtype growth from that call path entirely. - Ask for **a subset of subtypes** where the query language allows a type restriction; the layer prunes markers or joins to match. - Reserve base-type queries for the screens that genuinely mix classes. A list that only ever renders one subtype but queries the base type pays the whole hierarchy's cost on every page load. ## Writes fan out for the same reason The same layout question decides how many statements a base-type write becomes. Deleting by base type is one statement when everything shares a table, but becomes several when the row has parts in a base table and a child table (children first, then the base), and one per concrete table when each class stands alone. That is the same growth curve as reads, so a hierarchy that is expensive to query is usually expensive to purge as well. ## What to look for in review - Count the joins or union arms in the statement the layer actually emits for your busiest base-type read, and multiply mentally by the next two subtypes someone will add. - Watch for paging over a union: the branches must be combined before the ordering is meaningful. - Watch for base-type queries behind single-type screens; retyping the query is usually the cheapest performance fix available in a mapped hierarchy. - Treat "one more subtype" as a change to existing queries, not just an additive change — that is the part reviewers routinely miss. - Check what the layer does with a count or an aggregate over the base type: under a union it may still have to combine the arms before it can count them, so a cheap-looking summary can read the whole hierarchy. The habit worth building is to stop reasoning about a base-type query as "one query" at all. It is a request that the layer translates against a layout you chose earlier, and the translation is where the cost lives.
- Why is paging a base-type query awkward when each concrete class has its own table?The rows come from separate branches, so no single branch can be ordered or limited correctly on its own. The layer must combine the arms first and order the combined set, which means reading more rows than the page returns — the cost of a page does not shrink the way it does on a single table.
- How does the same layout question affect a delete written against the base type?It fans out the same way. One shared table is one statement; a base table with child tables means children first, then the base; standalone tables per class means one statement per table. A hierarchy that is expensive to read is usually expensive to purge.
- A list screen only ever shows one subtype but queries the base type. What is the fix?Retype the query to the concrete class. The layer then applies a marker predicate, joins one table, or reads one table, and the query stops paying for subtypes the screen never renders. It is usually the cheapest performance fix available in a mapped hierarchy.
saying these in an interview costs you the question
- Assumes a base-type query is always a single SELECT
- Thinks adding a subtype only affects code touching that subtype
- Believes a union arm can be paged independently
- Says the layer picks the cheapest layout at query time
- Filters subtypes in memory instead of restricting the query's type