Materialization of WITH Clause

When materialization is enabled, Vertica evaluates each WITH clause once, stores results in a temporary table, and references this table as often as the query requires. Vertica drops the temporary table after primary query execution completes.

Note: If the primary query returns with an error, temporary tables might be dropped only after the client’s session ends.

Materialization can facilitate better performance when WITH clauses are complex—for example, when the WITH clauses contain JOIN and GROUP BY clauses, and are referenced multiple times in the primary query.

If materialization is enabled, WITH statements perform an auto-commit of the user transaction. This occurs even when using EXPLAIN with the WITH statement.

Enabling Materialization

By default, materialization is disabled. You can enable and disable materialization by setting the configuration parameter ENABLE_WITH_CLAUSE_MATERIALIZATION at the following scopes:

Session: Parameter setting remains in effect until you explicitly set or clear it, or the session ends.

ALTER SESSION SET PARAMETER EnableWithClauseMaterialization={ 0 | 1 };
ALTER SESSION CLEAR PARAMETER EnableWithClauseMaterialization;

Query: WITH clause includes ENABLE_WITH_CLAUSE_MATERIALIZATION hint. Materialization is automatically cleared when the query returns.
```
WITH /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/ with-query...
```

Example

The following example shows a WITH clause that is a good candidate for materialization. The query obtains data for the vendor who has the highest combined order cost for all orders:

-- Enable materialization

ALTER SESSION SET PARAMETER EnableWithClauseMaterialization=1;

-- Begin WITH clause,revenue

WITH 
   revenue AS (
      SELECT vendor_key, SUM(total_order_cost) AS total_revenue
      FROM store.store_orders_fact
      GROUP BY vendor_key ORDER BY 1)

-- End defining WITH clause statement
-- Begin main primary query
SELECT vendor_name, vendor_address, vendor_city, total_revenue
FROM vendor_dimension v, revenue r
WHERE v.vendor_key = r.vendor_key AND total_revenue = (SELECT MAX(total_revenue) FROM revenue)
ORDER BY vendor_name;

Vertica processes this query as follows:

The WITH clause revenue evaluates its SELECT statement from table store.store_orders_fact.
The results of the revenue clause are stored in a local temporary table.
Whenever the revenue clause statement is referenced, the results stored in the table are used.
The temporary table is dropped when query execution is complete.