
326
11.2
How the Optimizer Rewrites Queries
DROP MATERIALIZED VIEW mv_sales;
CREATE MATERIALIZED VIEW mv_sales
NOPARALLEL NOLOGGING TABLESPACE mvdata
COMPRESS USING NO INDEX NEVER REFRESH
ENABLE QUERY REWRITE
AS SELECT year#, quarter#, month#
,COUNT(transaction_amount)
, SUM(transaction_amount)
FROM sale s, location l, time t, product p, industry i
WHERE s.location_id = l.location_id
AND s.time_id = t.time_id
AND s.product_id = p.product_id
AND s.industry_id = i.industry_id
GROUP BY year#, quarter#, month#;
ANALYZE TABLE mv_sales COMPUTE STATISTICS;
Figure 11.3 shows an example query rewrite dimensional rollup opera-
tion where the query rolls up on the ...