Re: My new Materialized View

C Chang wrote:

> 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

What is the point of casting dates to CHARs? Huge waste of CPU to produce something from which date math is impossible.

I'd also take a look at:
  AND purchase_type <>'SPOTBUY'
  AND order_type <> 'SPOTBUY'
why is the same string identified as being two different things?

I'd want to take a good look at the explain plan on this one before making any additional comment.

Daniel Morgan Received on Fri Mar 28 2003 - 10:31:36 CST