Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> To MV or not MV, that is my question

To MV or not MV, that is my question

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Mon, 23 Aug 2004 11:24:15 -0500
Message-ID: <FBE1FCA40ECAD41180400050DA2BC54004E93CAA@qtiexch2.qgraph.com>


Hey all,
Our ERP DB is a modest ~40GB on 9.2.0.5.0 on HPUX 11.11. One of the company metrics involves determining usage of parts. The current method of capturing this data is a beast. Enter the materialized view. Not having used them before, but realizing their potential, I write up this MV:

CREATE MATERIALIZED VIEW qt_part_hist_24_mon_mv PARALLEL 2
BUILD IMMEDIATE
--REFRESH FAST

REFRESH COMPLETE
ON DEMAND
WITH ROWID
USING DEFAULT LOCAL ROLLBACK SEGMENT
--ENABLE QUERY REWRITE

AS
SELECT partno,

        DECODE(warehouse,'  ','00',NULL,'00',warehouse) warehouse,
        transtype,
        accountno,
        SUM(CASE WHEN time_stamp >= RPAD(TO_CHAR(SYSDATE-NUMTODSINTERVAL(7,'DAY'),'YYYYMMDD'),16,'0') THEN transqty ELSE 0 END) "WEEK",
        SUM(CASE WHEN time_stamp >= RPAD(TO_CHAR(SYSDATE-NUMTOYMINTERVAL(3,'MONTH'),'YYYYMMDD'),16,'0') THEN transqty ELSE 0 END) "MONTH_3",
        SUM(CASE WHEN time_stamp >= RPAD(TO_CHAR(SYSDATE-NUMTOYMINTERVAL(6,'MONTH'),'YYYYMMDD'),16,'0') THEN transqty ELSE 0 END) "MONTH_6",
        SUM(CASE WHEN time_stamp >= RPAD(TO_CHAR(SYSDATE-NUMTOYMINTERVAL(12,'MONTH'),'YYYYMMDD'),16,'0') THEN transqty ELSE 0 END) "MONTH_12",
        SUM(CASE WHEN time_stamp >= RPAD(TO_CHAR(SYSDATE-NUMTOYMINTERVAL(18,'MONTH'),'YYYYMMDD'),16,'0') THEN transqty ELSE 0 END) "MONTH_18",
        SUM(CASE WHEN time_stamp >= RPAD(TO_CHAR(SYSDATE-NUMTOYMINTERVAL(24,'MONTH'),'YYYYMMDD'),16,'0') THEN transqty ELSE 0 END) "MONTH_24",
        SUM(CASE WHEN SUBSTR(time_stamp,1,4) = TO_CHAR(SYSDATE,'YYYY') THEN transqty ELSE 0 END) "YTD",
        SUM(CASE WHEN SUBSTR(time_stamp,1,4) = TO_CHAR(SYSDATE-NUMTOYMINTERVAL(1,'YEAR'),'YYYY') THEN transqty ELSE 0 END) "PREV_YEAR",
        SUM(CASE WHEN SUBSTR(time_stamp,1,4) = TO_CHAR(SYSDATE,'YYYY') AND SUBSTR(time_stamp,5,2) IN ('01','02','03') THEN transqty ELSE 0 END) "Q1",
        SUM(CASE WHEN SUBSTR(time_stamp,1,4) = TO_CHAR(SYSDATE,'YYYY') AND SUBSTR(time_stamp,5,2) IN ('04','05','06') THEN transqty ELSE 0 END) "Q2",
        SUM(CASE WHEN SUBSTR(time_stamp,1,4) = TO_CHAR(SYSDATE,'YYYY') AND SUBSTR(time_stamp,5,2) IN ('07','08','09') THEN transqty ELSE 0 END) "Q3",
        SUM(CASE WHEN SUBSTR(time_stamp,1,4) = TO_CHAR(SYSDATE,'YYYY') AND SUBSTR(time_stamp,5,2) IN ('10','11','12') THEN transqty ELSE 0 END) "Q4"
FROM part_history
        WHERE fiscal_year > TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 3
GROUP BY partno, DECODE (warehouse,' ', '00',NULL, '00',warehouse), transtype, accountno;

I don't particularly agree with the usefulness of some of these columns (I would think anything forward of previous quarter's usage could incorrectly skew the data, but I'm just a computer geek), but this is what's requested. My problem isn't with the MV itself, but with justifying why we should use it. As I'm dealving into this, I see that since SYSDATE is a non-deterministic function, it precludes the use of REFRESH FAST and QUERY REWRITE. While I'm less concerned about QUERY REWRITE, I don't see any advantage of using a REFRESH COMPLETE MV over a well-trusted TRUNCATE/INSERT-SELECT.

Thoughts???

BTW, yes, the "time_stamp" column is CHAR(16), thoughtfully supplied by our ERP vendor, probably because 8i didn't have the granularity in DATE types like TIMESTAMP does.

TIA,
Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_quadtechworld.com      QuadTech, Sussex, WI USA




----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Mon Aug 23 2004 - 11:19:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US