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';
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;
接下來，通過查詢一個新的 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
