情况介绍
现有表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
Oracle使用物化视图提高group by性能
最新推荐文章于 2024-07-15 20:03:26 发布