|
为话单表建物化视图。主要针对主叫号码，被叫号码的通话次数，进行合计。
通过对用户的日常通话频率的统计，运营商能够获得很多有价值的信息。
生产环境预计数据1500W/day，目前有一个200W的测试表，做了composite partition，local index。
Here we go..
Table Description：
SQL> desc analyse_content;
Name Type Nullable Default Comments
--------- ------------ -------- ------- ------------------
MAINNUM VARCHAR2(40) Y 主叫号码
SUBNUM VARCHAR2(40) Y 被叫号码
CALLTIME DATE Y 开始时间
MAINSITE VARCHAR2(20) Y 主叫基站
SUBSITE VARCHAR2(20) Y 被叫基站
CS VARCHAR2(20) Y 基站编码
CSID VARCHAR2(20) Y CSID
AREA VARCHAR2(20) Y 主叫归属地区号
ENDTIME DATE Y 结束时间
COMPANY VARCHAR2(10) Y PHS运营商类型
LONGITUDE VARCHAR2(20) Y 经度
LATITUDE VARCHAR2(20) Y 纬度
SN NUMBER 序列
PARSETIME DATE Y 解析时间
LOCAL VARCHAR2(20) Y 地区
Create Materialized View Logs,因为我要创建基于FAST REFRESH的视图，所以必须创建物化视图日志。
因为采用了FAST刷新，所以必须指定SEQUENCE关键字，而且聚合类型的物化视图必须包含原始表的所有
字段和指定SEQUENCE：
SQL> CREATE MATERIALIZED VIEW LOG ON ANALYSE_CONTENT WITH SEQUENCE,ROWID
2 (mainnum, subnum, calltime, mainsite, subsite, cs, csid, area, endtime,
3 company, longitude, latitude, sn, parsetime, LOCAL)
4 INCLUDING NEW VALUES
5 /
Materialized view log created
Create Materialized View，我希望数据是增量更新，所以指定了FAST选项，REFRESH ON COMMIT FAST是
Oracle document里面推荐的选项:
SQL> CREATE MATERIALIZED VIEW analyse_mv1
2 TABLESPACE PHSDATA
3 BUILD IMMEDIATE
4 REFRESH ON COMMIT FAST
5 ENABLE QUERY REWRITE AS
6 SELECT COUNT(1) cn,t.mainnum,t.subnum,to_char(calltime,'YYYY-MM-DD'),t.area
7 FROM analyse_content t GROUP BY t.mainnum,t.subnum,to_char(calltime,'YYYY-MM-DD'),t.area
8 /
Materialized view created
创建以后，我再观察原始查询的执行计划，已经已经由PARTITION LIST FULL SCAN变成SCAN物化视图了，物理读
大幅下降，如下：
执行计划
SQL> SELECT COUNT(1) cn,t.mainnum,t.subnum,to_char(calltime,'YYYY-MM-DD'),t.area
2 FROM analyse_content t GROUP BY t.mainnum,t.subnum,to_char(calltime,'YYYY-MM-DD'),t.area;
----------------------------------------------------------
Plan hash value: 3448750043
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2066K| 149M| 2636 (4)| 00:00:32 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| ANALYSE_MV1 | 2066K| 149M| 2636 (4)| 00:00:32 |
--------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
144269 consistent gets
10497 physical reads
0 redo size
77370809 bytes sent via SQL*Net to client
1453649 bytes received via SQL*Net from client
132117 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1981740 rows processed
更改查询后，同样可以使用MV
SQL> SELECT COUNT(1) cn,t.mainnum
2 FROM analyse_content t GROUP BY t.mainnum
3 /
已选择690098行。
执行计划
----------------------------------------------------------
Plan hash value: 696214863
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2066K| 68M| 2951 (14)| 00:00:36 |
| 1 | HASH GROUP BY | | 2066K| 68M| 2951 (14)| 00:00:36 |
| 2 | MAT_VIEW REWRITE ACCESS FULL| ANALYSE_MV1 | 2066K| 68M| 2614 (3)| 00:00:32 |
---------------------------------------------------------------------------------------------
Test 1,少量更新，纯粹REFRESH ON COMMIT会严重的影响COMMIT的性能，尤其是在一个需要每天载入的数据
的表中，所以不得不选用增量更新的方式，如果性能影响还不能满足实际需求，甚至可能采用手动或者定时
刷新，下面开始进行一些刷新的性能测试：
插入前的MV
SQL> SELECT SUM(CN) FROM analyse_mv1 WHERE mainnum = '02888851655';
SUM(CN)
----------
50
SQL>
SQL> BEGIN
2 FOR i IN 1..10 LOOP
3 INSERT INTO analyse_content(mainnum, subnum, calltime, mainsite, subsite, cs, csid, area, endtime, company, longitude, latitude, sn, parsetime, LOCAL)
4 VALUES ('02888851655', '013708076397', to_date('2007-12-11 20:58:58','yyyy-mm-dd hh24:mi:ss'),
5 NULL, NULL, NULL, 'xxx', '028', SYSDATE, 'ZX', 'NO', 'NO', seq_content.nextval, SYSDATE, '028');
6 COMMIT;
7 END LOOP;
8 END;
9 /
PL/SQL procedure successfully completed
插入后
SQL> SELECT SUM(CN) FROM analyse_mv1 WHERE mainnum = '02888851655';
SUM(CN)
----------
60
测试结果，COMMIT的速度非常快，FAST选项的确生效。Oracle对聚合统计incremental更新实在是一种非常
好的技术，有时间我一定要专研一下它的内部原理。
Test 2,大量更新，再观察物化视图的更新情况：
SQL> BEGIN
2 FOR i IN 1..10000 LOOP
3 INSERT INTO analyse_content(mainnum, subnum, calltime, mainsite, subsite, cs, csid, area, endtime, company, longitude, latitude, sn, parsetime, LOCAL)
4 VALUES ('02888851655', '013708076397', to_date('2007-12-11 20:58:58','yyyy-mm-dd hh24:mi:ss'),
5 NULL, NULL, NULL, 'xxx', '028', SYSDATE, 'ZX', 'NO', 'NO', seq_content.nextval, SYSDATE, '028');
6 COMMIT;
7 END LOOP;
8 END;
9 /
... in process
open another window:
SQL> SELECT SUM(CN) FROM analyse_mv1 WHERE mainnum = '02888851655';
SUM(CN)
----------
372
SQL> SELECT SUM(CN) FROM analyse_mv1 WHERE mainnum = '02888851655';
SUM(CN)
----------
396
SQL> SELECT SUM(CN) FROM analyse_mv1 WHERE mainnum = '02888851655';
SUM(CN)
----------
421
SQL> SELECT SUM(CN) FROM analyse_mv1 WHERE mainnum = '02888851655';
SUM(CN)
----------
440
...
可以看出，对批量更新，也可以快速的维护物化视图变化，每秒中插入记录的效率并没太大下降，不过在
应用到生产系统之前，必须对MV对ETL影响做更详细的评估。
如果会发现并发插入，Oracle的document曾经提及20个以内的并发是可以接受的。
最后：
更多物化视图的使用，可以参考Oracle datawarehourse guide 3.8,3.9章节，英文不好的筒子可以THOMAS
的专家编程的13章。
|
实战物化视图
一共有 0 条评论