dbms_mview系列(二)

dbms_mview系列(二)
-插入一些資料到測試表
SQL> insert into t_mv select level+300,level+3 from dual connect by level<=1000;
 
1000 rows inserted
--收集測試表
SQL> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'mv_t',cascade => true,estimate_percent => 100);
 
PL/SQL procedure successfully completed
--估算物化檢視大小,仍是1
SQL>
SQL> declare
  2  v_rows number;
  3  v_bytes number;
  4  begin
  5  dbms_mview.estimate_mview_size(stmt_id => '',select_clause => 'select a from t_mv',num_rows =>v_rows,num_bytes => v_bytes);
  6  dbms_output.put_line(v_rows||'------'||v_bytes);
  7  end;
  8  /
 
-1-------1

--再次收集測試表
SQL> exec dbms_stats.gather_table_stats('scott','t_mv',cascade=>true);
 
PL/SQL procedure successfully completed
--結果為1的原因是statement_id未指定值,隨機指定一個值,只要不為空即可,這下正確了吧
SQL> declare
  2  v_rows number;
  3  v_bytes number;
  4  begin
  5  dbms_mview.estimate_mview_size('s1','select a from t_mv',v_rows, v_bytes);
  6  dbms_output.put_line(v_rows||'------'||v_bytes);
  7  end;
  8  /
 
1007------22154
 
PL/SQL procedure successfully completed

EXPLAIN_MVIEW Procedure
explain_mview過程可以獲知物化檢視是否增量重新整理,是何種查詢重寫;這個過程使用很簡單,僅傳遞物化檢視名稱,也可以
傳遞select子句或create materialized view語句.執行後,物化檢視會被反板然後把結果存入名為mv_capabilities_table
表,或儲存到一個名為msg_array的array型別中.
This procedure enables you to learn what is possible with a materialized view or potential materialized view.
For example, you can determine if a materialized view is fast refreshable and what types of query rewrite
you can perform. with a particular materialized view.
 
Using this procedure is straightforward. You simply call DBMS_MVIEW.EXPLAIN_MVIEW, passing in as parameters
the schema and materialized view name for an existing materialized view. Alternatively, you can specify the
SELECT string or CREATE MATERIALIZED VIEW statement for a potential materialized view. The materialized view
or potential materialized view is then analyzed and the results are written into either a table called
MV_CAPABILITIES_TABLE, which is the default, or to an array called MSG_ARRAY.
--此過程2種過載形式
The procedure is overloaded:
 ?
The first version is for explaining an existing or potential materialized view with output to MV_CAPABILITIES_TABLE.
 
?
The second version is for explaining an existing or potential materialized view with output to a VARRAY.
--語法
Syntax
 DBMS_MVIEW.EXPLAIN_MVIEW (
   mv            IN VARCHAR2,
   statement_id  IN VARCHAR2:= NULL);

DBMS_MVIEW.EXPLAIN_MVIEW (
   mv          IN VARCHAR2,
   msg_array   OUT SYS.ExplainMVArrayType);
操作說明
Usage Notes
如使用第一種過載形式,使用此過程前先建立MV_CAPABILITIES_TABLE,此建立表的指令碼在admin/utlxmv.sql目錄下
(D:\oracle11g_64bit\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxmv.sql).而第二種過載
形式不用建立此表
You must run the utlxmv.sql script. to create MV_CAPABILITIES_TABLE in the current schema prior to
 calling EXPLAIN_MVIEW except when you direct output to a VARRAY. The script. is found in the ADMIN directory.
 
SQL> exec dbms_mview.explain_mview('mv_t');
 
PL/SQL procedure successfully completed
--此表已經有資料了,此表顯示物化檢視各功能的開啟情況
SQL> select * from mv_capabilities_table;
 
STATEMENT_ID                   MVOWNER                        MVNAME                         CAPABILITY_NAME                POSSIBLE RELATED_TEXT                                                                     RELATED_NUM                                   MSGNO MSGTXT                                                                                  SEQ
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- -------------------------------------------------------------------------------- ----------- --------------------------------------- -------------------------------------------------------------------------------- ----------
                               SCOTT                          MV_T                           PCT                            N                                                                                                                                                                                                                                       1
                               SCOTT                          MV_T                           REFRESH_COMPLETE               Y                                                                                                                                                                                                                                    1002
                               SCOTT                          MV_T                           REFRESH_FAST                   Y                                                                                                                                                                                                                                    2003
                               SCOTT                          MV_T                           REWRITE                        N                                                                                                                                                                                                                                    3004
                               SCOTT                          MV_T                           PCT_TABLE                      N        T_MV                                                                                      14                                    2068 relation is not a partitioned table                                                    4005
                               SCOTT                          MV_T                           REFRESH_FAST_AFTER_INSERT      Y                                                                                                                                                                                                                                    5006
                               SCOTT                          MV_T                           REFRESH_FAST_AFTER_ONETAB_DML  Y                                                                                                                                                                                                                                    6007
                               SCOTT                          MV_T                           REFRESH_FAST_AFTER_ANY_DML     Y                                                                                                                                                                                                                                    7008
                               SCOTT                          MV_T                           REFRESH_FAST_PCT               N                                                                                                                                        2157 PCT is not possible on any of the detail tables in the materialized view               8009
                               SCOTT                          MV_T                           REWRITE_FULL_TEXT_MATCH        N                                                                                                                                        2159 query rewrite is disabled on the materialized view                                     9010
                               SCOTT                          MV_T                           REWRITE_PARTIAL_TEXT_MATCH     N                                                                                                                                        2159 query rewrite is disabled on the materialized view                                    10011
                               SCOTT                          MV_T                           REWRITE_GENERAL                N                                                                                                                                        2159 query rewrite is disabled on the materialized view                                    11012
                               SCOTT                          MV_T                           REWRITE_PCT                    N                                                                                                                                        2158 general rewrite is not possible or PCT is not possible on any of the detail tabl      12013
                               SCOTT                          MV_T                           PCT_TABLE_REWRITE              N        T_MV                                                                                      14                                    2068 relation is not a partitioned table                                                   13014
 
14 rows selected