Query Rewrite and COUNT DISTINCT
I have a materialized view as follows.
CREATE MATERIALIZED VIEW MV_SUM
BUILD IMMEDIATE
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT a.Col
, b.Col1
, COUNT (*) count_all
, COUNT (DISTINCT b.Col2 || b.Col3) Count_unique
FROM Table1 a, Table2 b
WHERE a.Col = b.col
GROUP BY a.Col
, b.col1
Query rewrite works when I have the following SQL
SELECT a.Col
, b.Col1
, COUNT (*)
FROM Table1 a, Table2 b
WHERE a.Col = b.col
GROUP BY a.Col
, b.col1
It does not work when I use COUNT (DISTINCT b.Col2 || b.Col3), such as
SELECT a.Col
, b.Col1
, COUNT (DISTINCT b.Col2 || b.Col3)
FROM Table1 a, Table2 b
WHERE a.Col = b.col
GROUP BY a.Col
, b.col1
Does Query Rewrite work on COUNT Concatenated columns ?