My new Materialized View

From: C Chang <cschang_at_maxinter.net>
Date: Fri, 28 Mar 2003 00:10:02 -0500
Message-ID: <3E83D92A.1F69@maxinter.net>

Here is my proposed MV:

CREATE MATERIALIZED VIEW mv_vendor_consumption NOLOGGING
TABLESPACE sys_mview
ON PREBUILT TABLE
STORAGE

( INITIAL     400M
  NEXT        400M

  PCTINCREASE 0
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED )

REFRESH FAST on demand                      
WITH ROWID                             -- <--  question about this      

START WITH sysdate NEXT TRUNC(sysdate)+2/24 -- rerun at 2 am EST ENABLE QUERY REWRITE
AS
  SELECT c.vendor_id, c.fsc||c.niin, c.vendor_list, c.vendor_lin,

       c.vendor_price, c.qty_period, c.contract_id, c.vendor_qty, c.vendor_um,

       DECODE(s.reference_date,NULL,
TO_CHAR(s.source_file_date,'MM/DD/YYYY'),TO_CHAR(s.reference_date,'MM/DD/YYYY')),

       r.qty, r.qty_unit
  FROM catalog_items c, vp$source_lists s, receipts r, po_lines p, po_heads h
  WHERE s.list = c.vendor_list
  AND r.niin = c.niin
  AND r.site_id = SUBSTR(c.contract_id,1,2)   AND SAMMS_FLAG = 'N'
  AND purchase_type <>'SPOTBUY'
  AND order_type <> 'SPOTBUY'
  AND price_type = 'S'

  AND r.po_id = p.po_id
  AND r.req_num = p.req_num 
  AND r.niin = p.niin
  AND p.po_id = h.po_id;

This will be deployed on 8.1.6 at NT 4 with 2 CPU, 6 HD. I basically follow others references. Any Suggestion welcome.

C Chang Received on Thu Mar 27 2003 - 23:10:02 CST