* MV with this particular UDF is fast refreshable. The UDF is deterministic in nature.
  Even though it has not been declared to be one.
* Obviously not all UDF can make an MV fast refrsahble.
* The question now is what kind of UDF can be used with an MV and make it
  fast refreshable?


SQL> drop table test3_;

Table dropped.

SQL> create table test3_ as select * from test3;

Table created.

SQL> alter table test3_ add constraint pk_test3_ primary key(c1);

Table altered.

SQL> select * from test3_;

a           1                                                                   
b           2                                                                   
c           3                                                                   
e           4                                                                   
f           5                                                                   
o           9                                                                   
q           5                                                                   
p           1                                                                   

8 rows selected.

SQL> create or replace function test_func1_(x number) return number as
  2  inc_10 number;
  3  begin
  4  inc_10:=x+10;
  5  return inc_10;
  6  end;
  7  /

Function created.

SQL> create materialized view log on test3_;

Materialized view log created.

SQL> -- Can not have QR here
SQL> create materialized view test_fun_mv
  2  refresh fast
  3  enable query rewrite
  4  as select test_func1_(c2) from test3_;
create materialized view test_fun_mv
*
ERROR at line 1:
ORA-30357: this PL/SQL function cannot be supported for query rewrite 


SQL> 
SQL> create materialized view test_fun_mv
  2  refresh fast
  3  as select c1, test_func1_(c2) from test3_;

Materialized view created.

SQL> insert into test3_ values('i',2);

1 row created.

SQL> select count(1) from mlog$_test3_;

         1                                                                      

SQL> exec dbms_mview.refresh('test_fun_mv','f');

PL/SQL procedure successfully completed.

SQL> select * from test_fun_mv;

a               11                                                              
b               12                                                              
c               13                                                              
e               14                                                              
f               15                                                              
o               19                                                              
q               15                                                              
p               11                                                              
i               12                                                              

9 rows selected.

SQL> select last_refresh_type from user_mviews where mview_name='TEST_FUN_MV';

FAST                                                                            

SQL> select count(1) from mlog$_test3_;

         0                                                                      

SQL> truncate table mv_capabilities_table;

Table truncated.

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

PL/SQL procedure successfully completed.

SQL> set head off
SQL> select CAPABILITY_NAME,possible,MSGTXT from mv_capabilities_table;

PCT                            N                                                
                                                                                
                                                                                
REFRESH_COMPLETE               Y                                                
                                                                                
                                                                                
REFRESH_FAST                   Y                                                
                                                                                
                                                                                
REWRITE                        N                                                
                                                                                
                                                                                
PCT_TABLE                      N                                                

Oracle error: see RELATED_NUM and RELATED_TEXT for details                      
                                                                                
PCT_TABLE                      N                                                
relation is not a partitioned table                                             
                                                                                
REFRESH_FAST_AFTER_INSERT      Y                                                
                                                                                
                                                                                
REFRESH_FAST_AFTER_ONETAB_DML  Y                                                
                                                                                
                                                                                
REFRESH_FAST_AFTER_ANY_DML     Y                                                
                                                                                

                                                                                
REFRESH_FAST_PCT               N                                                
PCT is not possible on any of the detail tables in the materialized view        
                                                                                
REWRITE_FULL_TEXT_MATCH        N                                                
Oracle error: see RELATED_NUM and RELATED_TEXT for details                      
                                                                                
REWRITE_FULL_TEXT_MATCH        N                                                
query rewrite is disabled on the materialized view                              
                                                                                
REWRITE_PARTIAL_TEXT_MATCH     N                                                
materialized view cannot support any type of query rewrite                      
                                                                                

REWRITE_PARTIAL_TEXT_MATCH     N                                                
query rewrite is disabled on the materialized view                              
                                                                                
REWRITE_GENERAL                N                                                
materialized view cannot support any type of query rewrite                      
                                                                                
REWRITE_GENERAL                N                                                
query rewrite is disabled on the materialized view                              
                                                                                
REWRITE_PCT                    N                                                
general rewrite is not possible and PCT is not possible on any of the detail tab
les                                                                             
                                                                                

17 rows selected.

SQL> spool off