物化视图 (MV) — 也称为快照 — 一段时间来已经广泛使用。 MV 在一个段中存储查询结果,并且能够在提交查询时将结果返回给用户,从而不再需要重新执行查询 — 在查询要执行几次时(这在数据仓库环境中非常常见),这是一个很大的好处。物化视图可以利用一个快速刷新机制从基础表中全部或增量刷新。 假定您已经定义了一个物化视图,如下: 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'; 您如何才能知道已经为这个物化视图创建了其正常工作所必需的所有对象?在 Oracle 数据库 10 g 之前,这是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 过程来判断的。这些过程(在 10 g 中仍然提供)非常简要地说明一种特定的功能 — 如快速刷新功能或查询重写功能 — 可能用于上述的物化视图,但不提供如何实现这些功能的建议。相反,需要对每一个物化视图的结构进行目视检查,这是非常不实际的。 在 10 g 中,新的 DBMS_ADVISOR 程序包中的一个名为 TUNE_MVIEW 的过程使得这项工作变得非常容易:您利用 IN 参数来调用程序包,这构造了物化视图创建脚本的全部内容。该过程创建一个顾问程序任务 (Advisor Task) ,它拥有一个特定的名称,仅利用 OUT 参数就能够把这个名称传回给您。 下面是一个例子。因为第一个参数是一个 OUT 参数,所以您需要在 SQL*Plus 中定义一个变量来保存它。 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 SCRIPT_TYPE 列显示建议的性质。大多数行将要执行,因此名称为 IMPLEMENTATION 。如果接受,则需按照由 ACTION_ID 列指出的特定顺序执行建议的操作。
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 SCRIPT_TYPE 列显示建议的性质。大多数行将要执行,因此名称为 IMPLEMENTATION 。如果接受,则需按照由 ACTION_ID 列指出的特定顺序执行建议的操作。
解决方案 »
- Oracle在使用sys/change_on_install登录到sqlplus,浏览器em中登录不进去()
- oracle 用trigger动态创建表格
- SELECT BLOB字段的问题
- linux下怎么导入从windows导出的oracle的dmp,用sql plus
- 小弟又来麻烦大师们了
- 求一款数据库数据的迁移工具,支持Oracle/DB2/SQL Server/MySQL的就行了
- %%ORACLE_SID%%T%TS%S.ARC 是什么?
- 基于P4 CPU的windowsXP平台上安装oracle 8i错误:symcjit.dll更名为symcjit.old虽能成功安装,但总是java.exe报错
- ORACLE9在redhat 9.0下安装问题,各位大虾,求教求教!!
- 如何将decode转化为标准sql
- 十万火急。求助:report的runtime设定中的Non-blocking SQL选项,如何动态修改?
- 错在哪,简单问题
set feedback 1 set linesize 80 set trimspool on set tab off set pagesize 60
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON "ARUP"."HOTELS" WITH ROWID, SEQUENCE("HOTEL_ID","CITY") INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."HOTELS" ADD ROWID, SEQUENCE("HOTEL_ID","CITY") INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON "ARUP"."RESERVATIONS" WITH ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME") INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."RESERVATIONS" ADD ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME") INCLUDING NEW VALUES;
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;
whenever sqlerror EXIT SQL.SQLCODE
begin dbms_advisor._recommendation('TASK_117',1,'IMPLEMENTED'); end; / 这个文件包含了您实施建议所需的一切,从而为您省去了相当大的手动创建文件的麻烦。这个自动数据库管理员又一次能够为您完成工作。 重写或退出! 至此,您一定意识到了查询重写特性有多重要和多有用。它显著地减少了 I/O 和处理,并能够更快地返回结果。 让我们基于上述例子假定一种情况。用户执行以下查询: Select city, sum(actual_rate) from hotels h, reservations r, trans t where t.resv_id = r.resv_id and h.hotel_id = r.hotel_id group by city; 执行状态显示以下内容: 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 478 bytes sent via SQL*Net to client 496 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 注意 consistent gets 的值,它为 6 — 一个非常低的值。这个结果基于的事实是,重写了查询来使用在三个表上创建的两个物化视图。选择不是从表中进行的,而是从物化视图中进行,从而消耗了更少的资源(如 I/O 和 CPU )。 但如果查询重写失败了,那该怎么办?它失败的原因可能有以下几种:如果初始化参数 query_rewrite_integrity 的值被设为 TRUSTED ,且 MV 的状态是 STALE ,那么将不会重写该查询。您可以通过在查询之前在会话中设定这个值来模拟这个过程。 alter session set query_rewrite_enabled = false; 在这条命令之后,说明计划 (EXPLAIN PLAN) 显示是从所有三个表中而不是从 MV 中作出的选择。执行状态现在显示: 0 recursive calls 0 db block gets 16 consistent gets 0 physical reads 0 redo size 478 bytes sent via SQL*Net to client 496 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 注意 consistent gets 的值:它从 6 猛增到了 16 。在实际情况下,这个结果可能无法接受,因为无法提供所需的额外资源,因此您可能想自己重写查询。在这种情况下,您可以确保如果而且只有在查询被重写的情况下,才允许进行查询。 在 Oracle9 i 数据库和更低版本中,决策是单向的:您可以禁用查询重写,但不能禁用基础表访问。不过 Oracle 数据库 10 g 提供了一种机制 — 通过一个特殊的提示 REWRITE_OR_ERROR 来实现这一目的。上述查询将利用该提示写为: select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate) from hotels h, reservations r, trans t where t.resv_id = r.resv_id and h.hotel_id = r.hotel_id group by city; 注意现在的错误消息。 from hotels h, reservations r, trans t * ERROR at line 2: ORA-30393:a query block in the statement did not rewrite