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 2018 orders shipped between December 01-07:

-- Enable materialization
ALTER SESSION SET PARAMETER WithClauseMaterialization=1;

-- Begin WITH
WITH /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */
  store_orders_fact_new AS(
    SELECT * FROM store.store_orders_fact WHERE date_shipped between '2018-12-01' and '2018-12-07')
-- End WITH
-- Begin 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 DESC;

 store_key | product_key | product_version | total_price
-----------+-------------+-----------------+-------------
       135 |       14815 |               2 |       30000
       154 |       19202 |               1 |       29106
       232 |        1855 |               2 |       29008
        20 |        4804 |               3 |       28500
        11 |       16741 |               3 |       28200
       169 |       12374 |               1 |       28120
        50 |        9395 |               5 |       27538
        34 |        8888 |               4 |       27100
       142 |       10331 |               2 |       27027
       106 |       18932 |               1 |       26864
       190 |        8229 |               1 |       26460
       198 |        8545 |               3 |       26287
        38 |       17426 |               1 |       26280
         5 |       10095 |               1 |       26224
        41 |        2342 |               1 |       25920
        87 |        5574 |               1 |       25443
       219 |       15271 |               1 |       25146
        60 |       14223 |               1 |       25026
        97 |       16324 |               2 |       24864
       234 |       17681 |               1 |       24795
       195 |       16532 |               1 |       24794
        83 |        9597 |               2 |       24661
       149 |        7164 |               5 |       24518
       142 |       11022 |               4 |       24400
       202 |       12712 |               1 |       24380
        13 |       18154 |               1 |       24273
         7 |        3793 |               3 |       24250
...

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.