Live Aggregate Projection in Query Plan

norbert_krupa

Just doing some initial tests setting up a LAP, it doesn't seem as though the actual LAP appears in the query plan. Is this expected behavior as the anchor table is used, or is the setup below incorrect?

CREATE PROJECTION online_sales.online_sales_fact_lap_anchor (      transaction_type,      warehouse_key,      gross_profit_dollar_amount  )  AS    SELECT transaction_type,            warehouse_key,            gross_profit_dollar_amount    FROM online_sales.online_sales_fact  SEGMENTED BY HASH(online_sales_fact.transaction_type,                     online_sales_fact.warehouse_key) ALL NODES KSAFE 1;     SELECT MARK_DESIGN_KSAFE(1);     SELECT REFRESH('online_sales.online_sales_fact');    CREATE PROJECTION online_sales.online_sales_fact_lap AS   SELECT transaction_type,          warehouse_key,          SUM(gross_profit_dollar_amount) AS sum_gross_profit_dollar_amount   FROM   online_sales.online_sales_fact   GROUP  BY transaction_type,            warehouse_key;     SELECT REFRESH('online_sales.online_sales_fact');    SELECT ANALYZE_STATISTICS('online_sales.online_sales_fact');

The query being used:

SELECT transaction_type,          warehouse_key,          SUM(gross_profit_dollar_amount) AS sum_gross_profit_dollar_amount   FROM   online_sales.online_sales_fact   GROUP  BY transaction_type,            warehouse_key;

Requesting a query plan produces:

Access Path:   +-GROUPBY PIPELINED [Cost: 11K, Rows: 100] (PATH ID: 1)   |  Aggregates: sum(online_sales_fact.gross_profit_dollar_amount)   |  Group By: online_sales_fact.transaction_type, online_sales_fact.warehouse_key   |  Execute on: All Nodes   | +---> STORAGE ACCESS for online_sales_fact [Cost: 9K, Rows: 5M] (PATH ID: 2)   | |      Projection: online_sales.online_sales_fact_lap_anchor_b0   | |      Materialize: online_sales_fact.transaction_type, online_sales_fact.warehouse_key, online_sales_fact.gross_profit_dollar_amount   | |      Execute on: All Nodes

Sharon_Cutter

Hi Norbert,

In 7.1 you need to reference the Live Aggregate Projection projection explicitly.  In the docs:

"After the data is loaded, you can query data directly from the live aggregate projection."

So users need to now that the aggregate projection exists as opposed to picking it up transparently.  

And this is also an awfully big restriction:

"After you create a live aggregate projection for a table, you can no longer perform delete, update, or merge operations in the anchor table."

Yikes.

  --Sharon

norbert_krupa

Completely missed that. The query would then look like:

SELECT transaction_type,         warehouse_key,         sum_gross_profit_dollar_amount FROM   online_sales.online_sales_fact_lap;

The plan then shows the LAP projection used:

Access Path:  +-STORAGE ACCESS for online_sales_fact_lap [Cost: 13, Rows: 200 (NO STATISTICS)] (PATH ID: 1)  |  Projection: online_sales.online_sales_fact_lap  |  Materialize: online_sales_fact_lap.transaction_type, online_sales_fact_lap.warehouse_key, online_sales_fact_lap.sum_gross_profit_dollar_amount  |  Execute on: All Nodes

Thanks, Sharon!

[Deleted User]

You can't perform delete, update or merge operations on a table with aggregate projections but you can drop partitions.