在登录到 SH 模式的 SQL*Plus 会话中，运行 create_mv1.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\create_mv1.sql
DROP MATERIALIZED VIEW cust_sales_mv ;
CREATE MATERIALIZED VIEW cust_sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT c.cust_id,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_id;
使用物化视图和查询重写功能
这个语句失败并引发下面的错误：
ORA-23413: table "SH"."CUSTOMERS" does not have
a materialized view log.
@c:\wkdir\explain_mv1.sql
truncate table mv_capabilities_table;
exec dbms_mview.explain_mview( -
'SELECT c.cust_id, SUM(amount_sold) AS dollar_sales -
FROM sales s, customers c -
WHERE s.cust_id= c.cust_id -
GROUP BY c.cust_id');
set serveroutput on
begin
for crec in ( select capability_name, possible,
related_text, msgtxt
from mv_capabilities_table order by 1) loop
dbms_output.put_line(crec.capability_name ||': '||crec.possible);
dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
end loop;
end;
/
@c:\wkdir\create_mv_logs1.sql
DROP MATERIALIZED VIEW LOG ON sales;
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
(prod_id, cust_id, time_id, channel_id, promo_id,
quantity_sold, amount_sold)
INCLUDING NEW VALUES ;
DROP MATERIALIZED VIEW LOG ON customers;
CREATE MATERIALIZED VIEW LOG ON customers
WITH ROWID, SEQUENCE
(cust_id,cust_first_name,cust_last_name,cust_gender,cust_year_of_birth
,cust_marital_status,cust_street_address,cust_postal_code,cust_city
,cust_state_province,country_id,cust_main_phone_number,cust_income_level
,cust_credit_limit,cust_email)
INCLUDING NEW VALUES;
使用物化视图和查询重写功能
使用物化视图和查询重写功能
这一个在后面会使用。
DROP MATERIALIZED VIEW LOG ON products;
CREATE MATERIALIZED VIEW LOG ON products
WITH ROWID, SEQUENCE
(prod_id,prod_name,prod_desc,prod_subcategory,prod_subcategory_desc
,prod_category,prod_category_desc,prod_weight_class,prod_unit_of_measure
,prod_pack_size,supplier_id,prod_status,prod_list_price,prod_min_price)
INCLUDING NEW VALUES;
4.
再次检查潜在物化视图的功能。
@c:\wkdir\explain_mv1a.sql
TRUNCATE TABLE mv_capabilities_table;
EXEC dbms_mview.explain_mview( -
'SELECT c.cust_id, SUM(amount_sold) AS dollar_sales, -
COUNT(amount_sold) AS cnt_dollars, COUNT(*) -
FROM sales s, customers c -
WHERE s.cust_id= c.cust_id -
GROUP BY c.cust_id');
set serveroutput on
BEGIN
for crec in (select capability_name, possible,
related_text, msgtxt
from mv_capabilities_table order by 1) loop
dbms_output.put_line(crec.capability_name ||': '||crec.possible);
dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
end loop;
END;
/
使用物化视图和查询重写功能
此潜在物化视图的快速刷新功能已经按期望进行了更改。
5.
现在创建物化视图。
@c:\wkdir\create_mv1b.sql
DROP MATERIALIZED VIEW cust_sales_mv ;
CREATE MATERIALIZED VIEW cust_sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT c.cust_id,
SUM(amount_sold) AS dollar_sales,
COUNT(amount_sold) AS cnt_dollars,
COUNT(*) AS cnt
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_id;
使用物化视图和查询重写功能
6.
explain_mview 过程也使用现有的物化视图。
@c:\wkdir\explain_mv1b.sql
TRUNCATE TABLE mv_capabilities_table;
EXEC dbms_mview.explain_mview('cust_sales_mv');
set serveroutput on
begin
for crec in (select capability_name, possible,
related_text, msgtxt
from mv_capabilities_table order by 1) loop
dbms_output.put_line(crec.capability_name ||': '||crec.possible);
dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
end loop;
end;
/
当文本比较测试失败后，Oracle 优化程序将基于联接、选择、分组、聚合以及抓取的列数据执行一系列通用检查。这是通过将查询的不同子句（如 SELECT、FROM、WHERE、HAVING 或 GROUP BY）与物化视图的不同子句分别进行比较而完成的。
使用物化视图和查询重写功能
使用部分文本匹配重写
使用物化视图和查询重写功能
在预先建立的表上创建物化视图
使用物化视图和查询重写功能
使用简单的联回 (Join Back) 重写
使用物化视图和查询重写功能
分析重写过程
使用物化视图和查询重写功能
使用联回和卷积重写
使用物化视图和查询重写功能
使用复杂的联回和卷积重写
使用物化视图和查询重写功能
在数据的子集上创建物化视图
使用物化视图和查询重写功能
使用多物化视图重写
使用物化视图和查询重写功能
估计物化图的大小
使用物化视图和查询重写功能
分析重写过程
使用物化视图和查询重写功能
用联回和聚合卷积重写
返回主题列表
使用部分文本匹配重写
最简单的重写机制是文本匹配重写。在全文本匹配中，查询的整个文本会与物化视图定义的整个文本（即整个 SELECT 表达式）相比较 — 在文本比较过程中，忽略空白。在全文本匹配失败后，优化程序会尝试进行部分文本匹配。在这种方法中，以查询的 FROM 子句开始的文本会与以物化视图定义的 FROM 子句开始的文本进行比较。
1.
在登录到 SH 模式的 SQL*Plus 会话中，运行 explain_rewrite1.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\explain_rewrite1.sql
Rem REWRITE
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT c.cust_id,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_id;
set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);
使用物化视图和查询重写功能
该计划显示，使用部分文本匹配重写机制，通过 cust_sales_mv materialized 视图重写了查询。以 FROM 子句开始，SQL 语句和物化视图是相同的。
@c:\wkdir\do_rewrite1.sql
set timing on
SELECT c.cust_id,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_id;
@c:\wkdir\explain_norewrite.sql
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT /*+ norewrite */
c.cust_id,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_id;
set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);
在登录到 SH 模式的 SQL*Plus 会话中，运行 create_mv2.sql，或将以 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\create_mv2.sql
DROP MATERIALIZED VIEW cust_sales_aggr ;
CREATE MATERIALIZED VIEW cust_sales_aggr
ON PREBUILT TABLE
REFRESH FORCE
ENABLE QUERY REWRITE
AS
SELECT c.cust_id,
c.cust_last_name,
c.cust_first_name,
SUM(amount_sold) AS dollar_sales,
COUNT(amount_sold) AS cnt_dollars,
COUNT(*) AS cnt
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_id,
c.cust_last_name,
c.cust_first_name;
在登录到 SH 模式的 SQL*Plus 会话中，运行 explain_rewrite2.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\explain_rewrite2.sql
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT c.cust_last_name,
c.cust_credit_limit,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_last_name,
c.cust_credit_limit
ORDER BY 1;
set linesize 140
SELECT * FROM TABLE(dbms_xplan.display);
@c:\wkdir\do_rewrite2.sql
SELECT COUNT(*)
FROM (SELECT c.cust_last_name,
c.cust_credit_limit,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_last_name,
c.cust_credit_limit
ORDER BY 1;
使用物化视图和查询重写功能
3.
用于非重写查询的计划可以通过以下语句显示：
@c:\wkdir\explain_norewrite2.sql
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT /*+ NOREWRITE */ c.cust_last_name,
c.cust_credit_limit,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_last_name,
c.cust_credit_limit
ORDER BY 1;
set linesize 120
select * from table(dbms_xplan.display);
在登录到 SH 模式的 SQL*Plus 会话中，运行 explain_rewrite3.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\explain_rewrite3.sql
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT c.cust_state_province,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_state_province;
set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);
LEVEL customer IS (customers.cust_id)
LEVEL city IS (customers.cust_city)
LEVEL state IS (customers.cust_state_province)
. . .
HIERARCHY geog_rollup (
customer CHILD OF
city CHILD OF
state CHILD OF
. . .
所请求的 cust_state_province 属性表示州级，这是比用户级（由 cust_id 表示）更高的聚合级。级别和层次代表一种声明方法，用来表示一个表内的 1:n 关系。在这里，它表示在不影响数据完整性的情况下，将所有客户信息汇总到州级的有效性。每个不同的客户值都将获得一个唯一的州值。
2.
现在提交查询。其运行速度相当快。
@c:\wkdir\do_rewrite3.sql
SELECT COUNT(*)
FROM (SELECT c.cust_state_province,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_state_province);
在登录到 SH 模式的 SQL*Plus 会话中，运行 explain_rewrite4.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\explain_rewrite4.sql
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT co.country_name,
c.cust_state_province, SUM(amount_sold) AS dollar_sales
FROM sales s, customers c, countries co
WHERE s.cust_id= c.cust_id
AND c.country_id = co.country_id
GROUP BY co.country_name, c.cust_state_province
ORDER BY 1,2;
set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);
使用物化视图和查询重写功能
优化程序将重写查询以利用 cust_sales_mv 物化视图，将其联回到 customers 并将 customers 联接到 countries 以满足查询。利用与在不需重写的语句级别上强制执行查询相同的方法，可以强制使用特定的物化视图。
LEVEL customer IS (customers.cust_id)
LEVEL . . .
LEVEL state IS (customers.cust_state_province)
LEVEL country IS (countries.country_id)
. . .
HIERARCHY geog_rollup (
customer CHILD OF
. . .
state CHILD OF
country CHILD OF
. . .
JOIN KEY (customers.country_id) REFERENCES country
)
. . .
ATTRIBUTE country DETERMINES (countries.country_name)
Oracle 数据库通过所有这些信息，不但将物化视图与 customers 表联接，而且还将 customers 与 countries 联接，以获得查询的结果，并保证查询结果正确。
2.
重写的 SQL 语句如下所示：
@c:\wkdir\rewrite_sel.sql
SELECT COUNT(*)
FROM (SELECT co.country_name,
c.cust_state_province,
SUM(mv.dollar_sales) AS dollar_sales
FROM cust_sales_mv mv,
(SELECT DISTINCT cust_id, cust_state_province,
country_id FROM customers) c,
countries co
WHERE mv.cust_id = c.cust_id
AND c.country_id = co.country_id
GROUP BY co.country_name, c.cust_state_province
ORDER BY 1,2);
使用物化视图和查询重写功能
3.
现在执行该查询。
@c:\wkdir\do_rewrite4.sql
SELECT c.cust_state_province,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_state_province;
@c:\wkdir\explain_norewrite4.sql
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT /*+ norewrite */
co.country_name,
c.cust_state_province,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c, countries co
WHERE s.cust_id= c.cust_id
AND c.country_id = co.country_id
GROUP BY co.country_name, c.cust_state_province;
set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);
在登录到 SH 模式的 SQL*Plus 会话中，运行 create_mv3.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\create_mv3.sql
DROP MATERIALIZED VIEW some_cust_sales_mv;
CREATE MATERIALIZED VIEW some_cust_sales_mv
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND c.cust_state_province IN
('Dublin','Galway','Hamburg','Istanbul')
GROUP BY c.cust_id, p.prod_id;
使用物化视图和查询重写功能
记录下创建物化视图所耗费的时间。
2.
现在，创建相同的物化视图，只是其中无需谓词来限制结果集。
@c:\wkdir\create_mv3b.sql
DROP MATERIALIZED VIEW all_cust_sales_mv;
CREATE MATERIALIZED VIEW all_cust_sales_mv
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
GROUP BY c.cust_id, p.prod_id;
在登录到 SH 模式的 SQL*Plus 会话中，运行 cr_qw_mmv.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\cr_qw_mmv.sql
CREATE MATERIALIZED VIEW nov_2001
BUILD IMMEDIATE REFRESH COMPLETE
ENABLE QUERY REWRITE AS
SELECT cust_id, prod_id, time_id, sum(amount_sold) AS dollars
FROM sales s
WHERE time_id >= TO_DATE('01-NOV-2001','DD-MON-YYYY') AND
time_id <= TO_DATE('30-NOV-2001','DD-MON-YYYY')
GROUP BY time_id, cust_id, prod_id;
CREATE MATERIALIZED VIEW dec_2001
BUILD IMMEDIATE REFRESH COMPLETE
ENABLE QUERY REWRITE AS
SELECT cust_id, prod_id,
time_id, sum(amount_sold) AS dollars
FROM sales s
WHERE time_id >= TO_DATE('01-DEC-2001','DD-MON-YYYY') AND
time_id <= TO_DATE('31-DEC-2001','DD-MON-YYYY')
GROUP BY time_id, cust_id, prod_id;
EXPLAIN PLAN FOR
SELECT cust_id, sum(amount_sold) AS dollars
FROM sales s
WHERE time_id >= TO_DATE('05-NOV-2001','DD-MON-YYYY') AND
time_id <= TO_DATE('15-DEC-2001','DD-MON-YYYY')
GROUP BY cust_id;
--See how the query uses only these two materialized views to obtain the data
set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);
在登录到 SH 模式的 SQL*Plus 会话中，运行 estimate_mv_size1.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中。这将为您提供包含所有数据的物化视图的估计大小。
@c:\wkdir\estimate_mv_size1.sql
set serveroutput on;
DECLARE
no_of_rows NUMBER;
mv_size NUMBER;
BEGIN
no_of_rows :=555;
mv_size :=5555;
dbms_olap.estimate_summary_size ('MV 1',
'SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
GROUP BY c.cust_id, p.prod_id' , no_of_rows, mv_size );
DBMS_OUTPUT.put_line ( '');
DBMS_OUTPUT.put_line ( 'Complete MV');
DBMS_OUTPUT.put_line ( 'No of Rows: ' || no_of_rows );
DBMS_OUTPUT.put_line ( 'Size of Materialized view (MB): ' ||
round(mv_size/(1024*1024),2) );
DBMS_OUTPUT.put_line ( '');
END;
/
使用物化视图和查询重写功能
记录下创建物化视图所耗费的时间。
2.
确定包含数据子集的物化视图的大小：
@c:\wkdir\estimate_mv_size2.sql
DECLARE
no_of_rows NUMBER;
mv_size NUMBER;
BEGIN
no_of_rows :=555;
mv_size :=5555;
dbms_olap.estimate_summary_size ('MV 2',
'SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND c.cust_state_province IN
(''Dublin'',''Galway'',''Hamburg'',''Istanbul'')
GROUP BY c.cust_id, p.prod_id' , no_of_rows, mv_size );
DBMS_OUTPUT.put_line ( 'Partial MV');
DBMS_OUTPUT.put_line ( 'No of Rows: ' || no_of_rows );
DBMS_OUTPUT.put_line ( 'Size of Materialized view (MB): ' ||
round(mv_size/(1024*1024),2) );
DBMS_OUTPUT.put_line ( '');
END;
/
使用物化视图和查询重写功能
3.
现在，查看数据字典以获取两个新物化视图的实际大小。
@c:\wkdir\comp_mv_size.sql
COLUMN "MV name" format a20
SELECT substr(segment_name,1,30) "MV name", bytes/1024*1024 MB
FROM user_segments
WHERE segment_name in ('SOME_CUST_SALES_MV','ALL_CUST_SALES_MV')
ORDER BY segment_name ;
DECLARE
no_of_msgs NUMBER;
Rewrite_Array SYS.RewriteArrayType := SYS.RewriteArrayType();
querytxt VARCHAR2(1500) :=
'SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_state_province = ''Dublin''
AND (c.cust_id = s.cust_id)
AND (s.prod_id = p.prod_id)
GROUP BY c.cust_id, p.prod_id'
BEGIN
dbms_mview.Explain_Rewrite(querytxt, '', Rewrite_Array);
no_of_msgs := Rewrite_Array.count;
FOR i IN 1..no_of_msgs LOOP
DBMS_OUTPUT.PUT_LINE(Rewrite_Array(i).message);
END LOOP;
END;
/
在登录到 SH 模式的 SQL*Plus 会话中，运行 explain_subset_rewrite2.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\explain_subset_rewrite2.sql
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT c.cust_last_name, sum(s.amount_sold) AS dollars,
sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND c.cust_state_province IN ('Dublin','Galway')
GROUP BY c.cust_last_name;
set linesize 132
set pagesize 999
SELECT * FROM TABLE(dbms_xplan.display);
使用物化视图和查询重写功能
2.
同样运行这个查询：
@c:\wkdir\run_subset_rewrite2.sql
SELECT COUNT(*) FROM (SELECT c.cust_last_name,
sum(s.amount_sold) AS dollars,
sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND c.cust_state_province IN ('Dublin','Galway')
GROUP BY c.cust_last_name);
使用物化视图和查询重写功能
返回主题
返回主题列表
带有 GROUP BY 扩展项的增强的重写功能
通过以 GROUPING SETS、ROLLUP 及其连接的形式对 GROUP BY 子句进行扩展，您可以有选择地在查询的 GROUP BY 子句中指定所需的分组。
带有扩展 GROUP BY 的物化视图必须满足两个附加条件后，方可用于重写：
使用物化视图和查询重写功能
它必须包含分组区分符 (Grouping Distinguisher) — 所有 GROUP BY 表达式上的 GROUPING_ID 函数。例如，如果物化视图的 GROUP BY 子句是 GROUP BY CUBE(a, b)，那么 SELECT 列表应该包含 GROUPING_ID(a, b)。
使用物化视图和查询重写功能
物化视图的 GROUP BY 子句不应导致任何重复分组。例如，GROUP BY GROUPING SETS ((a, b), (a, b)) 将使物化视图不能进行通用重写。
带有扩展 GROUP BY 的物化视图包含多个分组。Oracle 将查找可从中计算查询的成本最低的分组，并将其用于重写。
1.
创建包含扩展 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');
@c:\wkdir\rewrite_gby1.sql
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT calendar_year year, calendar_quarter_desc quarter,
calendar_month_desc month,
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 GROUPING SETS ((calendar_year, cust_city),
(calendar_year, cust_city, cust_state_province),
(calendar_year, calendar_quarter_desc,
calendar_month_desc, cust_city));
PROMPT new output, using table function
set linesize 132
set pagesize 999
SELECT * FROM TABLE(dbms_xplan.display);
使用物化视图和查询重写功能
当物化视图和查询都包含 GROUP BY 扩展时，Oracle 将两个策略用于重写：分组匹配和 UNION ALL 重写。首先，Oracle 尝试分组匹配。查询中的分组与物化视图中的分组进行匹配，如果所有都匹配而没有卷积，则 Oracle 从物化视图中选择它们。组匹配在此例中进行。在不使用任何筛选条件的情况下，对物化视图进行全表扫描，以满足我们的查询。以下查询包含了与物化视图定义不同的 GROUPING SETS。此外，它从不是物化视图组成部分的 customers 表选择 country_id 列。
3.
在登录到 SH 模式的 SQL*Plus 会话中，执行以下 SQL 语句：
@c:\wkdir\rewrite_gby2.sql
PROMPT full access AND joinback to dimension customers
PROMPT decompose of grouping levels into UNION ALL
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT calendar_year year, calendar_quarter_desc quarter,
cust_state_province state, country_id,
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, country_id),
(calendar_year, cust_state_province),
(calendar_year, calendar_quarter_desc,
cust_state_province));
PROMPT new output, using table function
set linesize 132
set pagesize 999
SELECT * FROM TABLE(dbms_xplan.display);
使用物化视图和查询重写功能
在本例中，分组匹配失败。Oracle 将尝试一种名为 UNION ALL 重写的通用重写机制。Oracle 首先将带有扩展 GROUP BY 子句的查询表示为一个效 UNION ALL 查询。原始查询的每个分组都被放置在独立的 UNION ALL 分支中。这些分支都将具有一个简单的 GROUP BY 子句。
为了用现有物化视图实现上述查询，Oracle 已经将 GROUPING SETS 重写到了 3 个简单的 GROUP BY 表达式中，与一个 UNION ALL 运算符组合使用。然后，它单独研究每个 UNION ALL 分支的重写功能。每个分支都可能由包含一个简单（或扩展）GROUP BY 条件的物化视图改写。
@c:\wkdir\rewrite_gby3.sql
PROMPT full access of MV and usage of SALES fact for missing level
PROMPT decompose of grouping levels into UNION ALL
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT calendar_year year, calendar_quarter_desc quarter,
week_ending_day,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 GROUPING SETS ((calendar_year),
(calendar_year, week_ending_day),
(calendar_year, cust_state_province),
(calendar_year, calendar_quarter_desc,
cust_state_province));
PROMPT new output, using table function
set linesize 132
set pagesize 999
SELECT * FROM TABLE (dbms_xplan.display);
@c:\wkdir\prep4_roe.sql
ALTER MATERIALIZED VIEW cust_sales_aggr disable query rewrite;
ALTER MATERIALIZED VIEW cust_sales_mv disable query rewrite;
ALTER MATERIALIZED VIEW sales_cube_mv disable query rewrite;
ALTER MATERIALIZED VIEW sales_gby_mv disable query rewrite;
ALTER MATERIALIZED VIEW nov_2001 disable query rewrite;
ALTER MATERIALIZED VIEW dec_2001 disable query rewrite;
使用物化视图和查询重写功能
2.
以下语句通过任何物化视图都不会进行重写，如计划输出所示。
@c:\wkdir\xplan4_roe.sql
EXPLAIN PLAN FOR
SELECT c.cust_last_name,
c.cust_credit_limit,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_last_name,
c.cust_credit_limit
ORDER BY 1;
set pagesize 50
set linesize 130
SELECT * FROM TABLE(dbms_xplan.display);
使用物化视图和查询重写功能
3.
因此，当您使用新的 REWRITE_OR_ERROR 功能时，查询将会失败。
@c:\wkdir\run_roe.sql
Rem ORA-30393: a query block in the statement did not rewrite
SELECT /*+ REWRITE_OR_ERROR */ c.cust_last_name,
c.cust_credit_limit,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_last_name,
c.cust_credit_limit
ORDER BY 1;
在登录到 SH 模式的 SQL*Plus 会话中，运行 cleanup_for_pmop1.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\cleanup_for_pmop1.sql
ALTER TABLE costs DROP PARTITION costs_q1_2002;
ALTER TABLE costs DROP PARTITION costs_q2_2002;
ALTER TABLE costs DROP PARTITION costs_1_2002;
我们需要物化视图日志，用于下一个物化视图的快速刷新功能。
DROP MATERIALIZED VIEW LOG ON costs;
CREATE MATERIALIZED VIEW LOG ON costs
WITH ROWID, SEQUENCE
(prod_id, time_id, unit_cost, unit_price )
INCLUDING NEW VALUES ;
使用物化视图和查询重写功能
可以忽略任何 ORA-2149 或 ORA-12002 SQL 错误。
返回主题
2. 创建包含分区键的物化视图
要利用物化视图基于分区表的增强功能，最简单的方法是将分区键并入物化视图定义中。
1.
在登录到 SH 模式的 SQL*Plus 会话中，运行 create_pkey_mv.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\create_pkey_mv.sql
DROP MATERIALIZED VIEW costs_mv;
CREATE MATERIALIZED VIEW costs_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT time_id, prod_name, SUM( unit_cost) AS sum_units,
COUNT(unit_cost) AS count_units, COUNT(*) AS cnt
FROM costs c, products p
WHERE c.prod_id = p.prod_id
GROUP BY time_id, prod_name;
使用物化视图和查询重写功能
请注意，您将物化视图定义为 FAST REFRESHABLE ON DEMAND。该物化视图可能会变旧。
2.
物化视图的状态为 FRESH。
@c:\wkdir\show_status_of_pkey_mv.sql
ALTER MATERIALIZED VIEW costs_mv COMPILE;
SELECT mview_name, refresh_mode, refresh_method, staleness
FROM user_mviews
使用物化视图和查询重写功能
返回主题
3. 分析包含分区键的物化视图
1.
使用您已经了解的 dbms_mview.explain_mview 过程。
@c:\wkdir\analyze_pkey_mv.sql
TRUNCATE TABLE mv_capabilities_table;
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('costs_mv');
SET SERVEROUTPUT ON
BEGIN
FOR crec IN (SELECT capability_name, possible, related_text, msgtxt
FROM mv_capabilities_table ORDER BY 1) LOOP
DBMS_OUTPUT.PUT_LINE(crec.capability_name ||': '||crec.possible);
DBMS_OUTPUT.PUT_LINE(crec.related_text||': '||crec.msgtxt);
END LOOP;
END;
/
使用物化视图和查询重写功能
您可以看到，其中启用了分区变化跟踪 (PCT)，用于 COSTS 表和查询重写。
返回主题
4. 对 COSTS 表执行分区维护操作，并检查物化视图的状态
要将一些空分区添加到 costs 表，并察看其如何影响物化视图的陈旧程度，请执行下列步骤：
1.
在登录到 SH 模式的 SQL*Plus 会话中，运行 add_part_to_cost.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\add_part_to_cost.sql
ALTER TABLE costs
ADD PARTITION costs_q1_2002
values less than (TO_DATE('01-APR-2002', 'DD-MON-YYYY'));
ALTER TABLE costs
ADD PARTITION costs_q2_2002
values less than (TO_DATE('01-JUL-2002', 'DD-MON-YYYY'));
使用物化视图和查询重写功能
2.
物化视图的状态为 FRESH。
@c:\wkdir\show_status_of_pkey_mv_cost.sql
ALTER MATERIALIZED VIEW costs_mv COMPILE;
SELECT mview_name, refresh_mode, refresh_method, staleness
FROM user_mviews
WHERE mview_name like 'COST%'
在登录到 SH 模式的 SQL*Plus 会话中，运行 insert_costs.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\insert_costs.sql
INSERT INTO costs VALUES (20, '02-JAN-02',999, 99, 2.50, 25.00);
INSERT INTO costs VALUES (30, '02-FEB-02',999, 99, 2, 34);
INSERT INTO costs VALUES (30, '03-MAR-02',999, 99, 2, 34);
INSERT INTO costs VALUES (40, '21-APR-02',999, 99, 1, 35);
INSERT INTO costs VALUES (40, '22-MAY-02',999, 99, 3, 36);
INSERT INTO costs VALUES (30, '22-APR-02',999, 99, 4, 37);
INSERT INTO costs VALUES (20, '12-JUN-02',999, 99, 5, 34);
COMMIT;
使用物化视图和查询重写功能
2.
查询插入的数据。
@c:\wkdir\show_insert_costs.sql
Rem what's in the new partitions
SELECT *
FROM costs PARTITION (costs_q1_2002);
SELECT *
FROM costs PARTITION (costs_q2_2002);
使用物化视图和查询重写功能
3.
正如预期，物化视图的状态变为 STALE，这是因为新插入的数据当前并未反映在物化视图中。
@c:\wkdir\show_status_of_pkey_mv_cost.sql
ALTER MATERIALIZED VIEW costs_mv COMPILE;
SELECT mview_name, refresh_mode, refresh_method, staleness
FROM user_mviews
WHERE mview_name like 'COST%';
@c:\wkdir\show_sum_pkey_mv.sql
SELECT SUM(sum_units)
FROM costs_mv;
使用物化视图和查询重写功能
4.
显然，物化视图的状态又是新的了。
@c:\wkdir\show_status_of_pkey_mv_cost.sql
ALTER MATERIALIZED VIEW costs_mv COMPILE;
SELECT mview_name, refresh_mode, refresh_method, staleness
FROM user_mviews
WHERE mview_name like 'COST%';
使用物化视图和查询重写功能
返回主题
返回主题列表
带有 PMARKER 信息的物化视图
分区变化跟踪 (PCT) 要求在物化视图中有足够的信息，以能够将每个物化视图行关联回源分区从表中其相应的从表。这可以通过将从表分区键列包含在选择列表中（而且，如果使用了 GROUP BY，也要包含在 GROUP BY 列表中）来实现，如上例中所示。取决于所需的聚合级和分区键列的不同基数，这会造成不利的影响 — 大幅度提高物化视图的基数。例如，假定一个常用指标是一种产品在给定的一年期间产生的收入。如果 sales 表是按 time_id 分区的，那么，它将是物化视图中的 SELECT 子句和 GROUP BY 子句中的必需字段。如果每天销售 1000 种不同产品，这会大大增加物化视图中的行数。在很多情况下，PCT 的优势被这种对高聚合物化视图的限制抵消了。DBMS_MVIEW.PMARKER 函数旨在大幅度降低物化视图的基数。该函数返回一个分区标识符，此标识符唯一地标识特定分区表内的特定行的分区。DBMS_MVIEW.PMARKER 函数用于替代 SELECT 子句和 GROUPBY 子句中的分区键列。下一个示例将演示 PMARKER 函数的功能。
@c:\wkdir\create_pm_mv.sql
DROP MATERIALIZED VIEW costs_pm_mv ;
CREATE MATERIALIZED VIEW costs_pm_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT DBMS_MVIEW.PMARKER(c.rowid) AS pmarker_costs,
prod_name, SUM( unit_cost) AS sum_units,
COUNT(unit_cost) AS count_units, COUNT(*) AS cnt
FROM costs c, products p
WHERE c.prod_id = p.prod_id
GROUP BY prod_name, DBMS_MVIEW.PMARKER(c.rowid);
使用物化视图和查询重写功能
2.
与存储分区键不同，现在每个分区只有一个值，这极大地降低了基数。
@c:\wkdir\show_pm.sql
SELECT pmarker_costs, count(*)
FROM costs_pm_mv group by pmarker_costs;
使用物化视图和查询重写功能
3.
PCT 功能可用于这个物化视图。
@c:\wkdir\analyze_pm_mv.sql
TRUNCATE TABLE mv_capabilities_table;
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('costs_pm_mv');
SET SERVEROUTPUT ON
BEGIN
FOR crec IN (SELECT capability_name, possible, related_text, msgtxt
FROM mv_capabilities_table ORDER BY 1) LOOP
DBMS_OUTPUT.PUT_LINE(crec.capability_name ||': '||crec.possible);
DBMS_OUTPUT.PUT_LINE(crec.related_text||': '||crec.msgtxt);
END LOOP;
END;
/
在登录到 SH 模式的 SQL*Plus 会话中，运行 insert_costs.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\insert_costs.sql
INSERT INTO costs VALUES (20, '02-JAN-02',999, 99, 2.50, 25.00);
INSERT INTO costs VALUES (30, '02-FEB-02',999, 99, 2, 34);
INSERT INTO costs VALUES (30, '03-MAR-02',999, 99, 2, 34);
INSERT INTO costs VALUES (40, '21-APR-02',999, 99, 1, 35);
INSERT INTO costs VALUES (40, '22-MAY-02',999, 99, 3, 36);
INSERT INTO costs VALUES (30, '22-APR-02',999, 99, 4, 37);
INSERT INTO costs VALUES (20, '12-JUN-02',999, 99, 5, 34);
COMMIT;
使用物化视图和查询重写功能
2.
关于最新更改的信息也显示在 COSTS 表的物化视图日志中。
@c:\wkdir\show_mv_log_costs.sql
SELECT count(*)
FROM mlog$_costs;
使用物化视图和查询重写功能
3.
与预期一样，基于 COSTS 表的两个物化视图都呈现陈旧状态。
@c:\wkdir\show_status_costs_mv.sql
ALTER MATERIALIZED VIEW costs_mv COMPILE;
ALTER MATERIALIZED VIEW costs_pm_mv COMPILE;
SELECT mview_name, refresh_mode, refresh_method, staleness
FROM user_mviews
WHERE mview_name like 'COST%';
@c:\wkdir\analyze_part_stale_rewrite.sql
TRUNCATE TABLE rewrite_table;
DECLARE
querytxt VARCHAR2(1500) :=
' SELECT p.prod_name, sum(unit_cost) '||
' FROM costs c, products p ' ||
' WHERE c.prod_id = p.prod_id ' ||
' AND c.time_id >= TO_DATE(''01-JAN-2000'',''DD-MON-YYYY'') ' ||
' AND c.time_id < TO_DATE(''01-JAN-2002'',''DD-MON-YYYY'') ' ||
' GROUP BY prod_name';
BEGIN
dbms_mview.Explain_Rewrite(querytxt, NULL, 'ID1');
END;
/
Rem show final rewrite decision
SELECT message
FROM rewrite_table
ORDER BY sequence desc;
使用物化视图和查询重写功能
用部分陈旧的 costs_pm_mv 物化视图重写该查询。
2.
该计划将显示重写的查询。
@c:\wkdir\explain_part_stale_rewrite.sql
TRUNCATE TABLE plan_table;
EXPLAIN PLAN FOR
SELECT /*+ rewrite (costs_pm_mv) */ p.prod_name, sum(unit_cost)
FROM costs c, products p
WHERE c.prod_id = p.prod_id
AND c.time_id >= TO_DATE('01-JAN-2000','DD-MON-YYYY')
AND c.time_id < TO_DATE('01-JAN-2002','DD-MON-YYYY')
GROUP BY prod_name;
set linesize 132
set pagesize 999
SELECT * FROM TABLE(dbms_xplan.display);
使用物化视图和查询重写功能
3.
现在提交查询。
@c:\wkdir\run_part_stale_query.sql
SELECT p.prod_name, sum(unit_cost)
FROM costs c, products p
WHERE c.prod_id = p.prod_id
AND c.time_id >= TO_DATE('01-JAN-2000','DD-MON-YYYY')
AND c.time_id < TO_DATE('01-JAN-2002','DD-MON-YYYY')
GROUP BY prod_name;
PROMPT **** However, the MV is generically STALE !!
SELECT mview_name, refresh_mode, refresh_method, staleness
FROM user_mviews
WHERE mview_name like 'COSTS%';
PROMPT **** but it only works if you exactly hit the range boundaries
EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM (SELECT p.prod_name, sum(unit_cost)
FROM costs c, products p
WHERE c.prod_id = p.prod_id
AND c.time_id >= TO_DATE('01-JAN-2000','DD-MON-YYYY')
AND c.time_id < TO_DATE('02-JAN-2002','DD-MON-YYYY')
GROUP BY prod_name
);
set linesize 120
SELECT * FROM TABLE(dbms_xplan.display);
在登录到 SH 模式的 SQL*Plus 会话中，运行 check_sum_mvs1.sql,，或将以下 SQL 语句复制到 SQL*Plus 会话中。显示两个物化视图中所有信息的概要：
@c:\wkdir\check_sum_mvs1.sql
Rem However, the MV is generically STALE !!
Rem check data before
SELECT 'Value Before Costs: ' "Value Before Costs:",
SUM(sum_units)
FROM costs_mv;
SELECT 'Value Before Cost with PM: ' "Value Before Cost with PM:",
SUM(sum_units)
FROM costs_pm_mv;
使用物化视图和查询重写功能
2.
您只更改了 COSTS 表中的数据，因此，只需刷新所有依赖于这个表的物化视图。
要只刷新相关物化视图，有一个专用刷新过程：
@c:\wkdir\refresh_costs_mv.sql
Rem Now do a fast refresh using REFRESH_DEPENDENT upon a table
DECLARE
failures INTEGER;
BEGIN
-- set to 555 so that we can see that it changes
failures:= 555;
dbms_mview.refresh_dependent ( failures, 'COSTS', 'F', '',
FALSE,FALSE);
DBMS_OUTPUT.put_line ( 'No of Failures: ' || failures );
END;
/
使用物化视图和查询重写功能
3.
再次检查物化视图中的概要。
@c:\wkdir\check_sum_mvs2.sql
SELECT 'Value After Costs: ', SUM(sum_units)
FROM costs_mv;
SELECT 'Value After Cost with PM: ', SUM(sum_units)
FROM costs_pm_mv;
使用物化视图和查询重写功能
4.
costs_pm_mv 和 costs_pm 物化视图又是新的了。
@c:\wkdir\show_status_costs_mv.sql
ALTER MATERIALIZED VIEW costs_pm_mv COMPILE;
ALTER MATERIALIZED VIEW costs_mv COMPILE;
SELECT mview_name, refresh_mode, refresh_method, staleness
FROM user_mviews
WHERE mview_name like 'COST%';
使用物化视图和查询重写功能
返回主题
返回主题列表
使用 TUNE_MVIEW 使物化视图快速刷新
新的 TUNE_MVIEW API 给出了需要对物化视图进行的更改，以使其能够快速刷新并适用于高级查询重写技术。在本小节中，我们将检查：
使用物化视图和查询重写功能
使用 Tune_Mview 生成物化视图建议
使用物化视图和查询重写功能
使用 Tune_Mview 使物化视图快速刷新
使用 Tune_Mview 生成物化视图建议
要使用 Tune_Mview 生成物化视图建议，请按以下步骤进行：
1.
使用新的调整功能来获得关于您将要创建的潜在物化视图的建议。
在登录到 SH 模式的 SQL*Plus 会话中，运行 tune_mv01.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：
@c:\wkdir\tune_mv01.sql
Rem *** START TUNE_MVIEW section 10g
VARIABLE name_01 varchar2(30)
Rem tunemview01
EXECUTE DBMS_ADVISOR.TUNE_MVIEW -
( :name_01 -
, 'CREATE MATERIALIZED VIEW prod_mv -
REFRESH FAST WITH ROWID -
ENABLE QUERY REWRITE -
AS -
SELECT DISTINCT -
prod_name, prod_category -
FROM products' -
) ;
使用物化视图和查询重写功能
2.
查看物化视图的结果。
@c:\wkdir\mvtune_result01.sql
Rem mvtune_results01.sql
column statement format a70 word
set long 999
SELECT statement
FROM DBA_TUNE_MVIEW
WHERE task_name = :name_01
ORDER BY script_type, action_id;
使用物化视图和查询重写功能
请注意，DISTINCT 子句被 GROUP BY 所替代，而且 COUNT(*) 被添加到了
SELECT 子句。这使的物化视图可以进行快速更新，并可用于一般重写。
返回主题
使用 Tune_Mview 使物化视图快速刷新
要使用 Tune_Mview 使物化视图快速刷新，请按以下步骤进行：
1.
需要清除环境，以专门构建一个不能在此创建可快速刷新的物化视图的环境。运行下列脚本来清理环境。
@c:\wkdir\cleanup4tune_mv02.sql
DROP MATERIALIZED VIEW LOG ON customers;
DROP MATERIALIZED VIEW LOG ON countries;
DROP MATERIALIZED VIEW cuco_mv;
CREATE MATERIALIZED VIEW LOG ON customers
WITH SEQUENCE, ROWID INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON countries
WITH SEQUENCE, ROWID INCLUDING NEW VALUES;
使用物化视图和查询重写功能
2.
尝试创建一个可快速更新的物化视图。尝试将失败。
@c:\wkdir\cr_fr_mv.sql
Rem fails - not fast refreshable
CREATE MATERIALIZED VIEW cuco_mv
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT cu.cust_last_name
, co.country_name
FROM customers cu
, countries co
WHERE cu.country_id = co.country_id;
@c:\wkdir\tune_mv02.sql
VARIABLE name_02 varchar2(30)
Rem tunemview02.sql
EXECUTE DBMS_ADVISOR.TUNE_MVIEW -
( :name_02 -
, 'CREATE MATERIALIZED VIEW cuco_mv -
REFRESH FAST -
ENABLE QUERY REWRITE -
AS -
SELECT cu.cust_last_name -
, co.country_name -
FROM customers cu -
, countries co -
WHERE cu.country_id = co.country_id' -
) ;
与用于第一个示例的建议一样，SQL Access Advisor 的建议存储在数据库中。
column statement format a70 word
set long 999
SELECT script_type as type, statement
FROM DBA_TUNE_MVIEW
WHERE task_name = :name_02
ORDER BY script_type, action_id;
