2024年1月20日发(作者：)
Query Rewrite 在数据仓库是是一个非常有用的技术, Tom在< Design>>一书中将实体化视图(MView)称为是数据仓库的索引, 这是再贴切不过的了, 在OLTP中当SELECT语句的所有的字段都在索引中时, Oracle可以不从表读数据, 而直接从索引中获得全部信息, 而Query Rewrite则是通过创建中间表, 让Oracle自动从创建的中间表读取数据, 而不需要从原表读取了, 这个中间表可以是预先join好的或预先计算好的中间结果. 他的使用就和一般的索引同理了, 虽然你指定的还是那个大表, 但oracle可以为你自动识别可以从那个"数据仓库索引"中读取数据. 下面我们可以来看一下最简单的例子: SQL> CREATE TABLE DETAIL_TABLE 2 AS SELECT OWNER,OBJECT_NAME FROM _objects; Table created. SQL> DESC DETAIL_TABLE Name Null? Type ---------------------------------- -------- -------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SQL> CREATE MATERIALIZED VIEW MID_TABLE ENABLE QUERY REWRITE 2 AS 3 SELECT OWNER,COUNT(*) TABCNT FROM DETAIL_TABLE GROUP BY OWNER; Materialized view created. SQL> DESC MID_TABLE Name Null? Type --------------------------------- -------- -------------- OWNER VARCHAR2(30) TABCNT NUMBER 现在假设DETAIL_TABLE是一个非常大的表, MID_TABLE是预先计算好的中间表, 我们来做一个查询测试一下, 要让Oracle使用Query Rewrite,首先要使用CBO,我已经对这个两个表作了分析, 然后将query_rewrite_enabled参数设为True,这个参数可以在session一级更改. 如: SQL> show parameter query_rewrite_enabled NAME TYPE VALUE ------------------------------------ ----------- --------- query_rewrite_enabled string FALSE SQL> SELECT OWNER,COUNT(*) FROM DETAIL_TABLE 2 WHERE OWNER='SYSTEM' GROUP BY OWNER; OWNER COUNT(*) ------------------------------ ---------- SYSTEM 406 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 ...) 1 0 SORT (GROUP BY NOSORT) (Cost=4 ...) 2 1 TABLE ACCESS (FULL) OF 'DETAIL_TABLE' (Cost=4 ... SQL> ALTER SESSION SET query_rewrite_enabled=TRUE; Session altered. SQL> SELECT OWNER,COUNT(*) FROM DETAIL_TABLE 2 WHERE OWNER='SYSTEM' GROUP BY OWNER; OWNER COUNT(*) ------------------------------ ---------- SYSTEM 406 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=8) 1 0 TABLE ACCESS (FULL) OF 'MID_TABLE' (Cost=2 Card=1 Bytes=8) 大家可以看一下上面两个同样的语句在query rewrite时启用和禁用时的plan的区别, 可以看到MID_TABLE的作用就象是一个索引. 除了Query_rewrite_enabled参数外, 还有一个参数query_rewrite_integrity会影响Query Rewrite的使用. 在Document CD的Data Warehouse Guide中有对Query Rewrite的详细介绍, 对于DBA我强烈建议将这个文档看上三次. 在Oracle的Query Rewrite中主要有三点, 第一是要使用CBO; 第二是要设置query rewrite enabled参数为TRUE; 第三是要先择设置query rewrite integrity参数的值(stale_tolerated, trusted, enforced). 对于第一点, 我们最好analyze相关的表及索引及MV; 对于第二点,这个参数只有两个值(true, false), 很简单; 对于第三点, 我们先来看Oracle的官方对于这个参数的解释: ENFORCED Oracle enforces and guarantees consistency and integrity TRUSTED Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle. STALE_TOLERATED Oracle allows rewrites using unenforced relationships. Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data. 这个参数有点难于理解一些, 但主要和数据的一致性有关, 在Oracle的Query Rewrite中, 一些约束的声明或状态和Oracle决于可否Query Rewrite有很大的关系. ENFORCED表示Oracle只相信Enabled和Validated的约束, 而Trusted则相信RELY的约束, 就算这个约束没有Enabled和Validated, 这两种都要求MVIEW中的数据是及时刷新的,而STALE_TOLERATED则可以容忍一切, 就算中间表的数据是旧的, 指基表有新数据修改而MVIEW还没有刷新的情况下, Oracle也会选择使用Query Rewrite来作查询, 在这种情况下, 查出来的数据可能是不准的. 下面我们来作一个例子来显示enforced与trusted的不同: 接着前面的例子,我们创建这样一个实体化视图: CREATE MATERIALIZED VIEW MV_TABLE ENABLE QUERY REWRITE AS SELECT #,COUNT(*) OBJCNT FROM USR_TABLE U,OBJ_TABLE O WHERE #=# group by # 接下来我们创建这样的两个约束: ALTER TABLE USR_TABLE ADD PRIMARY KEY (USER#) RELY DISABLE; ALTER TABLE OBJ_TABLE ADD FOREIGN KEY (USER#) REFERENCES USR_TABLE(USER#) RELY DISABLE; 下接来创建一个USR_LEVEL的表, 如下所示: CREATE TABLE USR_LEVLEL AS SELECT USER#, TRUNC(USER#/10) ULEVEL FROM USR_TABLE; 实验所需要的表都建起来了, 对三个表和一个MVIEW进行分析后, 下面来做测试: SQL> SHOW PARAMETER QUERY_REWRITE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ query_rewrite_enabled string TRUE query_rewrite_integrity string TRUSTED SQL> SET AUTOT ON EXP SQL> select ,count(*) from usr_level l,obj_table o 2 where #=# 3 group by ; ULEVEL COUNT(*) ---------- ---------- 0 101456 1 136 2 9939 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 ... 1 0 SORT (GROUP BY) (Cost=7 ... 2 1 HASH JOIN (Cost=5 ... 3 2 TABLE ACCESS (FULL) OF 'MV_TABLE' (Cost=2 ... 4 2 TABLE ACCESS (FULL) OF 'USR_LEVEL' (Cost=2 ... SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=ENFORCED; Session altered. SQL> select ,count(*) from usr_level l,obj_table o 2 where #=# 3 group by ; ULEVEL COUNT(*) ---------- ---------- 0 101456 1 136 2 9939 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3239 ... 1 0 SORT (GROUP BY) (Cost=3239 ... 2 1 HASH JOIN (Cost=50 ... 3 2 TABLE ACCESS (FULL) OF 'USR_LEVEL' (Cost=2 ... 4 2 TABLE ACCESS (FULL) OF 'OBJ_TABLE' (Cost=47 ... 通过对比Cost和Plan可以看出那个Plan更好. 在Query Rewrite中大家看到这个技术离不开一样东西, 实体化视图, 简称MVIEW. 这是Oracle在8i中首先推出的技术, MVIEW除了在Query Rewrite中使用外, 还在Master - Slave复制中有很重要的作用, 在这儿我们主要关心Query Rewrite相关的地方, Oracle在Query Rewrite方面越来越强了, 在Oracle 8i中基本上是Text Match的Query Rewrite, 在9i/10g中有很大的更新了, 还支持一般的Query Rewrite(指Text Match以外的), 如可以试一下最后一个SQL语句, 在8i中不能rewrite, 而在9i中却可以: SQL> desc mvtest8 Name Null? Type ------------------------------ -------- ----------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SQL> create materialized view mv8_test 2 enable query rewrite 3 as 4 select owner,count(*) rowcnt from mvtest8 5 group by owner; SQL> select count(*) from mvtest8 where owner='SYS'; 在应用MVIEW实现Query Rewrite时, 需要对MVIEW有一个基本的了解, 主要要理解以下两点: 1, MVIEW的设计, 可以满足多少SQL的Rewrite. 2, MVIEW的刷新, MIVEW是一个中间表, 数据不一定能最新, 对于大量的数据刷新方法也是很重要的. 其实在这两点之间是有些冲突的, 要实现第一点, 可能复杂的MVIEW定义有利于提高Query Rewrite后的执行效率, 但复杂的MVIEW每次刷新都可能要抄描全部记录, 对于MVIEW刷新, 在有MVIEW LOG有情况下, MVIEW可以实现增量刷新的, 一般来说要实现增量刷新需要满足下面这样的条件: 1, MVIEW的SQL中不能包函返回值不确定的函数, 如sysdate, rownum等等. 2, MVIEW的SQL中只包括汇总时,最好将COUNT(*), COUNT(字段) 这样的分组函数加进去. 3, MVIEW的SQL中只包括Join时. 关于创建MVIEW和MVIEW LOG的语法在这儿就不说了, MVIEW的刷新有三种选项, FULL: 完全刷新; FAST: 利用MVIEW LOG进行增量刷新; FORCE: 如果不能用FAST, 就用FULL. 对于刷新的时机主要有两种, ON DEMAND: 手工刷新(Crontab, Job, Schedule等), ON COMMIT: 在Commit后进行马上刷新(必须是Fast, 否则你惨了). 以看到MVIEW在Query Rewrite中的重要性, 要在实际应用中使用, 就得知道它的很多方面, 其中刷新是最主要的: 1, MVIEW日志的建立 2, 汇总型的MIVEW的刷新 3, JOIN类型的MVIEW的刷新 4, 更复杂的MVIEW的刷新 5, 分区时的MVIEW的刷新 在这儿我们主要讨论的是如何实现Fast刷新, 否则没有多少意议的. 我们一点一点来看: 1, 要实现增量刷新, 必须在MVIEW引的的表上创建MVIEW Log, 我们主要来说一下几个选项 WITH ROWID/PRIMARY KEY : 在MVIEW Log中记录ROWID或主键以反映改更过的记录, 对于一般表我推荐用WITH ROWID, 对于IOT, 则请用PRIMARY KEY. (column list): 为了让MVIEW LOG变得小一点你可以只包括进在MVIEW的SQL中引用的字段, Oracle用表来实现MVIEW LOG在更新频繁的表上, MVIEW LOG可能会变得很大. 在指定了WITH PRIMARY KEY时, 主键的列已经包括了, 因此在column list中就不要再写进去了. WITH SEQUENCE: 用于记录修改发生的顺序, 如果没有对基表的DELETE操作则可以不用加这个选项. INCLUDING NEW VALUES: 主要用于在汇总型的MVIEW时, 同时记录字段的旧值和新值以实现快速刷新, 默认是EXCLUDING NEW VALUES, 这时如需要当前值, Oracle需要到表中去查询. 2, 汇总型的MIVEW的刷新, Oracle支持分组函数及一部份的分析函数的增量刷新. 将count(*)加到MVIEW的SQL, 如果有SUM(*)和COUNT(*)存在就可以实现增量刷新. 3, JOIN类型的MVIEW的刷新 在Join的MVIEW时将基表的ROWID都加到MVIEW中, 如TA(A,B)和TB(C,D), 则MVIEW时可以写为, SELECT TA_ROWID, TB_ROWID, < 4, 复杂类型的MVIEW的刷新 可以考虑转换成级联的MVIEW, 如先建JOIN的MVIEW, 再建SUMMARY的MVIEW 5, 分区时的MVIEW的刷新 用DBMS_(表名)来获得分区ID, 并加到MIVEW的SELECT列表中, 这样可以实现分区表的增量刷新, 请自已做实验来测试. 关于这部份的解释, 在<>这一个文档中有很详细的解绍, 我也正在看, 看完后会更新的. 对于一个给定的SQL, 和现有的MVIEW, 这个SQL可能被Rewrite, 也可能不能被Rewrite, 如何找出这其中的原因呢, 靠经验可以解决一些问题, 不过所花的时间就要长一点了. 其实在Oracle中提供了一个函数可以用于解释为什么某个SQL不能被重写, 这个过程位于dbms_mview这个包中, 过程名为explain_rewrite, 有了这个工具, 可以快速地找出为什么不能被重写, 要使用这个过程, 你需要事先创建一个表REWRITE_TABLE, 可以通过@?/rdbms/admin/来创建. CREATE TABLE REWRITE_TABLE ( statement_id VARCHAR2(30), -- id for the query mv_owner VARCHAR2(30), -- owner of the MV mv_name VARCHAR2(30), -- name of the MV sequence INTEGER, -- sequence no of the error msg query VARCHAR2(2000),-- user query message VARCHAR2(512), -- EXPLAIN_REWRITE error msg pass VARCHAR2(3), -- rewrite pass no flags INTEGER, -- associated flags reserved1 INTEGER, -- currently not used reserved2 VARCHAR2(10) -- currently not used ) 有了这个表后, 就可以调用dbms_n_rewrite来解决问题了: PROCEDURE EXPLAIN_REWRITE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- QUERY VARCHAR2 IN MV VARCHAR2 IN DEFAULT STATEMENT_ID VARCHAR2 IN DEFAULT 下面我们来看一个例子, 先创建所需的表: create table test_rewrite as select * from dba_objects; alter table test_rewrite modify owner not null; create materialized view mv_test_rewrite enable query rewrite as select owner,count(*) cntrow, count(object_id) cntowner, sum(object_id) sumobjid, sum(data_object_id) sumobjd FROM TEST_REWRITE group by owner; analyze table test_rewrite compute statistics; analyze table mv_test_rewrite compute statistics; 然后我们来试验几种不同情况下dbms_n_rewrite的输出情况: 1, 未设置query_rewrite_enable为true时 SQL> alter session set query_rewrite_enabled=false; Session altered. SQL> exec dbms_n_rewrite('select avg(object_id) from test_rewrite where owner=:1 group by owner'); PL/SQL procedure successfully completed. SQL> select MESSAGE from rewrite_table; MESSAGE ------------------------------------------------------------ QSM-01001: query rewrite not enabled 2, 试测一个不能query rewrite的SQL语句 SQL> exec dbms_n_rewrite('select avg(data_object_id) from test_rewrite where owner=:1 group by owner'); PL/SQL procedure successfully completed. SQL> select MESSAGE from rewrite_table; MESSAGE ------------------------------------------------------------ QSM-01067: materialized view, MV_TEST_REWRITE, cannot support the query measure, AVG 3, 再来测试一个可以rewrite的SQL语句 SQL> exec dbms_n_rewrite('select avg(object_id) from test_rewrite where owner=:1 group by owner'); PL/SQL procedure successfully completed. SQL> select MESSAGE from rewrite_table; MESSAGE ------------------------------------------------------------ QSM-01033: query rewritten with materialized view, MV_TEST_REWRITE 从上面的例子中, 我们可以从查询出来的消息中发现很有用的信息, 在做上述实验之前, 我都运行了delete rewrite_table语句. MVIEW的刷新也是一个比较难的话题, 尤其是遇到比较复杂的情况下, 如何实现增量刷新, 为什么现在的MVIEW不能实现增量刷新, 一直是我当初在实施MVIEW时遇到的最大问题, 在Oracle中也提供了一个过程可用于分析MVIEW, 这个过程在DBMS_MVIEW这个包中, 过程名为EXPLAIN_MVIEW. 对这个过程有所了解可以帮助你更好地实现MVIEW的应用. 在输出的信息中包括了是否可以实现增量刷新, 同时也列出了这个实体化视图上支持什么样的查询重写(文字匹配, 或一般函义上的重写). 要使用这个功能, 需要建一个名称为MV_CAPABILITIES_TABLE的表, 可以调用@?/rdbms/admin/来创建. 这个表的表结构如下: CREATE TABLE MV_CAPABILITIES_TABLE (STATEMENT_ID VARCHAR(30), -- Client-supplied unique statement identifier MVOWNER VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW MVNAME VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW CAPABILITY_NAME VARCHAR(30), -- of the particular -- capability: -- REWRITE -- Can do at least full text match -- rewrite -- REWRITE_PARTIAL_TEXT_MATCH -- Can do at leat full and partial -- text match rewrite -- REWRITE_GENERAL -- Can of rewrite -- REFRESH -- Can do at least complete refresh -- REFRESH_FROM_LOG_AFTER_INSERT -- Can do fast refresh from an mv log -- or change capture table at least -- when update operations are -- restricted to INSERT A descriptive name do all forms -- REFRESH_FROM_LOG_AFTER_ANY -- can do fast refresh from an mv log -- or change capture table after any -- combination of updates -- PCT -- Can do Enhanced Update Tracking on -- the table named in the RELATED_NAME -- column. EUT is needed for fast -- refresh after partitioned -- maintenance operations on the table -- named in the RELATED_NAME column -- and to do non-stale tolerated &n bsp; -- rewrite when the mv is partially -- stale with respect to the table -- named in the RELATED_NAME column. -- EUT can also sometimes enable fast -- refresh of updates to the table -- named in the RELATED_NAME column -- when fast refresh from an mv log -- or change capture table is not -- possilbe. POSSIBLE CHARACTER(1), -- T = capability is possible -- F = capability is not possible RELATED_TEXT VARCHAR(2000),-- , alias name, etc. -- related to this message. The -- specific meaning of this column -- depends on the MSGNO column. See -- the documentation for -- DBMS_N_MVIEW() for details RELATED_NUM NUMBER, -- When there is a numeric value -- associated with a row, it goes here. -- The specific meaning of this column -- depends on the MSGNO column. See -- the documentation for -- DBMS_N_MVIEW() for details MSGNO INTEGER, -- When available, QSM message # -- explaining why not possible or more -- details when enabled. MSGTXT VARCHAR(2000),-- Text associated with MSGNO. SEQ NUMBER); -- Useful in ORDER BY clause when -- selecting from this table. 大家应当好好看一下CAPABILITY_NAME这个列上的说明部份, 就可以对这个过程的功能有所了解了, 下面来看一下用法 PROCEDURE EXPLAIN_MVIEW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- MV VARCHAR2 IN STMT_ID VARCHAR2 IN DEFAULT PROCEDURE EXPLAIN_MVIEW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- MV VARCHAR2 IN MSG_ARRAY EXPLAINMVARRAYTYPE IN/OUT 其中第一个参数可以是现有的MVIEW的名称, 也可以是将要创建的MIVEW的SQL语句部份. SQL> exec dbms_n_mview('_TEST_REWRITE'); PL/SQL procedure successfully completed. SQL> select seq,CAPABILITY_NAME,POSSIBLE from MV_CAPABILITIES_TABLE order by seq; 1 PCT N 1002 REFRESH_COMPLETE Y 2003 REFRESH_FAST N 3004 REWRITE Y 4005 PCT_TABLE N 5006 REFRESH_FAST_AFTER_INSERT N 6007 REFRESH_FAST_AFTER_ONETAB_DML N 7008 REFRESH_FAST_AFTER_ANY_DML N 8009 REFRESH_FAST_PCT N 9010 REWRITE_FULL_TEXT_MATCH Y 10011 REWRITE_PARTIAL_TEXT_MATCH Y 11012 REWRITE_GENERAL Y 12013 REWRITE_PCT N 13 rows selected. SQL> select seq, msgtxt from MV_CAPABILITIES_TABLE order by seq; 1 1002 2003 3004 4005 relation is not a partitioned table 5006 the detail table does not have a materialized view log 6007 see the reason why REFRESH_FAST_AFTER_INSERT is disabled 7008 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled 8009 PCT is not possible on any of the detail tables in the mater ialized view 9010 10011 11012 12013 general rewrite is not possible and PCT is not possible on a ny of the detail tables 13 rows selected. 现在大家知道了这个功能, 就当去试试.
发布者：admin，转转请注明出处：http://www.yc00.com/news/1705723516a1419375.html
评论列表（0条）