物化视图的query_rewrite_enabled属性

SQL> set timing on;

SQL> set autotrace traceonly;

SQL> select ordseq,sum(qty) from orditmdtl group by ordseq;

 

已选择472行。

 

已用时间:  00: 00: 00.09

 

执行计划

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=165 Card=472 Bytes

          =3776)

 

   1    0   SORT (GROUP BY) (Cost=165 Card=472 Bytes=3776)

   2    1     TABLE ACCESS (FULL) OF 'ORDITMDTL' (CLUSTER) (Cost=164 C

          ard=5014 Bytes=40112)

 

 

 

 

 

统计信息

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        742  consistent gets

          0  physical reads

          0  redo size

       8710  bytes sent via SQL*Net to client

        853  bytes received via SQL*Net from client

         33  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

472       rows processed

创建可重新书写查询物化视图后

create materialized view sum_sales
build immediate
refresh complete
enable query rewrite
as select  ordseq,sum(qty)
from orditmdtl
group by ordseq;

 

SQL> show parameter query_rewrite_enabled;

 

NAME                                 TYPE

------------------------------------ ----------------------

VALUE

------------------------------

query_rewrite_enabled                string

TRUE

SQL> select ordseq,sum(qty) from orditmdtl group by ordseq;

 

已选择472行。

 

已用时间:  00: 00: 00.09

 

执行计划

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=472 Bytes=1

          2272)

 

   1    0   MAT_VIEW REWRITE ACCESS (FULL) OF 'SUM_SALES' (MAT_VIEW RE

          WRITE) (Cost=2 Card=472 Bytes=12272)

 

 

 

 

 

统计信息

----------------------------------------------------------

         15  recursive calls

          0  db block gets

         68  consistent gets

          0  physical reads

          0  redo size

       8710  bytes sent via SQL*Net to client

        853  bytes received via SQL*Net from client

         33  SQL*Net roundtrips to/from client

          5  sorts (memory)

          0  sorts (disk)

        472  rows processed

 

SQL> select sum(qty) from orditmdtl;

 

已用时间:  00: 00: 00.04

 

执行计划

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13)

   1    0   SORT (AGGREGATE)

   2    1     MAT_VIEW REWRITE ACCESS (FULL) OF 'SUM_SALES' (MAT_VIEW

          REWRITE) (Cost=2 Card=472 Bytes=6136)

 

 

 

 

 

统计信息

----------------------------------------------------------

          4  recursive calls

          0  db block gets

          7  consistent gets

          0  physical reads

          0  redo size

        427  bytes sent via SQL*Net to client

        512  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

1         rows processed

对比创建可重新书写查询物化视图前后,可知创建后进行求和查询性能有了很大的提高.所以对于常用的求和查询,创建一个可重新书写查询视图是一个提高性能的方法.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13387766/viewspace-524628/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13387766/viewspace-524628/