创建快速刷新物化视图

1、初始化环境
SYS@PROD1>create materialized view sh.prod_cost_mv as select time_id,prod_subcategory,sum(unit_cost) as sum_units from costs c,products p where c.prod_id=p.prod_id group by time_id,prod_subcategory;

2、开启重写查询参数
SYS@PROD1>show parameter query;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced
SYS@PROD1>

SYS@PROD1>alter system set query_rewrite_enabled =true;


System altered.


SYS@PROD1>
3、授予sh用户对应权限
SYS@PROD1>grant connect,resource,create materialized view to sh;


Grant succeeded.


SYS@PROD1>grant create any directory to sh;


Grant succeeded.


SYS@PROD1>grant advisor to sh;


Grant succeeded.


SYS@PROD1>

SYS@PROD1>alter user sh identified by sh account unlock;

4、通过EM获取创建物化视图DDL语句；
（1）登录em-找到schema选项
（2）在schema中找到materialized view点如进去
（3）在schema中输入用户点击go
（4）选择创建的物化视图prod_cust_mv,在action选项中选择 generate ddl点击go，即可得到创建物化视图的ddl语句

CREATE MATERIALIZED VIEW "SH"."PROD_COST_MV" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE
( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING USING INDEX TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE
( INITIAL 64K BUFFER_POOL DEFAULT) REFRESH FORCE ON DEMAND AS SELECT time_id, prod_subcategory,SUM( unit_cost) AS sum_units FROM costs c, products p
WHERE c.prod_id = p.prod_id GROUP BY time_id, prod_subcategory

5、创建mv.sql脚本 得到创建视图的语句
（1）打开官方文档找到database administration 选中，在右边目录中找到，Performance Tuning Guide 点击进入目录，选择18 SQL Access Advisor
        找到Example 18-1 Optimizing the Defining Query for Fast Refresh
       VARIABLE task_cust_mv VARCHAR2(30);
VARIABLE create_mv_ddl VARCHAR2(4000);
EXECUTE :task_cust_mv := 'cust_mv';

 （2）同上找到如下代码：
    EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);

     CREATE DIRECTORY TUNE_RESULTS AS '/myscript'
    GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;


   EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv), -
   'TUNE_RESULTS', 'mv_create.sql');

（3）得到完整的mv.sql语句为：
VARIABLE task_cust_mv VARCHAR2(4000);
VARIABLE create_mv_ddl VARCHAR2(4000);
EXECUTE :task_cust_mv := 'cust_mv';
EXECUTE :create_mv_ddl := 'CREATE MATERIALIZED VIEW "SH"."PROD_COST_MV" TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING USING INDEX TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) REFRESH FORCE ON DEMAND AS SELECT time_id, prod_subcategory,SUM( unit_cost) AS sum_units FROM costs c, products p WHERE c.prod_id = p.prod_id GROUP BY time_id, prod_subcategory';


EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
CREATE DIRECTORY TUNE_RESULTS AS '/home/oracle/files'
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;


EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv), 'TUNE_RESULTS', 'mv_create.sql');

6、使用sh用户登录并执行mv.sql语句获得创建快速刷新物化视图的语句；
 
（1）第一次执行失败
SH@PROD1>@/home/oracle/files/mv.sql


PL/SQL procedure successfully completed.




PL/SQL procedure successfully completed.




PL/SQL procedure successfully completed.


GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC
*
ERROR at line 2:
ORA-00901: invalid CREATE command




BEGIN DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv), 'TUNE_RESULTS', 'mv_create.sql'); END;


*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "SYS.PRVT_ADVISOR", line 1144
ORA-06512: at "SYS.DBMS_ADVISOR", line 548
ORA-06512: at line 1

（2）删除该job
   

SH@PROD1>exec dbms_advisor.delete_task('cust_mv');


PL/SQL procedure successfully completed.

查找错误原因：

CREATE DIRECTORY TUNE_RESULTS AS '/home/oracle/files'----此处少了；
（3）修正后再次执行（注意粘贴过来的代码会有空格或换行键，遇到相关错误可手动打一遍）
 SH@PROD1>@/home/oracle/files/mv.sql


PL/SQL procedure successfully completed.




PL/SQL procedure successfully completed.




PL/SQL procedure successfully completed.




Directory created.




Grant succeeded.




PL/SQL procedure successfully completed.

7、删除原来的物化视图
SH@PROD1>drop materialized view sh.prod_cost_mv;

8、使用vi编辑生成的mv_create.sql

将脚本中的disable改成为enable
9、执行调整后的脚本
SH@PROD1>@/home/oracle/files/mv_create.sql


Materialized view log created.




Materialized view log altered.




Materialized view log created.




Materialized view log altered.




Materialized view created.

11、收集统计信息
SH@PROD1>exec dbms_stats.gather_table_stats('SH','PROD_COST_MV',degree=>2);



PL/SQL procedure successfully completed.

12、查看执行计划，检查效果
SH@PROD1>set autot trace exp
SH@PROD1>select time_id,prod_subcategory,sum(unit_cost) as sum_units from costs c,products p where c.prod_id=p.prod_id group by time_id,prod_subcategory;


Execution Plan
----------------------------------------------------------
Plan hash value: 2761323600


--------------------------------------------------------------------------------
-------------


| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU
)| Time     |


--------------------------------------------------------------------------------
-------------


|   0 | SELECT STATEMENT             |              | 13358 |   352K|    22   (0
)| 00:00:01 |


|   1 |  MAT_VIEW REWRITE ACCESS FULL| PROD_COST_MV | 13358 |   352K|    22   (0
)| 00:00:01 |


--------------------------------------------------------------------------------
-------------

当看到 MAT_VIEW REWRITE ACCESS FULL| PROD_COST_MV说明查询已生效

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30317998/viewspace-2120582/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30317998/viewspace-2120582/