[20120808]學習物化檢視.txt

從來沒有使用過物化檢視,今天學習看看,主要是練習,我的測試基本按照如下連結的例子:


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(*)

----------

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

MB

----------


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(*)

----------

--發現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

[20120808]學習物化檢視.txt

相關文章