DBMS_MVIEW.EXPLAIN_MVIEW包的使用
DBMS_MVIEW.EXPLAIN_MVIEW能分析三种不同的物化视图代码,分别是:
1.定义的查询
2.一个CREATE MATERIALIZED VIEW的语句
3.一个存在的物化视图
步骤如下:
1、提前执行dbms_mview.explain_mview用到的表mv_capabilities_table的建表语句
SQL>@$ORACLE_HOME\RDBMS\ADMIN\utlxmv.sql;
如果未执行此语句,则有运行包的时候会报表不存在的错误。
2、先清空表中记录,便于分析信息的查看
TRUNCATE TABLE mv_capabilities_table;
3、执行包dbms_mview.explain_mview,其中,包里的参数可以是物化视图、查询语句、
CREATE物化视图语句。
begin
dbms_mview.explain_mview('SELECT a.id,b.name,a.rowid a_rowid,b.rowid b_rowid
FROM comp1 a , comp2 b WHERE a.id=b.id');
end;
/
4、查看分析结果或错误报告
select MSGTXT from mv_capabilities_table where capability_name='REFRESH_FAST_AFTER_INSERT';
或
select * from mv_capabilities_table;
Tips:
(1)、物化视图在判断是否支持刷新时不支持SQL92的标准外联接的写法,需要改成Oracle自己的(+)的方式。
(2)、对于包含外联接的物化视图的快速刷新,无法高效的利用物化视图日志,因此即使是快速刷新,
性能也可能很差。
下面使用物化视图
首先要建表$ORACLE_HOME\RDBMS\ADMIN\utlxmv.sql;
utlxmv.sql的内容如下:
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), -- A descriptive name 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 do all forms 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
-- 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
-- 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),-- Owner.table.column, alias name, etc.
-- related to this message. The
-- specific meaning of this column
-- depends on the MSGNO column. See
-- the documentation for
-- DBMS_MVIEW.EXPLAIN_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_MVIEW.EXPLAIN_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.
1.先建物化视图 。
CREATE MATERIALIZED VIEW cal_month_sales_mv
BUILD IMMEDIATE
REFRESH FORCE
ENABLE QUERY REWRITE AS
SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;
2.执行存储过程包,进行分析。
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SH.CAL_MONTH_SALES_MV');
3.执行以下语句,查看分析的结果。
SELECT capability_name, possible, SUBSTR(related_text,1,8)
AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
ORDER BY seq;
DBMS_MVIEW.EXPLAIN_MVIEW包的使用
DBMS_MVIEW.EXPLAIN_MVIEW能分析三种不同的物化视图代码,分别是:
1.定义的查询
2.一个CREATE MATERIALIZED VIEW的语句
3.一个存在的物化视图
步骤如下:
1、提前执行dbms_mview.explain_mview用到的表mv_capabilities_table的建表语句
SQL>@$ORACLE_HOME\RDBMS\ADMIN\utlxmv.sql;
如果未执行此语句,则有运行包的时候会报表不存在的错误。
2、先清空表中记录,便于分析信息的查看
TRUNCATE TABLE mv_capabilities_table;
3、执行包dbms_mview.explain_mview,其中,包里的参数可以是物化视图、查询语句、
CREATE物化视图语句。
begin
dbms_mview.explain_mview('SELECT a.id,b.name,a.rowid a_rowid,b.rowid b_rowid
FROM comp1 a , comp2 b WHERE a.id=b.id');
end;
/
4、查看分析结果或错误报告
select MSGTXT from mv_capabilities_table where capability_name='REFRESH_FAST_AFTER_INSERT';
或
select * from mv_capabilities_table;
Tips:
(1)、物化视图在判断是否支持刷新时不支持SQL92的标准外联接的写法,需要改成Oracle自己的(+)的方式。
(2)、对于包含外联接的物化视图的快速刷新,无法高效的利用物化视图日志,因此即使是快速刷新,
性能也可能很差。
下面使用物化视图
首先要建表$ORACLE_HOME\RDBMS\ADMIN\utlxmv.sql;
utlxmv.sql的内容如下:
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), -- A descriptive name 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 do all forms 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
-- 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
-- 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),-- Owner.table.column, alias name, etc.
-- related to this message. The
-- specific meaning of this column
-- depends on the MSGNO column. See
-- the documentation for
-- DBMS_MVIEW.EXPLAIN_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_MVIEW.EXPLAIN_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.
1.先建物化视图 。
CREATE MATERIALIZED VIEW cal_month_sales_mv
BUILD IMMEDIATE
REFRESH FORCE
ENABLE QUERY REWRITE AS
SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;
2.执行存储过程包,进行分析。
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SH.CAL_MONTH_SALES_MV');
3.执行以下语句,查看分析的结果。
SELECT capability_name, possible, SUBSTR(related_text,1,8)
AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
ORDER BY seq;
CAPABILITY_NAME P REL_TEXT MSGTXT
------------------------------ - ---------------- ------------------------------------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N SALES no partition key or PMARKER or join dependent expression in
PCT_TABLE N CUSTOMER relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT N SH.SALES the detail table does not have a materialized view log
REFRESH_FAST_AFTER_INSERT N SH.CUSTO the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML N SUM(AMOU SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list
CAPABILITY_NAME P REL_TEXT MSGTXT
------------------------------ - ---------------- ------------------------------------------------------------
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possible or PCT is not possible on an
PCT_TABLE_REWRITE N SALES no partition key or PMARKER in select list
PCT_TABLE_REWRITE N CUSTOMER relation is not a partitioned table
20 rows selected.
|