What is a materialized CTE (`WITH … AS MATERIALIZED`) vs an inlined CTE and what problem does materialization solve?

Question

Advanced SQL Developer — Medium

What is a materialized CTE (`WITH … AS MATERIALIZED`) vs an inlined CTE and what problem does materialization solve?

Accepted Answer

Materialized CTEs store the CTE results in a temporary structure, preventing re-evaluation and optimizer transformations. This solves cases where re-evaluation is costly or optimizer transformations lead to suboptimal plans.