Oracle WITH Clause: CBO and CTE Materialization | Article

Why the CBO Decides Whether Your CTE Materializes — and Why That Decision Breaks in Production

The WITH clause looks like a contract: define a named subquery once, reference it multiple times, let Oracle do the rest. The problem is that Oracle never signed that contract. The Cost-Based Optimizer treats your CTE as a suggestion, not an instruction. In staging with stale statistics and a few thousand rows, it materializes cleanly. In production, under real data volume and real access patterns, the CBO recalculates, decides inlining is cheaper, and executes your “reusable” subquery four times instead of once — silently, with no error, no warning, and a query that now takes twelve seconds instead of one.

This is not a bug. It is designed behavior that becomes a production failure when you don’t account for it.

Context: Where This Actually Happens

This problem surfaces most often in Oracle-based enterprise systems — finance, banking, ERP integrations — where:

Complex analytical queries are written by developers who understand the domain but not always the optimizer
Statistics are refreshed infrequently, or not at all on staging environments
The same schema holds five million rows in dev and five hundred million in production
Query plans are not reviewed as part of code review
Performance regressions appear at release time, not during testing

The WITH clause (formally called subquery factoring in Oracle, introduced in 9i Release 2) was adopted precisely because it looked cleaner than correlated subqueries or global temporary tables. In many systems, it became a standard pattern. The assumption baked into that pattern — that Oracle will materialize the subquery — is the assumption that breaks under production conditions.

Surface Symptoms

The failure mode is not dramatic. There is no ORA- error, no exception in the logs. What you get is:

A query that runs in 400ms on staging and times out in production
An execution plan that looks correct on paper — the WITH clause is there, the references are there
Developers who check the query, find nothing wrong, and escalate to DBAs who check the plan and find nothing obviously wrong either

The second wrong turn is the diagnosis: “it’s a data volume problem.” This is partially true and completely unhelpful. Volume is the condition, not the cause. The cause is that the CBO, given different statistics at different scale, made a different materialization decision — and nobody knew that was possible.

The third wrong turn is adding indexes. Indexes help if the problem is scan cost. If the problem is that a subquery is executing four times instead of once, an index makes each execution slightly faster and the query is still four times slower than it should be.

What Is Actually Happening Inside the Optimizer

The CBO’s Decision: Materialize vs. Inline

When Oracle encounters a WITH clause, the CBO evaluates two execution strategies:

Materialize: Execute the subquery once, write the result to a temporary segment (an internal session-level structure), and read from that segment on each subsequent reference. Cost is: one execution + N reads from temp.

Inline: Treat each reference to the CTE as if the subquery text were written inline at that point. Execute separately on each reference. Cost is: N executions with full access path each time.

The CBO chooses based on estimated cost. With small estimated result sets and cheap subquery execution, inlining can look cheaper because it avoids writing to temp. With large result sets or expensive subqueries, materialization wins.

The critical issue: cost is estimated from statistics, and statistics diverge between environments. In staging, the CBO sees small tables, low cardinality, short execution paths. Inlining looks fine. In production, the actual row counts are an order of magnitude higher, but if statistics haven’t been refreshed or if the optimizer’s cardinality estimates are off (which they often are with complex joins), the CBO still makes the staging-tuned decision.

The Execution Plan Tells You the Truth — If You Know What to Look For

In Oracle, EXPLAIN PLAN and DBMS_XPLAN.DISPLAY will show you whether a CTE was materialized or inlined:

Materialized CTE — you will see a TEMP TABLE TRANSFORMATION operation in the plan, with the CTE appearing as a step that writes to temp (LOAD AS SELECT), followed by reads from the temp table (TABLE ACCESS FULL on the internal temp object).

Inlined CTE — the CTE does not appear as a distinct step. Its logic is folded into the main query execution, appearing inline wherever the CTE is referenced. If it’s referenced three times, you’ll see three separate occurrences of that logic in the plan.

The plan output alone reveals the decision. Most developers don’t check this — they check whether the query returns the right result, not how many times the underlying subqueries executed.

-- Always run this after EXPLAIN PLAN to see the full decision
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

The ALL format flag is not optional here. Without it, temp table operations are often suppressed in the output.

Why Staging Doesn’t Catch This

Three structural reasons:

Statistics age: Production statistics reflect actual data distribution. Staging statistics are often either stale snapshots or not collected at all. The CBO model diverges from reality at the start.
Volume thresholds: The CBO’s crossover point — where materialization becomes cheaper than inlining — is volume-dependent. A subquery that returns 500 rows might be inlined cheaply. The same query returning 50,000 rows should be materialized, but if the CBO estimates 500 based on stale statistics, it still inlines.
Adaptive Query Optimization (introduced in Oracle 12c, persistent in 19c/21c): The optimizer can change a plan mid-execution based on actual row counts encountered. This means a query may execute differently on its first run versus subsequent runs in the same session, and differently across sessions depending on what feedback has been collected. In staging with light load, adaptive plans converge quickly and stabilize. In production with concurrent load and different data distributions, plan instability is common.

Taking Control: Hints, GTTs, and the Right Choice for Each Scenario

The `/+ materialize /` and `/+ inline /` Hints

Oracle provides two optimizer hints for CTE execution — both were historically undocumented but have been used reliably in production for over a decade:

WITH
  sales_summary AS (
    SELECT /*+ materialize */
      store_id,
      SUM(quantity) AS store_sales
    FROM sales
    GROUP BY store_id
  ),
  store_count AS (
    SELECT /*+ materialize */
      COUNT(*) AS nbr_stores
    FROM store
  )
SELECT
  s.store_name,
  ss.store_sales,
  (sc.nbr_stores)
FROM
  store s
  JOIN sales_summary ss ON s.store_id = ss.store_id
  CROSS JOIN store_count sc
WHERE
  ss.store_sales > (
    SELECT SUM(store_sales) / MAX(nbr_stores)
    FROM sales_summary, store_count
  );

The hint is placed inside the subquery that defines the CTE, immediately after the opening SELECT. It is honored by the CBO as a directive: execute this once, write to temp, read from temp.

The opposite hint, /*+ inline */, forces the CBO to never materialize — useful when you know the subquery is cheap and the temp write overhead isn’t worth it.

Oracle 19c and 21c behavior: In Oracle 19c, both hints are still the correct mechanism. The CBO in 19c is more aggressive with adaptive optimization and real-time statistics, which means the base materialization decision is more volatile without the hint — not less. In Oracle 21c, the hint behavior is unchanged, but the optimizer’s default heuristics have shifted slightly in favor of materialization for CTEs that are referenced more than once. Do not rely on the 21c heuristic shift as a substitute for explicit hinting in production-critical queries.

Global Temporary Tables: When CTEs Are Not Enough

For complex analytical workloads — particularly ETL steps, multi-stage reporting, or queries where the same intermediate result is needed across multiple separate SQL statements — GTTs remain the right answer despite looking less elegant:

-- Session-level GTT, data visible only to current session
CREATE GLOBAL TEMPORARY TABLE gtmp_sales_summary (
  store_id   NUMBER,
  store_sales NUMBER
) ON COMMIT PRESERVE ROWS;

-- Populate once
INSERT INTO gtmp_sales_summary
SELECT store_id, SUM(quantity)
FROM sales
GROUP BY store_id;

-- Use across multiple queries in the same transaction/session
SELECT s.store_name, g.store_sales
FROM store s JOIN gtmp_sales_summary g ON s.store_id = g.store_id
WHERE g.store_sales > :avg_threshold;

The trade-off is explicit: GTTs require DDL (a deployment artifact, not just a query), require population steps, and add session lifecycle management. What they give you in return is a guaranteed single execution with real optimizer statistics that can be gathered after population — something a CTE can never provide.

When to choose GTT over CTE with materialize hint:

The intermediate result is used across multiple SQL statements, not just multiple references in one query
You need to gather statistics on the intermediate result for downstream query optimization (DBMS_STATS.GATHER_TABLE_STATS)
The intermediate result is large (millions of rows) and you want explicit control over temp space allocation
You are working in a PL/SQL procedure where intermediate results need to survive across cursor opens

When the CTE with /*+ materialize */ is sufficient:

Single SQL statement, multiple references to the same aggregation
Result set is bounded and manageable (tens of thousands of rows)
You cannot introduce DDL (read-only DB access, runtime query generation, no deployment window)

Oracle 19c: Real-Time Statistics and Their Impact on CTE Decisions

Oracle 19c introduced real-time statistics — the optimizer can now collect statistics on-the-fly during DML operations, without waiting for a manual DBMS_STATS run. This sounds like it would help CTE materialization decisions, and it does — partially.

The practical impact: for tables that are actively modified, the optimizer’s cardinality estimates are more accurate in 19c than in earlier versions. This reduces (but does not eliminate) the staging/production plan divergence problem. However:

Real-time statistics are collected during DML, not during query execution. A table that is written to in batch jobs but read in reports may still have stale statistics at query time.
The optimizer’s real-time statistics are less detailed than full DBMS_STATS statistics — they track bulk counts but not histograms. For skewed data distributions (common in banking and financial data), histogram absence means cardinality estimates are still wrong in ways that affect materialization decisions.
In heavily concurrent 19c environments, the overhead of real-time statistics collection can itself become a performance issue, and DBAs sometimes disable it — reverting the system to pre-19c statistics behavior.

The bottom line: real-time statistics in 19c improve the average case. They do not make explicit materialization hints unnecessary for production-critical queries.

Oracle 21c: Automatic In-Memory and CTE Implications

Oracle 21c introduced Automatic In-Memory — when the In-Memory option is licensed, the database can automatically populate frequently-accessed objects into the In-Memory Column Store without manual configuration.

If materialized CTEs are frequently executed with the same structure (common in report generation or dashboard queries), Oracle 21c may automatically load the underlying base tables into the column store. This changes the cost model for CTE materialization: the base table scans become significantly cheaper (column store scan vs. row store scan), which can push the optimizer toward materialization even without the hint — because both the temp write and the base table scan are now cheaper.

The constraint: the In-Memory option is a licensed feature. Most enterprise Oracle deployments, particularly legacy systems running 19c/21c but not upgrading licensing, do not have it enabled. Do not design for Automatic In-Memory behavior unless you’ve verified the license and confirmed the feature is active.

In 21c without In-Memory, the CTE materialization story is essentially identical to 19c. The optimizer improvements are incremental, not transformative for this specific problem.

Production Stabilization Approach

If you are debugging a production performance regression tied to CTE execution, this is the sequence:

1. Get the actual plan, not the estimated plan.

SELECT sql_id FROM v$sql WHERE sql_text LIKE '%your_query_identifier%';

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', NULL, 'ALL +PEEKED_BINDS')
);

DISPLAY_CURSOR shows the plan Oracle actually used — including adaptive plan decisions applied at runtime. EXPLAIN PLAN shows the estimated plan, which may differ from what ran.

2. Confirm whether materialization happened.

Look for TEMP TABLE TRANSFORMATION in the plan. If it’s absent and your CTE is referenced multiple times, you’ve confirmed the problem.

3. Apply the hint to the specific CTEs that are referenced multiple times.

Don’t hint everything. The /*+ inline */ behavior is the correct default for CTEs referenced once — temp write overhead for a single-reference CTE is pure waste. Hint precisely.

4. Validate the plan after hinting.

Re-run DISPLAY_CURSOR after applying hints to confirm TEMP TABLE TRANSFORMATION now appears. Do not assume the hint was honored without verifying the plan.

5. If statistics are the underlying cause, address them directly.

-- For the affected tables
EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name', cascade => TRUE);

-- With histograms for skewed columns
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  'schema_name', 'table_name',
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE
);

Outdated statistics are rarely the only problem, but they are almost always a contributing factor. Fresh statistics let the CBO make better baseline decisions — reducing the cases where explicit hints become necessary.

Key Takeaways

The WITH clause is not a materialization directive. It is a named subquery that the CBO may or may not materialize based on its cost estimate. In production systems where statistics drift from staging, the CBO decision at scale is not the same as the CBO decision during development.

Plan divergence between environments is structural, not accidental. Different data volumes, different statistics age, and adaptive plan feedback produce different execution strategies for the same SQL. Building for the staging plan means building for the wrong plan.

/*+ materialize */ is the correct fix for production-critical CTEs referenced multiple times. It has been reliable since Oracle 9i and remains correct through 19c and 21c. The hint is not a workaround — it is the mechanism Oracle provides for this exact purpose.

GTTs are not legacy — they are the right tool for multi-statement intermediate results. When an intermediate result needs to survive across multiple SQL calls, be statistically characterized, or be explicitly managed for size, GTTs outperform CTEs in every dimension except aesthetics.

Oracle 19c real-time statistics improve the average case, not the worst case. Skewed data, batch-written tables, and high-concurrency environments still produce stale-statistics conditions in 19c. Do not remove explicit hints based on the version upgrade alone.

Oracle 21c Automatic In-Memory changes the cost model only if licensed and enabled. Assume it is off unless confirmed otherwise. The materialization hint strategy applies regardless.

The engineers who get burned by this are not the ones who don’t understand SQL — they’re the ones who understood it in a staging context and assumed that context transferred to production. It doesn’t. The optimizer recomputes every time, under conditions you don’t always control. The only stable outcome is the one you explicitly specify.