物化视图PCT特性（2）

如果含有group by 子句，在select和 group by子句中需要包含 分区键或分区标记(partition marker)或rowids,以支持PCT特性。
oracle内部会将rowids转换为分区标记(partition marker)。

以分区标记(partition marker)为例测试：

SQL> CREATE TABLE a_list 2 (id int, value int, type char(1)) 3 PARTITION BY LIST (type) 4 (PARTITION pa 5 VALUES ('a'), 6 PARTITION pb 7 VALUES ('b'), 8 PARTITION pc 9 VALUES ('c')); 表已创建。 SQL> insert into a_list 2 SELECT LEVEL, level+1,'a' FROM dual CONNECT BY LEVEL <= 10 3 UNION ALL 4 SELECT 10+LEVEL,level+100,'b' FROM dual CONNECT BY LEVEL <= 10 5 UNION ALL 6 SELECT 20+LEVEL,level+1000,'c' FROM dual CONNECT BY LEVEL <= 10; 已创建30行。 SQL> CREATE TABLE b 2 (id int,name varchar(20) ); 表已创建。 SQL> SQL> insert into b SELECT LEVEL,'D1'FROM dual CONNECT BY LEVEL <= 5 ; 已创建5行。 SQL> insert into b SELECT LEVEL+5,'D2'FROM dual CONNECT BY LEVEL <= 5 ; 已创建5行。 SQL> insert into b SELECT LEVEL+10,'D3'FROM dual CONNECT BY LEVEL <= 5 ; 已创建5行。 SQL> insert into b SELECT LEVEL+15,'D4'FROM dual CONNECT BY LEVEL <= 5 ; 已创建5行。 SQL> insert into b SELECT LEVEL+20,'D5'FROM dual CONNECT BY LEVEL <= 5 ; 已创建5行。 SQL> commit; 提交完成。 SQL> CREATE MATERIALIZED VIEW pmarker_mv 2 BUILD IMMEDIATE 3 REFRESH FORCE ON DEMAND 4 ENABLE QUERY REWRITE AS 5 select t2.name,DBMS_MVIEW.PMARKER(t1.rowid) pmarker, sum(t1.value) sum_val ,count(*) cnt from a_list t1,b t2 6 where t1.id=t2.id 7 group by t2.name,DBMS_MVIEW.PMARKER(t1.rowid); 实体化视图已创建。 SQL> set autot on SQL> select t2.name, sum(t1.value) sum_val from a_list t1,b t2 2 where t1.id=t2.id 3 group by t2.name; NAME SUM_VAL -------------------- ---------- D1 20 D5 5015 D3 515 D2 45 D4 540 执行计划 ---------------------------------------------------------- Plan hash value: 2609783564 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 125 | 4 (25)| 00:00:01 | | 1 | HASH GROUP BY | | 5 | 125 | 4 (25)| 00:00:01 | | 2 | MAT_VIEW REWRITE ACCESS FULL| PMARKER_MV | 5 | 125 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 13 recursive calls 0 db block gets 39 consistent gets 0 physical reads 0 redo size 454 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 5 rows processed SQL> select t2.name, sum(t1.value) sum_val from a_list t1,b t2 2 where t1.id=t2.id and t1.type='a' 3 group by t2.name; NAME SUM_VAL -------------------- ---------- D1 20 D2 45 执行计划 ---------------------------------------------------------- Plan hash value: 3809246422 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 540 | 8 (25)| 00:00:01 | | | | 1 | HASH GROUP BY | | 10 | 540 | 8 (25)| 00:00:01 | | | |* 2 | HASH JOIN | | 10 | 540 | 7 (15)| 00:00:01 | | | | 3 | PARTITION LIST SINGLE| | 10 | 290 | 3 (0)| 00:00:01 | 1 | 1 | | 4 | TABLE ACCESS FULL | A_LIST | 10 | 290 | 3 (0)| 00:00:01 | 1 | 1 | | 5 | TABLE ACCESS FULL | B | 25 | 625 | 3 (0)| 00:00:01 | | | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."ID"="T2"."ID") Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 8 recursive calls 0 db block gets 30 consistent gets 0 physical reads 0 redo size 418 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select /*+ REWRITE(pmarker_mv) */ t2.name, sum(t1.value) from a_list t1,b t2 2 where t1.id=t2.id and t1.type='a' 3 group by t2.name ; NAME SUM(T1.VALUE) -------------------- ------------- D1 20 D2 45 执行计划 ---------------------------------------------------------- Plan hash value: 3809246422 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 540 | 8 (25)| 00:00:01 | | | | 1 | HASH GROUP BY | | 10 | 540 | 8 (25)| 00:00:01 | | | |* 2 | HASH JOIN | | 10 | 540 | 7 (15)| 00:00:01 | | | | 3 | PARTITION LIST SINGLE| | 10 | 290 | 3 (0)| 00:00:01 | 1 | 1 | | 4 | TABLE ACCESS FULL | A_LIST | 10 | 290 | 3 (0)| 00:00:01 | 1 | 1 | | 5 | TABLE ACCESS FULL | B | 25 | 625 | 3 (0)| 00:00:01 | | | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."ID"="T2"."ID") Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 7 recursive calls 0 db block gets 30 consistent gets 0 physical reads 0 redo size 424 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select * from pmarker_mv; NAME PMARKER SUM_VAL CNT -------------------- ---------- ---------- ---------- D2 51855 45 5 D4 51856 540 5 D5 51857 5015 5 D3 51856 515 5 D1 51855 20 5 执行计划 ---------------------------------------------------------- Plan hash value: 4122272631 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 255 | 3 (0)| 00:00:01 | | 1 | MAT_VIEW ACCESS FULL| PMARKER_MV | 5 | 255 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 5 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 584 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed SQL> SELECT name, SUM(sum_val) FROM pmarker_mv where pmarker=51855 group by name; NAME SUM(SUM_VAL) -------------------- ------------ D1 20 D2 45 执行计划 ---------------------------------------------------------- Plan hash value: 2288911813 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2 | 76 | 4 (25)| 00:00:01 | | 1 | HASH GROUP BY | | 2 | 76 | 4 (25)| 00:00:01 | |* 2 | MAT_VIEW ACCESS FULL| PMARKER_MV | 2 | 76 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("PMARKER"=51855) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 5 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 423 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select /*+ REWRITE(PMARKER_MV) */ t2.name, sum(t1.value) from a_list t1,b t2 2 where t1.id=t2.id and t1.type='a' 3 group by t2.name ; NAME SUM(T1.VALUE) -------------------- ------------- D1 20 D2 45 执行计划 ---------------------------------------------------------- Plan hash value: 3809246422 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 540 | 8 (25)| 00:00:01 | | | | 1 | HASH GROUP BY | | 10 | 540 | 8 (25)| 00:00:01 | | | |* 2 | HASH JOIN | | 10 | 540 | 7 (15)| 00:00:01 | | | | 3 | PARTITION LIST SINGLE| | 10 | 290 | 3 (0)| 00:00:01 | 1 | 1 | | 4 | TABLE ACCESS FULL | A_LIST | 10 | 290 | 3 (0)| 00:00:01 | 1 | 1 | | 5 | TABLE ACCESS FULL | B | 25 | 625 | 3 (0)| 00:00:01 | | | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."ID"="T2"."ID") Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 7 recursive calls 0 db block gets 30 consistent gets 0 physical reads 0 redo size 424 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 2 rows processed
select /*+ REWRITE(PMARKER_MV) */ t2.name, sum(t1.value) from a_list t1,b t2
where t1.id=t2.id and t1.type='a'
group by t2.name ;
这句访问的是基表，按道理应该访问的是物化视图pmarker_mv，即进行查询重写为：

SELECT name, SUM(sum_val) FROM pmarker_mv where pmarker=51855 group by name;

以上是不成立的，因为物化视图pmarker_mv中没有type字段的信息，所以要回表（a_list）而不能实施查询重写。

/** 查看物化视图解释信息，可以看出是可以进行查询重写的，但这并不代表以上的查询可以应用该物化视图进行查询重写。 */ SQL> truncate table mv_capabilities_table; 表被截断。 SQL> exec dbms_mview.explain_mview('PMARKER_MV'); PL/SQL 过程已成功完成。 SQL> col capability_name for a20 SQL> col possible for a5 SQL> col related_text for a10 SQL> col msgtxt for a50 SQL> select capability_name, possible, related_text, msgtxt 2 from mv_capabilities_table where capability_name like '%PCT%'; CAPABILITY_NAME POSSI RELATED_TE MSGTXT -------------------- ----- ---------- -------------------------------------------------- PCT Y PCT_TABLE Y A_LIST PCT_TABLE N B relation is not a partitioned table REFRESH_FAST_PCT Y REWRITE_PCT Y PCT_TABLE_REWRITE Y A_LIST PCT_TABLE_REWRITE N B relation is not a partitioned table 已选择7行。 SQL> select capability_name, possible, related_text, msgtxt 2 from mv_capabilities_table where capability_name like '%REWRITE%'; CAPABILITY_NAME POSSI RELATED_TE MSGTXT -------------------- ----- ---------- -------------------------------------------------- REWRITE Y REWRITE_FULL_TEXT_MA Y TCH REWRITE_PARTIAL_TEXT Y _MATCH REWRITE_GENERAL Y REWRITE_PCT Y PCT_TABLE_REWRITE Y A_LIST PCT_TABLE_REWRITE N B relation is not a partitioned table 已选择7行。
从 物化视图解释信息，可以看出是可以进行查询重写的，可实际上没有， 为什么该查询没有实施查询重写，可以使用DBMS_MVIEW.EXPLAIN_REWRITE存储过程。
这里的问题是：先不说PCT,根本就没有重写。

参考：
http://docs.oracle.com/cd/B19306_01/server.102/b14223/qrbasic.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14223/qradv.htm