记录一次物化视图解决视图查询慢问题

涉及一个视图优化,记录如下, 会有相应时间的数据延迟。

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

posted @   Qtong  阅读(379)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 单线程的Redis速度为什么快?
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?
· 展开说说关于C#中ORM框架的用法!
点击右上角即可分享
微信分享提示