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 -----------------------------------------------------------------