How to use live aggregated projection ?

macservice123

I have situation where I need to use aggregated query within a sub query.

select
x.col1,
x.col2,
x.col3,
b.col5,
b.col6
(
select
a.col1,
sum(a.col2) as col2,
sum(a.col3) as col3,
sum(nvl(col3,0)) as col4
from
table1 a
where
col4 between to_date('09/01/2016', 'MM/dd/yyyy') AND to_date('08/31/2018', 'MM/dd/yyyy')
group by
col1
) x
join table2 b on x.col1 = b.col1

Here in this query, inner sub-query is taking to long time. So I try to use live aggregated projection. After creating a projection like:

CREATE PROJECTION table1_lap AS
SELECT
col1
SUM(col2) col2,
SUM(col3) col3
FROM table1
GROUP BY col1;

But execution time did not reduce. Please tell me the solution.
Thank you....
Cristiano

Akash_Manakshe

You have to use col4 column as well in projection. Here, your live aggregation is not working because, your live aggregation is aggregating whole table of data, while selecting data, you are giving a date condition (col4), so vertica is recalculation agg. To match with your filter condition.
In this case, use Trunc(col4) in Projection (as in group by clause as well &it will give best performance if used in segmented by clause as well, as colu4 is in Filter condition).