Expert One-on-One Oracle Chapter 13 物化视图 读书笔记(1)

主要讲什么是物化视图，物化视图可以帮助我们做什么以及物化视图是如何工作的
 1、什么是物化视图？ 要了解物化视图的本质，物化视图的特性是是否是你想要的。
 我的理解物化视图的本质：是用磁盘空间的资源来存储已经统计好的信息这样减少了对实时得对很大的主表的统计查询所需要的时间。
 下面举例说明什么是物化视图
 1）首先创建物化视图，需要授予一些权限：
 *grant create session
 *grant create table
 *grant create materialized view
 *grant query rewrite
注：查询重写是必须要在基于成本的优化器(cost-Based Optimizer,CBO)环境下才能用，如果没有使用CBO，
则将不会发生查询重写。下面所举的例子中，优化器将为缺省的CHOOSE（Choolse:这是我们应观注的,默认的情况下Oracle用的便是这种
方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就
走索引,走RBO的方式。），当需要利用查询重些时之前，必须要对表做下统计，才能使查询重写使用在CBO优化器环境下。
2）下面是练习与验证过程
EXAMPLE1:
新建一张大表：
drop table my_all_objects;

create table my_all_objects
nologging
as
select * from all_objects
union all
select * from all_objects
union all
select * from all_objects ;
/

insert /*+ APPEND */ into my_all_objects
select * from my_all_objects;

commit;

insert /*+ APPEND */ into my_all_objects
select * from my_all_objects;

commit;

//此处需要对对表my_all_objects做下统计
SQL> analyze table my_all_objects compute statistics;

Table analyzed.

SQL> set autotrace on;
SQL> set timing on;
SQL> select owner, count(*) from my_all_objects group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
CTXSYS                               3132
DAIMIN                                360
HR                                    408
MDSYS                                2808
ODM                                  5268
ODM_MTR                               144
OE                                   1032
OLAPSYS                              7944
ORDPLUGINS                            348
ORDSYS                              11628
OUTLN                                  84

OWNER                            COUNT(*)
------------------------------ ----------
PM                                    108
PUBLIC                             146796
QS                                    492
QS_ADM                                 84
QS_CBADM                              288
QS_CS                                 276
QS_ES                                 468
QS_OS                                 468
QS_WS                                 468
SCOTT                                  72
SH                                   2088

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                166524
SYSTEM                               4584
TEST                                  360
WKSYS                                3348
WMSYS                                1548
XDB                                  3240

28 rows selected.

Elapsed: 00:00:03.82

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1025 Card=28 Bytes=1
          40)

   1    0   SORT (GROUP BY) (Cost=1025 Card=28 Bytes=140)
   2    1     TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS' (Cost=489 Card=3
          64368 Bytes=1821840)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4984  consistent gets              --逻辑I/O执行了4984次
       4173  physical reads               --物理I/O也需要进行4173次
          0  redo size
        970  bytes sent via SQL*Net to client
        514  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         28  rows processed

分析上面的结果：
对36万条记录进行聚集的计算，需要逻辑I/O执行4984次，下面通过创建物化视图来进行聚集计算：

SQL> grant query rewrite to test;

Grant succeeded.

SQL> alter session set query_rewrite_enabled=true;

Session altered.

SQL> alter session set query_rewrite_integrity=enforced;

Session altered.

create materialized view my_all_objects_aggs
build immediate         --立即建立物化视图并填充数据
refresh on commit       --有数据提交就会及时更新物化视图
enable query rewrite    --允许查询重写
as
select owner, count(*)
  from my_all_objects
 group by owner;

注：物化视图实质上就是一张真正的表，可以被索引以及被分析；

SQL> analyze table my_all_objects_aggs compute statistics;

Table analyzed.

创建好物化视图之后，再执行同样的查询，查看其执行计划的变化情况：
SQL> set timing on;
SQL> set autotrace on;
SQL> select owner, count(*)
  from my_all_objects
  2    3   group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
CTXSYS                               3132
DAIMIN                                360
HR                                    408
MDSYS                                2808
ODM                                  5268
ODM_MTR                               144
OE                                   1032
OLAPSYS                              7944
ORDPLUGINS                            348
ORDSYS                              11628
OUTLN                                  84

OWNER                            COUNT(*)
------------------------------ ----------
PM                                    108
PUBLIC                             146796
QS                                    492
QS_ADM                                 84
QS_CBADM                              288
QS_CS                                 276
QS_ES                                 468
QS_OS                                 468
QS_WS                                 468
SCOTT                                  72
SH                                   2088

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                166524
SYSTEM                               4584
TEST                                  360
WKSYS                                3348
WMSYS                                1548
XDB                                  3240

28 rows selected.

Elapsed: 00:00:00.47

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=28 Bytes=224)
   1    0   TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=2 Card=
          28 Bytes=224)

 

 

Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
         16  consistent gets   --使用物化视图之后逻辑I/O次数由原来的4984次减少到16次
          0  physical reads    --并且物理I/O也由4173次变为0，表明原来该表在内存中现在可以一次性存放的下来了
          0  redo size
        970  bytes sent via SQL*Net to client
        514  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
         28  rows processed

分析：由上面的例子比较看出，查询同样的语句，如果创建了物化视图，并且使用了查询重写(query_rewrite=true)功能，数据库自动将其指向物化视图；

下面新插入一条记录，查看物化视图的变化：
SQL> insert into my_all_objects
( owner, object_name, object_type, object_id )
values
( 'New Owner', 'New Name', 'New Type', 1111111 );  2    3    4

1 row created.

Elapsed: 00:00:00.40

Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE (Cost=489 Card=364368 Byte
          s=31335648)

Statistics
----------------------------------------------------------
          0  recursive calls
         32  db block gets
          4  consistent gets
          0  physical reads
       1604  redo size
        626  bytes sent via SQL*Net to client
        633  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;

Commit complete.

Elapsed: 00:00:05.04

SQL> set timing on;
SQL> set autotrace on;
SQL> select owner, count(*)
  2    from my_all_objects
 where owner = 'New Owner'
  3    4   group by owner;
 
OWNER                            COUNT(*)
------------------------------ ----------
New Owner                               1

Elapsed: 00:00:00.34

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=8)
   1    0   TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=2 Card=
          1 Bytes=8)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        442  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

分析：新插入一条数据之后，通过同样的查询语句查该条记录，首先发现查询语句的执行计划中采用了物化视图MY_ALL_OBJECTS_AGGS，并且能够查询到该条新加入的记录；
      说明创建的物化视图是及时更新的自己，保持与主表同步，说明在创建物化视图时refresh on commit语句起了作用；
书上说要注意：refresh on commit语句不能对任意的物化视图的每一种情况维护同步，而是对于单一表的物化视图或没有任何聚集的连接则可以.

我想在下面的学习过程中验证下书上说的这个注意点是否在oracle9i上可以，然后测试过程写出来.

还有一个例子如下：

SQL> set timing on;
SQL> set autotrace on;
SQL> select count(*)
  2    from my_all_objects
 where owner = 'New Owner';  3

  COUNT(*)
----------
         1

Elapsed: 00:00:02.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=8)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=2 Car
          d=1 Bytes=8)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        379  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

分析：上面的例子SQL语句中并没有group by子句，而数据库则认为该物化视图仍然可以使用。

以上的例子都是使用了物化视图的查询重写的功能，数据库会认识到答案已经存在，并且会
自动地重写此查询以使用物化视图。