* ORACLE will not allow you to create an MV with function, unless the
  function is declared as "deterministic"
* Even that, you still need to set the 
  query_rewrite_integrity to trusted or stale_tolerated
  for QR to kick in.
* Use ORACLE's own function does not require the above step.


SQL> create materialized view test_fun_mv
  2  enable query rewrite
  3  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> create or replace function test_func1_(x number)
  2  return number deterministic as
  3  inc_10 number;
  4  begin
  5  inc_10:=x+10;
  6  return inc_10;
  7  end;
  8  /

Function created.

SQL> create materialized view test_fun_mv
  2  enable query rewrite
  3  as select test_func1_(c2) from test3;

Materialized view created.


-- No query rewrite
SQL> set autotrace on explain
SQL> select test_func1_(c2) from test3;

TEST_FUNC1_(C2)
---------------
             11
             12
             13
             14
             15


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
   1    0   TABLE ACCESS (FULL) OF 'TEST3' (Cost=2 Card=4 Bytes=8)


SQL> analyze table test_fun_mv compute statistics;

Table analyzed.

SQL> select test_func1_(c2) from test3;

TEST_FUNC1_(C2)
---------------
             11
             12
             13
             14
             15


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
   1    0   TABLE ACCESS (FULL) OF 'TEST3' (Cost=2 Card=4 Bytes=8)


-- Still no query reqrite
SQL> alter session set query_rewrite_enabled=true;

Session altered.

SQL> select test_func1_(c2) from test3;

TEST_FUNC1_(C2)
---------------
             11
             12
             13
             14
             15


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
   1    0   TABLE ACCESS (FULL) OF 'TEST3' (Cost=2 Card=4 Bytes=8)



SQL> drop materialized view test_fun_mv;

Materialized view dropped.

-- Use ORACLE function
SQL> create materialized view test_fun_mv
  2  enable query rewrite
  3  as select length(c1) from test3;

Materialized view created.

SQL> analyze table test_fun_mv compute statistics;

Table analyzed.


-- Query rewrite kicks in
SQL> select length(c1) from test3;

LENGTH(C1)
----------
         1
         1
         1
         1
         1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=10)
   1    0   TABLE ACCESS (FULL) OF 'TEST_FUN_MV' (Cost=2 Card=5 Bytes=
          10)


SQL> show parameter integrity

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_integrity              string      enforced
SQL> alter session set  query_rewrite_integrity = trusted;

Session altered.

SQL> drop materialized view test_fun_mv;

Materialized view dropped.

SQL> create materialized view test_fun_mv
  2  enable query rewrite
  3  as select test_func1_(c2) from test3;

Materialized view created.

SQL> select test_func1_(c2) from test3;

TEST_FUNC1_(C2)
---------------
             11
             12
             13
             14
             15


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=425
          1)

   1    0   TABLE ACCESS (FULL) OF 'TEST_FUN_MV' (Cost=2 Card=327 Byte
          s=4251)

SQL> alter session set  query_rewrite_integrity = stale_tolerated;

Session altered.

SQL> select test_func1_(c2) from test3;

TEST_FUNC1_(C2)
---------------
             11
             12
             13
             14
             15


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=425
          1)

   1    0   TABLE ACCESS (FULL) OF 'TEST_FUN_MV' (Cost=2 Card=327 Byte
          s=4251)

SQL>