情况介绍
现有表OE.PRODUCT_INFORMATION数据量为800万，求出各个供应商供应产品的数量

无使用物化视图，只在建立SUPPLIER_ID建立索引，执行时间为15s左右

SELECT SUPPLIER_ID,COUNT(*) FROM OE.PRODUCT_INFORMATION GROUP BY SUPPLIER_ID ORDER BY SUPPLIER_ID;

使用物化视图(MATERIALIZED VIEW)，时间为50ms左右

--Create a mv log before creating a mv
CREATE MATERIALIZED VIEW LOG ON OE.PRODUCT_INFORMATION WITH ROWID,SEQUENCE (SUPPLIER_ID),PRIMARY KEY INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW PI_MV_ON_SUPPLIERS BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT SUPPLIER_ID,COUNT(SUPPLIER_ID) FROM OE.PRODUCT_INFORMATION GROUP BY SUPPLIER_ID SELECT * FROM PI_MV_ON_SUPPLIERS;

click Materialized View Log and Materialized View for more information