[20120808]學習物化檢視.txt

lfree發表於2012-08-09
[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(*)
----------
   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/，如需轉載，請註明出處，否則將追究法律責任。







相關文章