| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> To MV or not MV, that is my question
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 USAPlease see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
 |
 |