创建包含扩展 GROUP BY 表达式的物化视图：
在登录到 SH 模式的 SQL*Plus 会话中，执行以下 SQL 语句：
@c:\wkdir\create_gby_mv.sql
DROP MATERIALIZED VIEW sales_cube_mv;
CREATE MATERIALIZED VIEW sales_cube_mv
ENABLE QUERY REWRITE
AS
SELECT calendar_year year, calendar_quarter_desc quarter,
calendar_month_desc month, cust_state_province state,
cust_city city,
GROUPING_ID (calendar_year,calendar_quarter_desc,
calendar_month_desc,
cust_state_province,cust_city) gid,
GROUPING(calendar_year) grp_y,
GROUPING(calendar_quarter_desc) grp_q,
GROUPING(calendar_month_desc) grp_m,
GROUPING(cust_state_province) grp_s,
GROUPING(cust_city) grp_c,
SUM(amount_sold) sum_sales
FROM sales s, times t, customers c
WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id
GROUP BY GROUPING SETS ((calendar_year, cust_city),
(calendar_year, cust_city,
cust_state_province),
(calendar_year, calendar_quarter_desc,
calendar_month_desc,cust_city));
exec dbms_stats.gather_table_stats('SH','sales_cube_mv');
DROP MATERIALIZED VIEW sales_gby_mv;
CREATE MATERIALIZED VIEW sales_gby_mv
ENABLE QUERY REWRITE
AS
SELECT calendar_year year, cust_state_province state,
SUM(amount_sold) sum_sales
FROM sales s, times t, customers c
WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id
GROUP BY (calendar_year, cust_state_province);
exec dbms_stats.gather_table_stats('SH','sales_gby_mv');
以下查询包含与已创建的物化视图中完全相同的 GROUPING SETS。如您所见，查询就像是根据物化视图重写的。