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