Oracle使用物化视图提高group by性能

• 情况介绍
  现有表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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!