设为首页收藏本站

Botang唐波's Oracle Station

查看: 1148|回复: 0

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

[复制链接]

677

主题

1037

帖子

7580

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7580
发表于 2015-11-30 09:44:17 | 显示全部楼层 |阅读模式
本帖最后由 botang 于 2015-12-4 23:19 编辑

s4.sql:
  1. CREATE MATERIALIZED VIEW CAL_MONTH_SALES_MV REFRESH fast ENABLE QUERY REWRITE   AS SELECT   t.calendar_month_desc   ,   sum(s.amount_sold) AS dollars
  2.   FROM     sales s
  3.   ,        times t
  4.   WHERE    s.time_id = t.time_id
  5.   GROUP BY t.calendar_month_desc
复制代码
s42.sql:
  1. var a varchar2(4000);
  2. var b varchar2(200);

  3. begin
  4. :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' ;
  5. end;
  6. /


  7. print :a

  8. begin
  9. dbms_advisor.tune_mview(:b,:a);
  10. end;
  11. /

  12. print :b;


  13. set linesize 1000
  14. set long 10000
  15. set pagesize 1000
  16. select  dbms_advisor.GET_TASK_SCRIPT(:b)  from dual;
复制代码


s43.sql:

  1. CREATE MATERIALIZED VIEW LOG ON
  2.     "SH"."SALES"
  3.     WITH ROWID, SEQUENCE("TIME_ID","AMOUNT_SOLD")
  4.     INCLUDING NEW VALUES;


  5. CREATE MATERIALIZED VIEW LOG ON
  6.     "SH"."TIMES"
  7.     WITH ROWID, SEQUENCE("TIME_ID","CALENDAR_MONTH_DESC")
  8.     INCLUDING NEW VALUES;

  9. drop MATERIALIZED VIEW SH.CAL_MONTH_SALES_MV;

  10. CREATE MATERIALIZED VIEW SH.CAL_MONTH_SALES_MV
  11.     REFRESH FAST WITH ROWID
  12.     ENABLE QUERY REWRITE
  13.     AS SELECT SH.TIMES.CALENDAR_MONTH_DESC  C1, SUM("SH"."SALES"."AMOUNT_SOLD") M1,
  14.       COUNT("SH"."SALES"."AMOUNT_SOLD")        M2, COUNT(*) M3 FROM SH.TIMES, SH.SALES
  15.      WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID
  16.        GROUP BY SH.TIMES.CALENDAR_MONTH_DESC;
复制代码

s44.sql:
  1. CREATE MATERIALIZED VIEW SH.CAL_MONTH_SALES_MV
  2. REFRESH FAST WITH ROWID
  3. ENABLE QUERY REWRITE
  4. AS SELECT SH.TIMES.CALENDAR_MONTH_DESC  C1, SUM("SH"."SALES"."AMOUNT_SOLD") M1,
  5. COUNT("SH"."SALES"."AMOUNT_SOLD")    M2, COUNT(*) M3 FROM SH.TIMES, SH.SALES
  6. WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID
  7. GROUP BY SH.TIMES.CALENDAR_MONTH_DESC;
复制代码

s45.sql:

  1. CREATE MATERIALIZED VIEW "SH"."MV1"
  2.   REFRESH FORCE ON DEMAND
  3.   ENABLE QUERY REWRITE
  4.   AS SELECT time_id,prod_subcategory,SUM(unit_cost) ucost
  5. FROM costs c,products p
  6. where c.prod_id=p.prod_id
  7. GROUP BY time_id,prod_subcategory
复制代码

s46.sql:

  1. var a varchar2(4000);
  2. var b varchar2(200);


  3. begin
  4. :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'  ;
  5. end;
  6. /

  7. print :a

  8. begin
  9. dbms_advisor.tune_mview(:b, :a);
  10. end;
  11. /

  12. print :b


  13. set pagesize 1000

  14. set linesize 1000

  15. set long 10000


  16. select  dbms_advisor.get_task_script(:b) from dual;
复制代码

s47.sql:

  1. CREATE MATERIALIZED VIEW LOG ON
  2.     "SH"."COSTS"
  3.     WITH ROWID, SEQUENCE("PROD_ID","TIME_ID","UNIT_COST")
  4.     INCLUDING NEW VALUES;


  5. CREATE MATERIALIZED VIEW LOG ON
  6.     "SH"."PRODUCTS"
  7.     WITH ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
  8.     INCLUDING NEW VALUES;


  9. drop MATERIALIZED VIEW SH.MV1;

  10. CREATE MATERIALIZED VIEW SH.MV1
  11.     REFRESH FAST WITH ROWID
  12.     ENABLE QUERY REWRITE
  13.     AS SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM("SH"."COSTS"."UNIT_COST")
  14.        M1, COUNT("SH"."COSTS"."UNIT_COST") M2, COUNT(*) M3 FROM SH.PRODUCTS,
  15.        SH.COSTS WHERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY,
  16.        SH.COSTS.TIME_ID;
复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-7-16 11:03 , Processed in 0.152650 second(s), 24 queries .

快速回复 返回顶部 返回列表