Home » SQL & PL/SQL » SQL & PL/SQL » materialized views
icon9.gif  materialized views [message #228013] Fri, 30 March 2007 13:55 Go to next message
vineetahere
Messages: 2
Registered: March 2007
Junior Member
my query is in this format
set heading off
set newpage none
set echo off
set feedback on
spool ../log/MVIEW_BUILD.log
DROP MATERIALIZED VIEW MV_TRIAL;

CREATE MATERIALIZED VIEW MV_TRIAL
PARTITION BY RANGE (as_of_date)
(
PARTITION MV_FACT_FCST_2006_M1 VALUES LESS THAN (TO_DATE('02/01/2006

00:00:00','MM/DD/YYYY HH24:MI:SS')),
.
.
.
PARTITION MV_FACT_FCST_2011_M12 VALUES LESS THAN (TO_DATE('01/01/2012

00:00:00','MM/DD/YYYY HH24:MI:SS'))


)
TABLESPACE DWQA_DATA01
CACHE
NOLOGGING
PARALLEL ( DEGREE 6 INSTANCES 1 )
REFRESH FORCE ON DEMAND
AS


SELECT Query THAT CONTAINS SUM(DECODE()) GROUP BY
UNION all

SELECT Query THAT CONTAINS SUM(DECODE()) GROUP BY




CREATE INDEX XN10_MV_TRIAL ON MV_TRIAL
(AS_OF_DATE)
NOLOGGING
TABLESPACE DWQA_INDX01
PARALLEL (DEGREE 6);

CREATE INDEX XN11_MV_TRIAL ON MV_TRIAL
(ASSUMP_TYP_BE_ID)
NOLOGGING
TABLESPACE DWQA_INDX01
PARALLEL (DEGREE 6);



COMMIT;
set term off;
spool off;
exit;

when i use REFRESH FORCE ON DEMAND it takes a lot of time to refresh . I want to implement FAST REFRESH .What all things shd be changed in my query ?
Re: materialized views [message #228017 is a reply to message #228013] Fri, 30 March 2007 14:03 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
As far as I know, the only restriction is that you have to have a materialized view log on the base table(s) and that you can't use analytical functions in the query statement of the MV.
Re: materialized views [message #228030 is a reply to message #228013] Fri, 30 March 2007 15:35 Go to previous messageGo to next message
vineetahere
Messages: 2
Registered: March 2007
Junior Member
i just read somewhere that if SYSDATE is used in the query then we cannot implement FAST REFRESH ?
Re: materialized views [message #228048 is a reply to message #228030] Sat, 31 March 2007 00:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Lookup DBMS_MVIEW.EXPLAIN_MVIEW in the manual. It will tell you exactly why the MV cannot fast refresh.

Ross Leishman
Re: materialized views [message #228170 is a reply to message #228013] Sun, 01 April 2007 23:34 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Why do you not read replies to your post? Skooman already pointed it out. As SUM(DECODE()) GROUP BY is not analytics, so the non-existence of the MV log seems to be the reason as your script does not contain its creation. Also why did you not simply specify REFRESH FAST in MV refresh clause? It failed without existence of MV log, did not it (as documented)?

I do not think it has something to do with columns based on SYSDATE, although I find it the same bug as storing these columns in the ordinary TABLE.
Previous Topic: how to dynamically switch accessing a table from one db to another
Next Topic: how to find max if data type is varchar2
Goto Forum:
  


Current Time: Fri Dec 09 13:38:58 CST 2016

Total time taken to generate the page: 0.08038 seconds