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

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

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