第43/44/45/46单：2015-11-28/29双休日

botang · 发表于 2015-11-30 09:44:17

本帖最后由 botang 于 2015-12-4 23:19 编辑

s4.sql：

CREATE MATERIALIZED VIEW CAL_MONTH_SALES_MV REFRESH fast ENABLE QUERY REWRITE AS SELECT t.calendar_month_desc , sum(s.amount_sold) AS dollars
FROM sales s
, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc

复制代码

s42.sql：

var a varchar2(4000);
var b varchar2(200);
begin
:a := 'CREATE MATERIALIZED VIEW sh.CAL_MONTH_SALES_MV REFRESH fast ENABLE QUERY REWRITE AS SELECT t.calendar_month_desc , sum(s.amount_sold) AS dollars FROM sh.sales s , sh.times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc' ;
end;
/
print :a
begin
dbms_advisor.tune_mview(:b,:a);
end;
/
print :b;
set linesize 1000
set long 10000
set pagesize 1000
select dbms_advisor.GET_TASK_SCRIPT(:b) from dual;

复制代码

s43.sql：

复制代码

s44.sql：

复制代码

s45.sql：

复制代码

s46.sql：

var a varchar2(4000);
var b varchar2(200);
begin
:a :='CREATE MATERIALIZED VIEW SH.MV1 REFRESH FAST ENABLE QUERY REWRITE AS SELECT time_id,prod_subcategory,SUM(unit_cost) ucost FROM sh.costs c,sh.products p where c.prod_id=p.prod_id GROUP BY time_id,prod_subcategory' ;
end;
/
print :a
begin
dbms_advisor.tune_mview(:b, :a);
end;
/
print :b
set pagesize 1000
set linesize 1000
set long 10000
select dbms_advisor.get_task_script(:b) from dual;

复制代码

s47.sql：

CREATE MATERIALIZED VIEW LOG ON
"SH"."COSTS"
WITH ROWID, SEQUENCE("PROD_ID","TIME_ID","UNIT_COST")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"SH"."PRODUCTS"
WITH ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
INCLUDING NEW VALUES;
drop MATERIALIZED VIEW SH.MV1;
CREATE MATERIALIZED VIEW SH.MV1
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM("SH"."COSTS"."UNIT_COST")
M1, COUNT("SH"."COSTS"."UNIT_COST") M2, COUNT(*) M3 FROM SH.PRODUCTS,
SH.COSTS WHERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY,
SH.COSTS.TIME_ID;

复制代码