Oracle的物化檢視提供了強大的功能，可以用於預先計算並儲存表連線或聚集等耗時較多的操作的結果，這樣，在執行查詢時，就可以避免進行這些耗時的操作，而從快速的得到結果。關於物化檢視相關的說明參考：

在我們建立物化檢視失敗時，Oracle給的錯誤資訊過於簡單，不能幫助我們定位具體的問題，Oracle 為此提供了dbms_mview.explain_mview過程幫助我們快速定位問題的原因。

一.EXPLAIN_MVIEW 說明

使用explain_mview過程先要建立mv_capabilities_table表，建表的腳步是$oracle_home/rdbms/admin/utlxmv.sql。（explain_mview過程是兩個過程的過載，一個輸出到mv_capabilities_table表，另一個以pl/sql的varray格式輸出）。

SQL>@?/rdbms/admin/utlxmv.sql

Table created.

檢視utlxmv.sql 指令碼，可以看到mv_capabilities_tables 表的定義：

CREATETABLEMV_CAPABILITIES_TABLE

(STATEMENT_ID VARCHAR(30), --Client-supplied unique statement identifier

MVOWNER VARCHAR(30), -- NULLfor SELECT based EXPLAIN_MVIEW

MVNAME VARCHAR(30), -- NULLfor SELECT based EXPLAIN_MVIEW

CAPABILITY_NAME VARCHAR(30), -- A descriptivename 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 = capabilityis 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

-- thedocumentation 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 thiscolumn

--depends on the MSGNO column. See

-- thedocumentation 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 ORDERBY clause when

--selecting from this table.

二．Explain_mview 使用示例

dbms_mview.explain_mview能分析三種不同的物化檢視程式碼，分別是：

1.定義的查詢

2.一個create materialized view的語句

3.一個存在的物化檢視

因為物化檢視在語法有一定的限制，所以在建立物化檢視之前我們可以先使用explain_mview 過程來驗證一下語法上的問題。如：

SQL> execdbms_mview.explain_mview('select * from dave');

PL/SQL proceduresuccessfully completed.

--然後檢視mv_capabilities_table 表：

SQL> desc mv_capabilities_table

Name

---------------------------------

STATEMENT_ID

MVOWNER

MVNAME

CAPABILITY_NAME

POSSIBLE

RELATED_TEXT

RELATED_NUM

MSGNO

MSGTXT

SEQ

SQL> selectcapability_name,possible,msgtxt from mv_capabilities_table;

CAPABILITY_NAME P MSGTXT

------------------------------ ------------------------------------------------------------------------------------

PCT N

REFRESH_COMPLETE N no primary key constraint inthe master table

--這裡提示我們主表沒有主鍵，

REFRESH_FAST N

REWRITE N

PCT_TABLE N Oracle error: seeRELATED_NUM and RELATED_TEXT for details

REFRESH_FAST_AFTER_INSERT N the detail table does not have amaterialized view log

REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERTis disabled

REFRESH_FAST_AFTER_ANY_DML N see the reason whyREFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT N PCT is not possible on any ofthe detail tables in the materialized view

REWRITE_FULL_TEXT_MATCH NOracle error: see RELATED_NUM and RELATED_TEXT for details

REWRITE_PARTIAL_TEXT_MATCH N materialized view cannot support anytype of query rewrite

REWRITE_GENERAL N materialized view cannotsupport any type of query rewrite

REWRITE_PCT N general rewrite is notpossible or PCT is not possible on any of the detail tables

PCT_TABLE_REWRITE N Oracle error: see RELATED_NUMand RELATED_TEXT for details

--這裡會顯示所有不符合的地方。

SQL> create materialized view mv_daverefresh fast on demand as select * from dave;

create materialized view mv_dave refreshfast on demand as select * from dave

*

ERROR at line 1:

ORA-12014: 表 'DAVE' 不包含主鍵約束條件

--如果我們直接使用上面的語句，就會出現沒有主鍵的錯誤。

我們建立一個新的物化檢視，然後使用explain_mview 來驗證：

SQL> create table anqing as select *from all_users;

Table created.

SQL> create materialized view mv_daverefresh force on demand as select * from anqing;

Materialized view created.

SQL> exec dbms_mview.explain_mview('mv_dave');

PL/SQL procedure successfully completed.

SQL> selectcapability_name,possible,msgtxt from mv_capabilities_table wheremvname='MV_DAVE';

CAPABILITY_NAME P MSGTXT

------------------------------ -----------------------------------------------------------------------------------

PCT N

REFRESH_COMPLETE Y

REFRESH_FAST N

REWRITE N

PCT_TABLE N Oracle error: seeRELATED_NUM and RELATED_TEXT for details

REFRESH_FAST_AFTER_INSERT N does not meet the requirements of aprimary key mv

REFRESH_FAST_AFTER_ONETAB_DML N see the reason whyREFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML N see the reason whyREFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT N PCT is not possible on any ofthe detail tables in the materialized view

REWRITE_FULL_TEXT_MATCH N Oracle error: see RELATED_NUM andRELATED_TEXT for details

REWRITE_FULL_TEXT_MATCH N query rewrite is disabled on thematerialized view

REWRITE_PARTIAL_TEXT_MATCH N materialized view cannot support anytype of query rewrite

REWRITE_PARTIAL_TEXT_MATCH N query rewrite is disabled on thematerialized view

REWRITE_GENERAL N materialized view cannotsupport any type of query rewrite

REWRITE_GENERAL N query rewrite is disabled onthe materialized view

REWRITE_PCT N general rewrite is not possible or PCT isnot possible on any of the detail tables

PCT_TABLE_REWRITE N Oracle error: see RELATED_NUMand RELATED_TEXT for details

17 rows selected.

--這裡就ok了。

-------------------------------------------------------------------------------------------------------

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email:

Blog:

Weibo:

Twitter:

Facebook:

Linkedin:

Oracle 物化檢視詳細錯誤描述檢視方法

一.EXPLAIN_MVIEW 說明

二．Explain_mview 使用示例

相關文章

Oracle 物化檢視 詳細錯誤描述 檢視方法

一.EXPLAIN_MVIEW 說明

二．Explain_mview 使用示例

相關文章

Oracle 物化檢視詳細錯誤描述檢視方法