
240 8.3 GROUP BY Clause Extensions and Materialized Views
CREATE MATERIALIZED VIEW mv_rollup_cost
PARALLEL NOLOGGING TABLESPACE mvdata
COMPRESS USING NO INDEX NEVER REFRESH
ENABLE QUERY REWRITE
AS SELECT l.city, i.industry, p.category
,COUNT(pu.transaction_amount),COUNT(*)
,SUM(pu.transaction_amount)
FROM purchase pu, location l, time t, product p, industry i
WHERE pu.location_id = l.location_id
AND pu.time_id = t.time_id
AND pu.product_id = p.product_id
AND pu.industry_id = i.industry_id
GROUP BY ROLLUP(l.city, i.industry, p.category);
ANALYZE TABLE mv_rollup_cost COMPUTE STATISTICS;
Unless the query executed against the join creating the
MV_ROLLUP_COST ...