Inline Expansion of WITH Clause

By default, Vertica uses inline expansion to evaluate WITH clauses. Vertica evaluates each WITH clause every time it is referenced by the primary query. Inline expansion often works best if the query does not reference the same WITH clause multiple times, or if some local optimizations are possible after inline expansion.

Example

The following example shows a WITH clause that is a good candidate for inline expansion. The WITH clause is used in a query that obtains order information for all orders shipped in 2007, between December 15-31.

-- Enable materialization

ALTER SESSION SET PARAMETER EnableWithClauseMaterialization=1;

-- Begin WITH clause
				
WITH 
  store_orders_fact_new AS(
    SELECT * FROM store.store_orders_fact WHERE date_shipped between '2007-12-15' and '2007-12-31')

-- End WITH clause
-- Begin main primary query

SELECT store_key, product_key, product_version, SUM(quantity_ordered*unit_price) AS total_price
FROM store_orders_fact_new
GROUP BY store_key, product_key, product_version
ORDER BY total_price;

Vertica processes the query as follows:

1. Expands the WITH clause reference to store_orders_fact_new within the primary query.
2. After expanding the WITH clause, evaluates the primary query.