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