物化视图PCT特性（2）

wapysun

浏览: 21511091 次
性别:
来自: 杭州

最近访客更多访客>>

devcang

hunankeda110

辽东小小

apex53

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2012-06 ( 77)
2012-05 ( 587)
2012-04 ( 177)
更多存档...

如果含有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

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

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

2 - access("T1"."ID"="T2"."ID")

Note
-----
   - dynamic sampling used for this statement

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

分享到：