物化视图PCT特性（2）

最新推荐文章于 2021-04-02 21:16:01 发布
最新推荐文章于 2021-04-02 21:16:01 发布
阅读量1.2k 收藏
点赞数
分类专栏： Oracle 物化视图 文章标签： sql table access list insert disk
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/aqszhuaihuai/article/details/7107435
版权

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


Laughing
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
物化视图PCT特性（1）
Laughing
12-26 1227
SQL> select * from v$version where rownum =1; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 理解PCT特性 当物化视图的
物化视图的PCT特性
cuanzhong6087的博客
10-29 477
Oracle的物化视图从9i开始支持了PARTITION CHANGE TRACKING（PCT）功能。 PCT是基于分区的修改跟踪，如果基表进行了分区，Oracle可以知道物化视图中的每条记录会被基表中的哪个...
dbms_mview系列(三)
02-22 235
dbms_mview系列(三)EXPLAIN_REWRITE Procedure和explain_mview同理,也有2个重载过程形式.一个存储到表,另一个存储到array语法 DBMS_MVIEW.EXPLAIN_REWRI...
物化视图详解(原创)
czmmiao的专栏
03-10 648
物化视图 物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。物化视图可以基于表查询，视图和其它的物化视图。通常情况下，在复制环境下，物化视图被称为主表，在数据仓库中称为明细表。对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从...
PostgreSQL物化视图（materialized view）过程解析
09-08
PostgreSQL的物化视图（Materialized View）是一种数据库特性，它允许用户预先计算和存储复杂查询的结果，形成一个新的表，以提供更快的数据访问速度。物化视图不同于普通的视图，后者在查询时动态计算结果，而物化...
Oracle物化视图使用详解
06-09
2. 刷新策略：物化视图可以设置为手动刷新或自动刷新。手动刷新需要用户通过`REFRESH MATERIALIZED VIEW`语句来触发；自动刷新则可以根据定时器或依赖关系自动进行。 3. 分区：对于大量数据，物化视图可采用分区...
mysql触发器实现oracle物化视图示例代码
09-10
物化视图与普通的视图不同，视图是基于基表的逻辑表示，而物化视图则是物理存在并维护的表，其数据会随着基表的更新而自动或手动刷新。 在MySQL中，由于不直接支持物化视图，我们可以使用触发器来模拟Oracle的物化...
PostgreSQL物化视图的刷新机制.pptx
10-14
物化视图可以分为四种类型：快照物化视图、积极物化视图、消极物化视图和非常消极物化视图。 为什么使用物化视图？在执行某些查询时，效率较低，传统方法（例如，索引或查询优化）无法显著提高效率。这时使用的方法...
Oracle物化视图整理
04-29
Oracle物化视图整理，包含所有资料，值得学习
Oracle物化视图DBMS_MVIEW.EXPLAIN_MVIEW包的使用
数据库天地
04-27 451
DBMS_MVIEW.EXPLAIN_MVIEW能分析三种不同的物化视图代码，分别是：1.定义的查询 2.一个CREATE MATERIALIZED VIEW的语句 3.一个存在的物化视图 步骤如下：1、提前执行dbms_mview.explain_mview用到的表mv_capabilities_table的建表语句 SQL&gt;@F:\oracle\product\10.2.0\db...
使用物化视图和查询重写功能
haiross的专栏
04-11 6649
OBE 主页 > 10gR2 单实例 > 商务智能 使用物化视图和查询重写功能 目的 在本教程中，您将了解如何利用强大的物化视图和查询重写功能。 所需时间 大约 2 个小时 主题 本教程包括下列主题： 概述 情景 前提条件 为销售历史模式实施模式更改 启用查询重写 分析物化视
DBMS_MVIEW.EXPLAIN_MVIEW包的使用
luguohai123的专栏
05-20 2559
DBMS_MVIEW.EXPLAIN_MVIEW能分析三种不同的物化视图代码，分别是： 1.定义的查询 2.一个CREATE MATERIALIZED VIEW的语句 3.一个存在的物化视图 步骤如下： 1、提前执行dbms_mview.explain_mview用到的表mv_capabilities_table的建表语句 SQL>@F:\oracle\product\10.2.
物化视图：使用dbms_mview.explain_mview
DATA
04-09 2505
可以在《Oracle® Database Data Warehousing Guide》中找到更多内容 http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#DWHSG8219   基本概况 用dbms_mview.explain_mview可以解释、分析物化视图，也可以用来排错。 dbms_mview.exp
利用PCT解决快速刷新性能问题
cuilin2183的博客
04-03 272
在数据仓库的一个应用中，有一个这样的案例：1. 源表很大，且是根据天做的分区表2. 正常情况下，每天凌晨往源表一次性导入上一天的数据3. 正常情况下，数据导入后不会做任何的修改和删除操作4. 有其他的应用需要对这个源表的数据做聚...
基于分区表的物化视图快速刷新以及维护
BoWa21C的专栏
12-27 1217
【全文转自：http://blog.csdn.net/zjc0507/article/details/7865283】 从业DBA多年，始终致力于OLTP的实时交易类型的数据库。龙年伊始，接手了阿联酋惠普 deliver给阿曼电信的名为Dragon的系统，该系统是用于反恐的data warehousing和BI分析系统，这才第一次和数据仓库有了接触，区区两个月的时间，也没有什么特别深的体会，只是
oracle分区注意点,转 oracle 分区表删除的两个注意事项
weixin_39940755的博客
04-02 132
原文：oracle分区表维护的两个注意事项Oracle 10g分区表维护中的两个注意事项:1.全局索引SQL> alter table analyse_content truncate subpartitionDATA0712_DEYANG;Table truncatedtruncate分区后，修改或者插入数据报错：ORA-01502: index 'phs.pk' or partition...
物化视图 Materialized View
weixin_34226182的博客
08-03 524
一、物化视图的一般用法物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处。1、物化视图的类型ON DEMAND、ON COMMIT。二者的区别在于刷新方法的不同，ON DEMAND顾名思义，仅在该物化视图“需要”被...
Oracle物化视图详解与创建实例
2. Rowid物化视图：这种视图使用行标识符（Rowid）来跟踪数据的变化，适合于不需要主键的场景。创建Rowid物化视图的命令如下： ```sql CREATE MATERIALIZED VIEW mv_emp_rowid REFRESH WITH ROWID AS SELECT * ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值