materialized view query rewrite and ORA-30353

materialized view query rewrite是高级优化中的一个技术，通常在修改访问路径、表连接方式，优化query optimizer 参数等后才考虑的手段。
启用物化视图的重写有两个条件QUERY_REWRITE_ENABLED=true and materialized view enable query rewrite.
在CBO对查询sql 重写后估算cast后直接查询mv

看我的实验。

conn system/oracle

create table anbob.obj as select * from dba_objects;

conn anbob/anbob

anbob@ANBOB> create table myseg as select * from user_segments;

Table created.

anbob@ANBOB>   select segment_type,sum(seg.bytes) from myseg seg
        join obj o on o.object_type=seg.segment_type and o.object_name=seg.segment_name
        where o.owner=user
        group by segment_type;


Execution Plan
----------------------------------------------------------
Plan hash value: 1267477370

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     6 |   636 |   294   (1)| 00:00:04 |
|   1 |  HASH GROUP BY      |       |     6 |   636 |   294   (1)| 00:00:04 |
|*  2 |   HASH JOIN         |       |     6 |   636 |   293   (1)| 00:00:04 |
|   3 |    TABLE ACCESS FULL| MYSEG |     6 |   396 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| OBJ   |  2417 | 96680 |   290   (1)| 00:00:04 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("O"."OBJECT_TYPE"="SEG"."SEGMENT_TYPE" AND
              "O"."OBJECT_NAME"="SEG"."SEGMENT_NAME")
   4 - filter("O"."OWNER"=USER@!)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
       1054  consistent gets
       1033  physical reads
          0  redo size
        503  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

anbob@ANBOB> create   materialized view myobj_mv
  2  as
  3  select segment_type,sum(seg.bytes) from myseg seg
        join obj o on o.object_type=seg.segment_type and o.object_name=seg.segment_name
  5          where o.owner=user
  6          group by segment_type;

Materialized view created.

anbob@ANBOB> select * from myobj_mv;
Execution Plan
----------------------------------------------------------
Plan hash value: 1295014342

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    24 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW ACCESS FULL| MYOBJ_MV |     1 |    24 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        507  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

note: 访问路径是MAT_VIEW ACCESS FULL，这是从10g后才有的，与之前的TABLE ACCESS FUL作用是一样的，只是表明此处用到了物化视图。


QUERY_REWRITE_ENABLED
Default value If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then
true
If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0 or lower, then
false
Modifiable ALTER SESSION, ALTER SYSTEM
Oracle RAC Multiple instances can have different values

QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally
for the database.
Values:
■ false
Oracle does not use rewrite.
■ true
Oracle costs the query with rewrite and without rewrite and chooses the method
with the lower cost.
■ force
Oracle always uses rewrite and does not evaluate the cost before doing so. Use
force when you know that the query will always benefit from rewrite and when
reduction in compile time is important.
To take advantage of query rewrite for a particular materialized view, you must enable
query rewrite for that materialized view, and you must enable cost-based
optimization.

anbob@ANBOB> alter materialized view myobj_mv enable query rewrite;
alter materialized view myobj_mv enable query rewrite
                                                    *
ERROR at line 1:
ORA-30353: expression not supported for query rewrite


anbob@ANBOB> ! oerr ora 30353
30353, 00000, "expression not supported for query rewrite"
// *Cause: The SELECT clause referenced UID, USER, ROWNUM, SYSDATE,
//         CURRENT_TIMESTAMP, MAXVALUE, a sequence number, a bind variable, 
//         correlation variable, a set result, a  trigger return variable, a 
//         parallel table queue column, collection iterator, a non-deterministic
//         date format token RR, etc.
// *Action: Remove the offending expression or disable the REWRITE option on
//          the materialized view.

anbob@ANBOB> create   materialized view myobj_mv
  2      as
  3     select segment_type,sum(seg.bytes) from myseg seg
      join obj o on o.object_type=seg.segment_type and o.object_name=seg.segment_name
  5              where o.owner='ANBOB'
  6              group by segment_type;

Materialized view created.

anbob@ANBOB> alter materialized view myobj_mv enable query rewrite;

Materialized view altered.

anbob@ANBOB> select segment_type,sum(seg.bytes) from myseg seg
  2          join obj o on o.object_type=seg.segment_type and o.object_name=seg.segment_name
  3          where o.owner=user
  4          group by segment_type;

SEGMENT_TYPE       SUM(SEG.BYTES)
------------------ --------------
TABLE                     9502720


Execution Plan
----------------------------------------------------------
Plan hash value: 1267477370

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     6 |   636 |   294   (1)| 00:00:04 |
|   1 |  HASH GROUP BY      |       |     6 |   636 |   294   (1)| 00:00:04 |
|*  2 |   HASH JOIN         |       |     6 |   636 |   293   (1)| 00:00:04 |
|   3 |    TABLE ACCESS FULL| MYSEG |     6 |   396 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| OBJ   |  2417 | 96680 |   290   (1)| 00:00:04 |
-----------------------------------------------------------------------------

note:此处还是USER

anbob@ANBOB>   select segment_type,sum(seg.bytes) from myseg seg
        join obj o on o.object_type=seg.segment_type and o.object_name=seg.segment_name
  3          where o.owner='ANBOB'
  4          group by segment_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 3509541945

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    24 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MYOBJ_MV |     1 |    24 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        503  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

note:
访问路径变成了MAT_VIEW REWRITE ACCESS FULL，启用了MV的 QUERY REWRITE优化特性，没有走原来的全用扫描再HS JOIN,而是直接扫描了物化视图，前提是启用了CBO，而且用MV COST必较小的情况下。这是发生在全文匹配，也可以部分匹配，甚至是一般匹配，如果想查为什么没有使用mv查询重写可以使用dbms_mview.explain_rewrite查询原因。

如果在一个应用的查询页面查询本周或昨天一个变化不频繁的值时，而且应用程序的代码又不想改动或不能改动时，用这个方法优化是不是很巧妙呢？