DA Morgan wrote:
>
> 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
Dan:
Thanks again for your opinion. Yes. it is my fault, the to_char() of
Date column shouldn't be here.
Both
AND purchase_type <>'SPOTBUY'
AND order_type <> 'SPOTBUY'
are two different column but either one can have same values for some
rows, so I just want to eliminate those rows.
C Chang
Received on Fri Mar 28 2003 - 21:19:31 CST
