To MV or not MV, that is my question
- From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
- To: "ORACLE-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 Aug 2004 11:24:15 -0500
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@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI USA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » To MV or not MV, that is my question
