Unlike COUNT(expression), COUNT(DISTINCT expression) is not a
distributable function. That is, the expression can not be
computed by looking at only the changed rows in the table change
logs.
The following view can not be FAST refreshed with Flexviews
today:
SELECT a a_alias,
b b_alias,
c c_alias,
COUNT(DISTINCT d) d_alias
FROM T1
GROUP BY a, b, c;
However, a dependent child materialization could be
created to support the value for COUNT(DISTINCT d):
-- child materialization, dependent subview
--There will be one row for each DISTINCT value of d
SELECT a a_alias,
b b_alias,
c c_alias,
count(*) d_alias_cnt
FROM T1
GROUP BY a, b, c, d;
The original view could then be rewritten as:
SELECT a a_alias,
b b_alias,
c c_alias, …[Read more]