记录一次物化视图解决视图查询慢问题
涉及一个视图优化,记录如下, 会有相应时间的数据延迟。
CREATE MATERIALIZED VIEW PU_VIEW_STOREREQ
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + 10/1440 --每10min重新物化一次
AS
(
SELECT
s.pk_storereq_b AS pk_storereq_b,
s.pk_storereq AS storereq,
f.DBILLDATE AS dbilldate,
NVL(h.NNUM,0) AS stockonhand,
NVL(p.NNUM ,0) AS prospectivevolume
FROM
PO_STOREREQ s
LEFT JOIN
po_stor f
ON
s.PK_STOREREQ = f.PK_STOREREQ
LEFT JOIN
ONHANDNUM h
ON
h.PK_MATERIAL = s.PK_MATERIAL
LEFT JOIN
PROSPECTNUM p
ON
p.PK_MATERIAL = s.PK_MATERIAL
WHERE
s.dr = 0
)
# 手动物化
BEGIN
dbms_mview.refresh(list => 'PU_VIEW_STOREREQ',
method => 'COMPLETE',
refresh_after_errors => TRUE);
END;
/
从37s优化到0.95s
https://blog.csdn.net/m0_37253968/article/details/120408558
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 单线程的Redis速度为什么快?
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?
· 展开说说关于C#中ORM框架的用法!