物化视图PCT特性（1）

最新推荐文章于 2019-04-03 13:30:04 发布

Laughing

最新推荐文章于 2019-04-03 13:30:04 发布

阅读量1.2k

点赞数

分类专栏： Oracle 物化视图文章标签： sql list access filter insert disk

本文链接：https://blog.csdn.net/aqszhuaihuai/article/details/7104134

版权

Oracle 物化视图专栏收录该内容

11 篇文章 0 订阅

订阅专栏

SQL> select * from v$version where rownum =1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

理解PCT特性
当物化视图的某些分区信息变得陈旧时，这些受影响分区的查询重写不再有效。
这有个前提:
The optimizer uses PCT rewrite in QUERY_REWRITE_INTEGRITY = ENFORCED and TRUSTED modes. The optimizer does not use PCT rewrite in STALE_TOLERATED mode because data freshness is not considered in that mode.
QUERY_REWRITE_INTEGRITY参数要设置为ENFORCED或TRUSTED模式，因为优化器要使用的数据为fresh的。

以List分区为例：

SQL> CREATE TABLE a_list
  2  (id 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,'a' FROM dual CONNECT BY LEVEL <= 10
  3  UNION ALL
  4  SELECT 10+LEVEL,'b' FROM dual CONNECT BY LEVEL <= 10
  5  UNION ALL
  6  SELECT 20+LEVEL,'c' FROM dual CONNECT BY LEVEL <= 10;

已创建30行。

SQL> commit;

提交完成。

SQL> CREATE MATERIALIZED VIEW a_list_mv
  2  BUILD IMMEDIATE
  3  REFRESH FORCE ON DEMAND
  4  ENABLE QUERY REWRITE AS
  5  select type,count(*) from a_list
  6  group by type;

实体化视图已创建。

SQL> set autot on
SQL> select type,count(*) from a_list where type='a' group by type;

T   COUNT(*)
- ----------
a         10


执行计划
----------------------------------------------------------
Plan hash value: 397019434

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

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

   1 - filter("A_LIST_MV"."TYPE"='a')

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        387  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)
          1  rows processed

SQL> set autot off
SQL> insert into a_list values(1,'b');

已创建 1 行。

SQL> commit;

提交完成。

SQL> set autot on
--添加的记录对type='a'的分区没有影响，查询重写依然生效。
SQL> select type,count(*) from a_list where type='a' group by type;

T   COUNT(*)
- ----------
a         10


执行计划
----------------------------------------------------------
Plan hash value: 397019434

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

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

   1 - filter("A_LIST_MV"."TYPE"='a')

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        387  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)
          1  rows processed

SQL> set autot off
SQL> insert into a_list values(1,'a');

已创建 1 行。

SQL> commit;

提交完成。

SQL> set autot on
--添加的记录使得物化视图中有关type='a'分区信息变得陈旧，查询重写不再有效。
SQL> select type,count(*) from a_list where type='a' group by type;

T   COUNT(*)
- ----------
a         11


执行计划
----------------------------------------------------------
Plan hash value: 669100331

-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |    11 |    33 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY NOSORT  |        |    11 |    33 |     3   (0)| 00:00:01 |       |       |
|   2 |   PARTITION LIST SINGLE|        |    11 |    33 |     3   (0)| 00:00:01 |     1 |     1 |
|   3 |    TABLE ACCESS FULL   | A_LIST |    11 |    33 |     3   (0)| 00:00:01 |     1 |     1 |
-------------------------------------------------------------------------------------------------

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


统计信息
----------------------------------------------------------
        126  recursive calls
         34  db block gets
         80  consistent gets
          0  physical reads
       5524  redo size
        387  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)
          1  rows processed

SQL> exec DBMS_MVIEW.REFRESH('a_list_mv','f');

PL/SQL 过程已成功完成。

SQL> select type,count(*) from a_list where type='a' group by type;

T   COUNT(*)
- ----------
a         11


执行计划
----------------------------------------------------------
Plan hash value: 397019434

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

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

   1 - filter("A_LIST_MV"."TYPE"='a')

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        387  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)
          1  rows processed

非分区测试：

SQL> CREATE TABLE a
  2  (id , type )
  3  AS
  4  SELECT LEVEL,'a' FROM dual CONNECT BY LEVEL <= 10
  5  UNION ALL
  6  SELECT 10+LEVEL,'b' FROM dual CONNECT BY LEVEL <= 10
  7  UNION ALL
  8  SELECT 20+LEVEL,'c' FROM dual CONNECT BY LEVEL <= 10;

表已创建。

SQL> CREATE MATERIALIZED VIEW a_mv
  2  BUILD IMMEDIATE
  3  REFRESH FORCE ON DEMAND
  4  ENABLE QUERY REWRITE AS
  5  select type,count(*) from a
  6  group by type;

实体化视图已创建。

SQL> set autot on
SQL> select type,count(*) from a  where type='a' group by type ;

T   COUNT(*)
- ----------
a         10


执行计划
----------------------------------------------------------
Plan hash value: 3318198354

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

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

   1 - filter("A_MV"."TYPE"='a')

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


统计信息
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        387  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)
          1  rows processed

SQL> set autot off
SQL> insert into a values(1,'b');

已创建 1 行。

SQL> commit;

提交完成。

SQL> set autot on
SQL> select type,count(*) from a  where type='a' group by type ;

T   COUNT(*)
- ----------
a         10


执行计划
----------------------------------------------------------
Plan hash value: 79714678

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    10 |    30 |     3   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|      |    10 |    30 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | A    |    10 |    30 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - filter("TYPE"='a')

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


统计信息
----------------------------------------------------------
        110  recursive calls
         34  db block gets
         78  consistent gets
          0  physical reads
       5484  redo size
        387  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)
          1  rows processed

SQL> exec DBMS_MVIEW.REFRESH('a_mv');

PL/SQL 过程已成功完成。

SQL> select type,count(*) from a  where type='a' group by type ;

T   COUNT(*)
- ----------
a         10


执行计划
----------------------------------------------------------
Plan hash value: 3318198354

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

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

   1 - filter("A_MV"."TYPE"='a')

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


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        387  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)
          1  rows processed

查询重写完整级别：

SQL> show parameter query_rewrite_integrity

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_integrity              string      enforced

可见，当完整级别设置为enforced时，物化视图的数据为fresh时，查询重写才有可能变得有效。

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

Laughing

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
物化视图PCT特性（1）

SQL> select * from v$version where rownum =1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod理解PCT特性当物化视图的
复制链接

扫一扫

专栏目录