Materialized Views Driving Me Mad!

From: Gee <george_at_41donny.freeserve.co.uk>
Date: 9 Apr 2002 07:25:54 -0700
Message-ID: <f7859e6f.0204090625.2771d9cc@posting.google.com>

I have crated a mview using the following : CREATE SNAPSHOT CAESAR_DELIVERY_LINE_MV TABLESPACE ORA9
PCTFREE 1 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 100M NEXT 10M MINEXTENTS 1 MAXEXTENTS 10 PCTINCREASE 1)
REFRESH FORCE
ENABLE QUERY REWRITE
AS
SELECT CAESAR_DELIVERY_LINE.COMPANY_CODE,   CAESAR_DELIVERY_LINE.LDG_PERIOD,

  M_PRODUCTS.BUSINESS_DESC,
  sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED) as QUANTITY_DELIVERED,
  sum(CAESAR_DELIVERY_LINE.EURO_REVENUE) as EURO_REVENUE,
  sum(CAESAR_DELIVERY_LINE.EURO_COST) as EURO_COST,
  sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED * M_PRODUCTS.MAIN_UNIT)

as VOLUME_DELIVERED
FROM
  CAESAR_DELIVERY_LINE,
  M_PRODUCTS
WHERE
( M_PRODUCTS.ITEM_ID=CAESAR_DELIVERY_LINE.MATERIAL_NO )
GROUP BY
  CAESAR_DELIVERY_LINE.COMPANY_CODE,
  CAESAR_DELIVERY_LINE.LDG_PERIOD,
  M_PRODUCTS.BUSINESS_DESC; analyze table FUJMIS.CAESAR_DELIVERY_LINE_MV compute statistics;

When a run the SQL :

SELECT CAESAR_DELIVERY_LINE.COMPANY_CODE,   CAESAR_DELIVERY_LINE.LDG_PERIOD,

  M_PRODUCTS.BUSINESS_DESC,
  sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED) as QUANTITY_DELIVERED,
  sum(CAESAR_DELIVERY_LINE.EURO_REVENUE) as EURO_REVENUE,
  sum(CAESAR_DELIVERY_LINE.EURO_COST) as EURO_COST,
  sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED * M_PRODUCTS.MAIN_UNIT)

as VOLUME_DELIVERED
FROM
  CAESAR_DELIVERY_LINE,
  M_PRODUCTS
WHERE
( M_PRODUCTS.ITEM_ID=CAESAR_DELIVERY_LINE.MATERIAL_NO )
GROUP BY
  CAESAR_DELIVERY_LINE.COMPANY_CODE,
  CAESAR_DELIVERY_LINE.LDG_PERIOD,
  M_PRODUCTS.BUSINESS_DESC; The query is rewritten and the mview is used, i.e. SQL Text matching works (although if I change any of the text to lowercase after the FROM cluse the MVIEW will NOT be used?)

However, if I run :
SELECT CAESAR_DELIVERY_LINE.COMPANY_CODE,   CAESAR_DELIVERY_LINE.LDG_PERIOD,

  M_PRODUCTS.BUSINESS_DESC,
  sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED) as QUANTITY_DELIVERED,
  sum(CAESAR_DELIVERY_LINE.EURO_REVENUE) as EURO_REVENUE,
  sum(CAESAR_DELIVERY_LINE.EURO_COST) as EURO_COST,
  sum(CAESAR_DELIVERY_LINE.QUANTITY_DELIVERED * M_PRODUCTS.MAIN_UNIT)

as VOLUME_DELIVERED
FROM
  CAESAR_DELIVERY_LINE,
  M_PRODUCTS
WHERE
( M_PRODUCTS.ITEM_ID=CAESAR_DELIVERY_LINE.MATERIAL_NO )
   AND CAESAR_DELIVERY_LINE.LDG_PERIOD = '200202' GROUP BY
  CAESAR_DELIVERY_LINE.COMPANY_CODE,
  CAESAR_DELIVERY_LINE.LDG_PERIOD,
  M_PRODUCTS.BUSINESS_DESC; i.e. added a filter on the "LDG_PERIOD" field from the FACT table, the view is not used and the query is not rewritten - even though this field is present in the mview.

Any ideas as having read the literature over and over I don't know how to make it work!?

George Received on Tue Apr 09 2002 - 09:25:54 CDT