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