Home » SQL & PL/SQL » SQL & PL/SQL » Refresh on MATERIALIZED VIEW
Refresh on MATERIALIZED VIEW [message #262705] Tue, 28 August 2007 01:53 Go to next message
bagulia_pilla
Messages: 25
Registered: July 2006
Junior Member
Hi Friends,
I want to know how to refresh the MATERIALIZED VIEW.

CREATE TABLE emp( 
	   empno NUMBER(4) PRIMARY KEY,      
	   ename VARCHAR2(10),      
	   job VARCHAR2(9),      
	   mgr NUMBER(4),      
	   hiredate DATE,      
	   sal NUMBER(7, 2),      
	   comm NUMBER(7, 2),      
	   deptno NUMBER(2)
	   );

   
CREATE MATERIALIZED VIEW LOG ON EMP
   WITH PRIMARY KEY
   INCLUDING NEW VALUES;


INSERT INTO emp1
     VALUES (333, 'SMITH', 'CLERK', 7902, TO_DATE ('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);


Material View Code
CREATE MATERIALIZED VIEW MV_EMP_FAST
   	REFRESH fast ON  demand  	 
	AS SELECT * FROM test_cms.EMP;	


After creating the view. I am getting correct value but once I am inserting some records to emp table.. The view is not refreshed..

Please help me
Re: Refresh on MATERIALIZED VIEW [message #262729 is a reply to message #262705] Tue, 28 August 2007 02:48 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
http://www.psoug.org/reference/materialized_views.html
Re: Refresh on MATERIALIZED VIEW [message #262737 is a reply to message #262705] Tue, 28 August 2007 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but once I am inserting some records to emp table.. The view is not refreshed..

It is the normal behaviour as you asked Oracle to "REFRESH fast ON demand" so it does it when you ask it to do it.

Have a look at dbms_mview and dbms_refresh package.

Regards
Michel
Re: Refresh on MATERIALIZED VIEW [message #262749 is a reply to message #262737] Tue, 28 August 2007 04:04 Go to previous messageGo to next message
bagulia_pilla
Messages: 25
Registered: July 2006
Junior Member
what is the command or how to do it ?
please give some example or modify my query to do

thanks
Re: Refresh on MATERIALIZED VIEW [message #262759 is a reply to message #262749] Tue, 28 August 2007 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Follow the links I posted.

Also read CREATE MATERIALIZED VIEW.

Regards
Michel

[Updated on: Tue, 28 August 2007 04:23]

Report message to a moderator

Re: Refresh on MATERIALIZED VIEW [message #262831 is a reply to message #262705] Tue, 28 August 2007 06:15 Go to previous messageGo to next message
bagulia_pilla
Messages: 25
Registered: July 2006
Junior Member
Hi,,
I am bit of confusing on ur link.
is it possible to give me example with emp table as i did before..

The requirement is .
I need to create on MATERIALIZED VIEW ON EMP TABLE.

Please donot send me the link,,
thanks
Re: Refresh on MATERIALIZED VIEW [message #262833 is a reply to message #262705] Tue, 28 August 2007 06:17 Go to previous messageGo to next message
bagulia_pilla
Messages: 25
Registered: July 2006
Junior Member
I did this ,,
CREATE MATERIALIZED VIEW MV_EMP_FAST
   	REFRESH fast ON  demand  	 
	AS SELECT * FROM test_cms.


when I am modifying the emp table.. the new records are not reflecting on the View..
This is my Problem
Re: Refresh on MATERIALIZED VIEW [message #262837 is a reply to message #262833] Tue, 28 August 2007 06:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
REFRESH ON DEMAND means that you have to explicitly demand a refresh.

I think you're looking for REFRESH ON COMMIT.
Re: Refresh on MATERIALIZED VIEW [message #262844 is a reply to message #262705] Tue, 28 August 2007 06:29 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Syntax of refresh materialized view is

ALTER MATERIALIZED VIEW <schema.materialized_view>
REFRESH <FAST | COMPLETE | FORCE>
ON <DEMAND | COMMIT>
START WITH <date_time>
NEXT <date_time>
WITH PRIMARY KEY
USING DEFAULT MASTER ROLLBACK SEGMENT
USING <ENFORCED | TRUSTED> CONSTRAINTS;

You May use the following
ALTER MATERIALIZED VIEW MV_EMP_FAST REFRESH COMPLETE On commit;
Re: Refresh on MATERIALIZED VIEW [message #262851 is a reply to message #262831] Tue, 28 August 2007 06:45 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am bit of confusing on ur link.
is it possible to give me example with emp table as i did before..

There are examples in the links I posted (or they point to examples).

Regards
Michel
Previous Topic: cursor
Next Topic: Problem with OR
Goto Forum:
  


Current Time: Thu Dec 08 20:23:48 CST 2016

Total time taken to generate the page: 0.12371 seconds