問題背景
描述
某省系統界面需要展示業務指標，該指標來自一張表如下圖所示，資料量巨大，舊方案的統計邏輯是：java cron定時任務執行sql匯總插入匯總表，匯總頻率一小時一次，
在進行定時匯總的時候，由于需要統計歷史所有資料，盡管sql已經優化避免全表掃描但是查詢sql執行時間很久，
導致：界面長時間沒資料，
限制因素
- 資料庫服務器配置一般，普通Pc配置（16g記憶體，500g硬碟，cpu不清楚），沒有ssd;
- 上集群、大資料分析、hadoop？想多了，估計沒人會（除了我，這里不謙虛了），運維成本比較高，客戶也不會給那么多機器；其實我還是很想搞這個實時計算的，
- 表沒有磁區，如果磁區可能會好點，重建表進行表磁區，但是ogg可能要重新搭建；
- 不想改架構，把資料分表會好點，但是查詢邏輯要變化；
結合問題場景分析
- 每一小時都要把近幾年的歷史資料統計一遍，歷史資料又沒變化，真的有必要？
- 該指標也不需要做到有秒級的延遲，何況提供資料的友商一天才給2次資料，資料實時性也不高；
解決方案oracle物理視圖
**大道至簡：用空間換時間；這是資料結構與演算法里面常見的解決方案， **
也即：把不變動的歷史歷史資料都加工后存盤，定時匯總任務直接查加工后的資料，資料量大幅降低，提升查詢速度，雖然資料不是最新的，但是至少有資料可以容忍，
物化視圖概述
Oracle的物化視圖是包括一個查詢結果的資料庫對像，它是遠程資料的的本地副本，或者用來生成基于資料表求和的匯總表，
物化視圖可以用于預先計算并保存表連接或聚集等耗時較多的操作的結果，這樣，在執行查詢時，就可以避免進行這些耗時的操作，而從快速的得到結果，
物化視圖特點
- 使用物化視圖的目的是為了提高查詢性能；
- 物化視圖對應用透明，增加和洗掉物化視圖不會影回應用程式中SQL陳述句的正確性和有效性；
- 物化視圖需要占用存盤空間；
- 當基表發生變化時，物化視圖也應當重繪，
這里我定的是物化視圖一天更新一次，
創建語法
CREATE MATERIALIZED VIEW XX
REFRESH [[fast | complete | force]
[on demand | commit]
[start with date]
[next date]
[with {primary key | rowid}]
]
[ENABLE | DISABLE] QUERY REWRITE
Refresh 重繪子句
描述 當基表發生了DML操作后，物體化視圖何時采用哪種方式和基表進行同步 ，
- 取值 FAST 采用增量重繪，只重繪自上次重繪以后進行的修改 ；
- COMPLETE 對整個物體化視圖進行完全的重繪 ；
- FORCE(默認) Oracle在重繪時會去判斷是否可以進行快速重繪，如果可以則采用Fast方式，否則采用Complete的方式，Force選項是默認選項；
- ON DEMAND(默認) 物體化視圖在用戶需要的時候進行重繪，可以手工通過 DBMS_MVIEW.REFRESH等方法來進行重繪，也可以通過JOB定時進行重繪 ；
- ON COMMIT 物體化視圖在對基表的DML操作提交的同時進行重繪 ；
**START WITH 第一次重繪時間 **
**
**NEXT 重繪時間間隔 **
**
**WITH PRIMARY KEY(默認) **
生成主鍵物體化視圖,也就是說物體化視圖是基于表的主鍵，而不是ROWID(對應于ROWID子句)， 為了生成PRIMARY KEY子句，應該在表上定義主鍵，否則應該用基于ROWID的物體化視圖，主鍵物體化視圖允許識別物體化視圖表而不影響物體化視圖增量重繪的可用性
REWRITE 字句
**
包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE兩種，
分別指出創建的物體化視圖是否支持查詢重寫，查詢重寫是指當對物體化視圖的基表進行查詢時，Oracle會自動判斷能否通過查詢物體化視圖來得到結果，如果可以，則避免了聚集或連接操作，而直接從已經計算好的物體化視圖中讀取資料 默認 DISABLE QUERY REWRITE
demo
CREATE MATERIALIZED VIEW bm_dqd_znzdhs
refresh force on demand start with sysdate next trunc(sysdate+1)
as
select ksdm swjgdm ,
count(distinct djxh) znzdhs,
to_date(to_char(lrrq, 'YYYY-MM-DD'), 'YYYY-MM-DD') lrrq
from t_temp_ywtj_mx r
where ywlx = '自助' and ksdm is not null
group by ksdm, to_char(lrrq, 'YYYY-MM-DD');
參考
https://www.cnblogs.com/andy-wcl/p/3430995.html
感謝關注！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/293857.html
標籤：其他