[20120808]学习物化视图.txt

[20120808]学习物化视图.txt

从来没有使用过物化视图,今天学习看看,主要是练习,我的测试基本按照如下链接的例子:

http://uhesse.com/2009/07/08/brief-introduction-into-materialized-views/

SQL> select * from v$version ;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

create table sales as select * from sh.sales;

insert into sales select * from sales;

insert into sales select * from sales;

insert into sales select * from sales;

--建立sales表,大小280M.

SQL> desc sales

Name            Null?    Type

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

PROD_ID         NOT NULL NUMBER

CUST_ID         NOT NULL NUMBER

TIME_ID         NOT NULL DATE

CHANNEL_ID      NOT NULL NUMBER

PROMO_ID        NOT NULL NUMBER

QUANTITY_SOLD   NOT NULL NUMBER(10,2)

AMOUNT_SOLD     NOT NULL NUMBER(10,2)

SQL> select count(*) from sales;

  COUNT(*)

----------

   7350744

SQL> select bytes/1024/1024 as mb from user_segments where segment_name='SALES';

        MB

----------

       280

SQL> set timing on

SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)

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

         9       2219410.08

         2        210770739

         4        109654416

         3        463002085

Elapsed: 00:00:01.78

--使用不到2秒!

1.建立物化视图:

create materialized view mv1 enable query rewrite as select channel_id,sum(amount_sold) from sales group by channel_id;

SQL> create materialized view mv1 enable query rewrite as select channel_id,sum(amount_sold) from sales group by channel_id;

Materialized view created.

Elapsed: 00:00:02.64

SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)

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

         9       2219410.08

         2        210770739

         4        109654416

         3        463002085

Elapsed: 00:00:00.01

--执行很快!

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  9wwp2am6pm4dz, child number 1

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

select channel_id,sum(amount_sold) from sales group by channel_id

Plan hash value: 2958490228

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

| Id  | Operation                    | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT             |      |        |     3 (100)|

|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |      4 |     3   (0)|

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

--可以发现访问的是物化视图mv1.

SQL> update sales set amount_sold=amount_sold+1 where rownum<2;

1 row updated.

SQL> commit ;

Commit complete.

--修改一条记录,直接查询物化视图mv1,结果不再正确.

SQL> select * from mv1;

CHANNEL_ID SUM(AMOUNT_SOLD)

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

         9       2219410.08

         2        210770739

         4        109654416

         3        463002085

Elapsed: 00:00:00.00

SQL> select mview_name,staleness from user_mviews;

MVIEW_NAME                     STALENESS

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

MV1                            NEEDS_COMPILE

Elapsed: 00:00:00.06

SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)

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

         3        463002086

         9       2219410.08

         2        210770739

         4        109654416

Elapsed: 00:00:01.71

--可以发现执行时间变长!

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  9wwp2am6pm4dz, child number 2

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

select channel_id,sum(amount_sold) from sales group by channel_id

Plan hash value: 2895541888

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

| Id  | Operation          | Name  | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT   |       |        | 10007 (100)|       |       |          |

|   1 |  HASH GROUP BY     |       |   8154K| 10007   (4)|   948K|   948K| 4874K (0)|

|   2 |   TABLE ACCESS FULL| SALES |   8154K|  9741   (1)|       |       |          |

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

--可以发现执行计划变为了full.

--为了能再次使用物化视图,必须刷新物化视图.

exec dbms_mview.refresh(list=>'MV1',method=>'C');

SQL> exec dbms_mview.refresh(list=>'MV1',method=>'C');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.57

SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)

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

         3        463002086

         9       2219410.08

         2        210770739

         4        109654416

Elapsed: 00:00:00.01

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  9wwp2am6pm4dz, child number 1

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

select channel_id,sum(amount_sold) from sales group by channel_id

Plan hash value: 2958490228

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

| Id  | Operation                    | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT             |      |        |     3 (100)|

|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |      4 |     3   (0)|

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

Note

-----

   - dynamic sampling used for this statement (level=2)

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

20 rows selected.

Elapsed: 00:00:00.02

--又可以使用物化视图mv1.

SQL> select mview_name,staleness from user_mviews;

MVIEW_NAME                     STALENESS

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

MV1                            FRESH

Elapsed: 00:00:00.02

--为了一直使用物化视图,完全刷新时间太长(如果表很大),必须改变刷新方式.

    One comfortable method to get a fast refreshable materialized view is the usage of the package DBMS_ADVISOR:

--使用dbms_advisor包可以获得一些建议方法.

variable t varchar2(50)

begin

dbms_advisor.tune_mview(task_name=>:t,

mv_create_stmt=>'create materialized view mv1'

    || ' refresh fast as'

    || ' select channel_id,sum(amount_sold)'

    || ' from sales group by channel_id');

end;

set long 5000

SQL> column  statement format a140

SQL> select script_type,statement from user_tune_mview where task_name=:t order by action_id;

SCRIPT_TYPE    STATEMENT

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

IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "SCOTT"."SALES" WITH ROWID, SEQUENCE ("CHANNEL_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES

IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."SALES" ADD ROWID, SEQUENCE ("CHANNEL_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES

IMPLEMENTATION CREATE MATERIALIZED VIEW SCOTT.MV1   REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SCOTT.SALES.CHANNEL_ID C1, SUM("SCOTT"."SALES".

               "AMOUNT_SOLD") M1, COUNT("SCOTT"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SCOTT.SALES GROUP BY SCOTT.SALES.CHANNEL_ID

UNDO           DROP MATERIALIZED VIEW SCOTT.MV1

--可以发现建议要这样建立MATERIALIZED VIEW LOG.

exec dbms_advisor.delete_task(:t);

SQL> select script_type,statement from user_tune_mview where task_name=:t order by action_id;

no rows selected

--执行上述语句,修改以下代码.系统会建议一张 MLOG$_SALES记录变化.

CREATE MATERIALIZED VIEW LOG ON "SCOTT"."SALES" WITH ROWID, SEQUENCE ("CHANNEL_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES;

--alter materialized view log force on "scott"."sales" add rowid, sequence ("channel_id","amount_sold")  including new values;

drop MATERIALIZED VIEW SCOTT.MV1;

CREATE MATERIALIZED VIEW SCOTT.MV1   REFRESH FAST WITH ROWID enable QUERY REWRITE AS SELECT SCOTT.SALES.CHANNEL_ID C1, SUM("SCOTT"."SALES".

"AMOUNT_SOLD") M1, COUNT("SCOTT"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SCOTT.SALES GROUP BY SCOTT.SALES.CHANNEL_ID;

set timing off

update sales set amount_sold=amount_sold*1 where rownum<2;

--打开另外的回话

SQL2> select * from MLOG$_SALES;

no rows selected

commit;

SQL2> select count(*) from MLOG$_SALES;

  COUNT(*)

----------

         2

--发现log存在记录!

select mview_name,staleness from user_mviews;

SQL> select mview_name,staleness from user_mviews;

MVIEW_NAME                     STALENESS

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

MV1                            NEEDS_COMPILE

set timing on

exec dbms_mview.refresh('MV1','F');

SQL> exec dbms_mview.refresh('MV1','F');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10

--刷新很快!'C'=>表示完全刷新,'F'=>表示快速刷新，也就是增量刷新

--但是这样系统每次都要手工刷新,可以使用job来更新,也可以使用如下命令,这样每分钟更新一次.

alter materialized view mv1 refresh start with sysdate next sysdate + interval '1' minute;

--alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;

SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;

MVIEW_NAME                     LAST_REF LAST_REFRESH_DATE

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

MV1                            FAST     2012-08-09 11:29:02

--等1分钟看!

select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;

SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;

MVIEW_NAME                     LAST_REF LAST_REFRESH_DATE

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

MV1                            FAST     2012-08-09 11:30:02

--也可以采用提交的时候刷新,这样如果表DML很多,对性能有一些影响.要仔细权衡.

drop MATERIALIZED VIEW SCOTT.MV1;

CREATE MATERIALIZED VIEW SCOTT.MV1

 REFRESH FAST on commit 

 WITH ROWID

 enable QUERY REWRITE AS

 SELECT SCOTT.SALES.CHANNEL_ID C1,

 SUM("SCOTT"."SALES"."AMOUNT_SOLD") M1,

 COUNT("SCOTT"."SALES"."AMOUNT_SOLD") M2,

 COUNT(*) M3

 FROM SCOTT.SALES

 GROUP BY SCOTT.SALES.CHANNEL_ID;

select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;

SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;

MVIEW_NAME                     LAST_REF LAST_REFRESH_DATE

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

MV1                            COMPLETE 2012-08-09 11:36:31

Elapsed: 00:00:00.00

update sales set amount_sold=amount_sold*1 where rownum<2;

commit;

select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;

SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;

MVIEW_NAME                     LAST_REF LAST_REFRESH_DATE

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

MV1                            FAST     2012-08-09 11:36:59

SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)

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

         2        210770739

         4        109654416

         3        463002086

         9       2219410.08

Elapsed: 00:00:00.00

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  9wwp2am6pm4dz, child number 1

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

select channel_id,sum(amount_sold) from sales group by channel_id

Plan hash value: 2958490228

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

| Id  | Operation                    | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT             |      |        |     3 (100)|

|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |      4 |     3   (0)|

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

Note

-----

   - dynamic sampling used for this statement (level=2)

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

20 rows selected.

Elapsed: 00:00:00.02

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

转载于:http://blog.itpub.net/267265/viewspace-740487/