Errors from create Materialized View

From: cschang <cschang_at_maxinter.net>
Date: Thu, 07 Aug 2003 23:43:23 -0400
Message-ID: <3F331C5B.FB18FCC5@maxinter.net>

My system 8.1.7 on NT 4 sp6a. I tried to create a MV as syntax below, while compiled it, I faced few problems which I could not figure out. And by searching on web, most of the documents, including the Oracle, did not give me clear clues.
I have created all the MATERIALIZED VIEW LOGs on the tables involved with their primary keys.
I also set up QUERY_REWRITE_INTEEGRITY on the system.

CREATE MATERIALIZED VIEW mv_consumption
NOLOGGING NOCACHE
TABLESPACE ipvsys_dbview
ON PREBUILT TABLE
REFRESH complete
START WITH sysdate NEXT TRUNC(sysdate)+2/24 -- rerun at 2 am EST ENABLE QUERY REWRITE
AS
  SELECT c.vendor_id, 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 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;

Errors
1. With "ON PREBUILT TABLE" (follow the formula of Oracle), I got the "Missing Option" error.
2. Taken out the "ON PREBUILT TABLE", by combining REFRESH COMPLETE ON DEMAND, I still got the same error ( which the Oracle manual did not say I cannot)
3. Remove the "ON DEMAND", I got the "Insufficient Privileges" error to the last statement of the query " AND p.po_id = h.po_id". Because logged in as the Schema owner, who actually create all the tables involved.
I also tried to include the a "STORAGE ( )" part and it gave me the same error of Missing Option.

So My questions

1.  What exact orders of these option ?
2.  Which one can combine and which exclude each other?
3.  Not too much documents of what "ON PREBUILT TABLE" exactly does?
4.  What exact the Insufficient Privileges mean here?
5.  Because I am also setting up environment for Function-based index,

so I set the QUERY_REWRITE_INTEGRITY=TRUSTED. Does it affect the MV performance? some document mention it has to set =stale_.., So which one should be.

C Chang Received on Thu Aug 07 2003 - 22:43:23 CDT