Oracle中管理物化视图变得更加容易

核心提示： SQL> -- 首先定义一个变量来保存 OUT 参数：SQL> var adv_name varchar2(20)SQL>begin2dbms_advisor.tune_mview3 (4:adv_name,5'create materialized view m

SQL> -- 首先定义一个变量来保存 OUT 参数：

　　SQL> var adv_name varchar2(20)
　　SQL>　begin
　　2　dbms_advisor.tune_mview
　　　3　　 (
　　4　　　　:adv_name,
　　5　　　　'create materialized view mv_hotel_resv refresh
　　　　　　fast enable query rewrite as
　　　　　　select distinct city, resv_id, cust_name from hotels h,
　　 　　　 reservations r where r.hotel_id = h.hotel_id');
　　6* end;

现在您可以在该变量中找出顾问程序的名称。　　SQL> print adv_name
　　
　　ADV_NAME
　　-----------------------
　　TASK_117

接下来，通过查询一个新的 DBA_TUNE_MVIEW 来获取由这个顾问程序提供的建议。务必在运行该命令之前执行 SET LONG 999999，因为该视图中的列语句是一个 CLOB，默认情况下只显示 80 个字符。　　select script_type, statement
　　from　 dba_tune_mview
　　where　task_name = 'TASK_117'
　　order　by script_type, action_id;

下面是输出：　　SCRIPT_TYPE　　STATEMENT
　　-------------- ------------------------------------
　　IMPLEMENTATION CREATE MATERIALIZED VIEW LOG
　　ON "ARUP"."HOTELS" WITH ROWID,
　　SEQUENCE ("HOTEL_ID","CITY")　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE
　　ON "ARUP"."HOTELS" ADD
　　ROWID, SEQUENCE ("HOTEL_ID","CITY")　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION CREATE MATERIALIZED VIEW LOG
　　ON "ARUP"."RESERVATIONS" WITH
　　ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
　　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE
　　ON "ARUP"."RESERVATIONS"
　　ADD ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
　　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION CREATE MATERIALIZED VIEW ARUP.
　　MV_HOTEL_RESV　 REFRESH FAST
　　WITH ROWID ENABLE QUERY REWRITE AS SELECT
　　ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID
　　C2, ARUP.HOTELS.CITY C3, COUNT(*) M1 FROM ARUP.RESERVATIONS,
　　ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID =
　　ARUP.RESERVATIONS.HOTEL_ID GROUP BY
　　ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
　　ARUP.HOTELS.CITY
　　
　　UNDO　　　　　 DROP MATERIALIZED VIEW ARUP.MV_HOTEL_RESV