[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
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
--打开另外的回话
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
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
时间： 2024-08-28 03:02:31