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.
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:
- Expands the
WITHclause reference to
store_orders_fact_newwithin the primary query.
- After expanding the
WITHclause, evaluates the primary query.